Thứ Tư, 8 tháng 1, 2020

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

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

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master