Bài 4.PL/SQL cơ bản - Chương trình con

1.    Sự khác nhau giữa  Anonymous Block và Subprograms

2.    Anonymous Blocks (Oracle PL/SQL Blocks)

DECLARE
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   salary      NUMBER(7,2);
   dept_id     NUMBER(2) := 50;
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
   plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;
   sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
      RETURNING sal INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
      USING dept_id;
   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END; 

declare

total number :=0;

begin

for rs in (select isdn,d_tac,profile_text,imsi   from user1.tab1 b where

  file_DATE >= trunc(p_date)+1-- >=n+1

 and  file_DATE < trunc(p_date)+2

   AND d_TAC = trunc(p_date)

   and (A_CURSTA = 'ACTIF' OR (A_CURSTA = 'INACT' AND d_tin >  trunc(p_date)))

   and profile_text not like 'TS%'

   ) 

loop

  insert into  user1.tab1_tmp (isdn,d_tac,profile_text,imsi) values(rs.isdn,rs.d_tac,rs.profile_text,rs.imsi);

  total :=total+1;

  if(total = 100) then commit; total:=0; end if;

end loop;

commit;

EXCEPTION WHEN others  THEN

        dbms_output.put_line('n_dump_data_ptm_tmp:'||SQLERRM);

end;

3.    Chương trình con (Subprograms)

3.1. Procedure:

PROCEDURE Transaction_count_alert IS    CURSOR c1    IS       SELECT COUNT ( * ) cnt, service_code         FROM msg_audit        WHERE issue_datetime >= SYSDATE - 6 / 24 and response_code = '00'       GROUP BY service_code order by service_code;    v1   c1%ROWTYPE;    msg varchar2(160); BEGIN     msg:= 'Trans Count in last 6h:';    FOR v1 IN c1    LOOP     msg:= msg || 'Srv=' || v1.service_code || ', Cnt =' || v1.cnt || ';';    END LOOP;          INSERT INTO THREAD_SMTP_QUEUE             (                 batch_id,                 SOURCE,                 message_date,                 MESSAGE             )       VALUES (                 '2800',                 'Transaction Count Alert',                 SYSDATE,                 msg              );       COMMIT; END;

·         Gọi procedure

BEGIN Transaction_count_alert; END; /

3.2. Function

CREATE FUNCTION check_sal RETURN Boolean IS dept_id employees.department_id%TYPE; empno employees.employee_id%TYPE; sal employees.salary%TYPE; avg_sal employees.salary%TYPE; BEGIN empno:=205; SELECT salary,department_id INTO sal,dept_id FROM employees WHERE employee_id= empno; SELECT avg(salary) INTO avg_sal FROM employees WHERE department_id=dept_id; IF sal > avg_sal THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; /

·         Truyền biến vào function:

DROP FUNCTION check_sal; CREATE FUNCTION check_sal(empno employees.employee_id%TYPE) RETURN Boolean IS dept_id employees.department_id%TYPE; sal employees.salary%TYPE; avg_sal employees.salary%TYPE; BEGIN SELECT salary,department_id INTO sal,dept_id FROM employees WHERE employee_id=empno; SELECT avg(salary) INTO avg_sal FROM employees WHERE department_id=dept_id; IF sal > avg_sal THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION ... ...

·         Gọi hàm:

BEGIN DBMS_OUTPUT.PUT_LINE('Checking for employee with id 205'); IF (check_sal(205) IS NULL) THEN DBMS_OUTPUT.PUT_LINE('The function returned NULL due to exception'); ELSIF (check_sal(205)) THEN DBMS_OUTPUT.PUT_LINE('Salary > average'); ELSE DBMS_OUTPUT.PUT_LINE('Salary < average'); END IF; DBMS_OUTPUT.PUT_LINE('Checking for employee with id 70'); IF (check_sal(70) IS NULL) THEN DBMS_OUTPUT.PUT_LINE('The function returned NULL due to exception'); ELSIF (check_sal(70)) THEN ... END IF; END; /

·         Trong TOAD, SQL Navigator chú ý thêm output để hiển thị nhiều hơn:

DECLARE
   ... declarations ...
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);   // enable output voi 1000000 ky tu
   ...
END;

·         Trong SQL*Plus:

SQL> SET SERVEROUTPUT ON

·         Hàm trong hàm:

DECLARE

outer_variable VARCHAR2(20):='GLOBAL VARIABLE';

BEGIN

DECLARE

inner_variable VARCHAR2(20):='LOCAL VARIABLE';

BEGIN

DBMS_OUTPUT.PUT_LINE(inner_variable);

DBMS_OUTPUT.PUT_LINE(outer_variable);

END;

DBMS_OUTPUT.PUT_LINE(outer_variable);

END;

/

4. Packages

CREATE OR REPLACE PACKAGE test_pkg AS   PROCEDURE add_test (col1_in NUMBER, col2_in CHAR);   PROCEDURE del_test (col1_in NUMBER); END test_pkg; / CREATE OR REPLACE PACKAGE BODY test_pkg AS   PROCEDURE add_test(col1_in IN NUMBER,col2_in IN CHAR) AS   BEGIN     INSERT INTO test VALUES (col1_in,col2_in);   END add_test;   PROCEDURE del_test(col1_in IN NUMBER) AS   BEGIN     DELETE FROM test WHERE col1_in = col1;   END del_test; END test_pkg; /

Gọi thủ tục trong package:

Begin

            Test_pkg.add_test(1, ‘col10’);

            Test_pkg.del_test(1, ‘col10’);

           End;
@ Trần Văn Bình - Founder of Oracle DBA AZ #OraAz #OracleDBAAz #OracleTutorial