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;
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 wherefile_DATE >= trunc(p_date)+1-- >=n+1and file_DATE < trunc(p_date)+2AND 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 THENdbms_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
