PACKAGE 可变数组+行组触发器+表级触发器解决数据操作问题

最新推荐文章于 2022-03-03 18:32:02 发布

原创最新推荐文章于 2022-03-03 18:32:02 发布 · 1.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#manager #date #insert #exception #integer #variables

这篇博客展示了如何利用PL/SQL中的PACKAGE定义可变数组，并结合行级触发器和表级触发器来处理数据操作。通过示例，解释了如何在INSERT操作后获取并存储新插入的行，以及如何在表级触发器中计算每个部门的最大薪资。

SQL> DESC EMP_TEST;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO    NUMBER(4)    Y
ENAME    VARCHAR2(10) Y
JOB      VARCHAR2(9) Y
MGR      NUMBER(4)    Y
HIREDATE DATE         Y
SAL      NUMBER(7,2) Y
COMM     NUMBER(7,2) Y
DEPTNO   NUMBER(2)    Y

SQL> SELECT * FROM EMP_TEST;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH      CLERK      7902 1980-12-17     800.00               20

Executed in 0.016 seconds

-- package 代码:
CREATE OR REPLACE PACKAGE SCOTT.PKG_TRIGGER_TEST IS
TYPE T_EMP IS TABLE OF SCOTT.EMP_TEST%ROWTYPE INDEX BY BINARY_INTEGER;
V_EMPS T_EMP;
V_NUM BINARY_INTEGER := 0;
END ;

-- 行级触发器得发NEW值：
CREATE OR REPLACE TRIGGER SCOTT.GET_EMP_ROW
AFTER INSERT ON SCOTT.EMP_TEST
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('-- TRIGGER SCOTT.GET_EMP_ROW START --');
SCOTT.PKG_TRIGGER_TEST.V_NUM := SCOTT.PKG_TRIGGER_TEST.V_NUM + 1;
SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).EMPNO := :NEW.EMPNO;
SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).ENAME := :NEW.ENAME;
SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).JOB := :NEW.JOB;
SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).MGR := :NEW.MGR;
SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).HIREDATE := :NEW.HIREDATE;
SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).SAL := :NEW.SAL;
SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).COMM := :NEW.COMM;
SCOTT.PKG_TRIGGER_TEST.V_EMPS(SCOTT.PKG_TRIGGER_TEST.V_NUM).DEPTNO := :NEW.DEPTNO;

EXCEPTION
WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);

END GET_EMP_ROW;

-- 表级触发器得到最大工资等Operation:
CREATE OR REPLACE TRIGGER SCOTT.TIA_T_RESULT_HEAD
AFTER INSERT ON SCOTT.EMP_TEST
DECLARE
-- local variables here
V_EMP_NO NUMBER;
V_MANAGER_NO NUMBER;
V_EMP_DATE DATE;
V_DEPT_NO NUMBER;

V_MAX_SALARY SCOTT.EMP_TEST.SAL%TYPE;

V_STEP VARCHAR2(100) := '';
BEGIN
DBMS_OUTPUT.PUT_LINE('-- TRIGGER SCOTT.TIA_T_RESULT_HEAD START --');
V_STEP := 'GET NEW VALUES';
FOR I IN 1..SCOTT.PKG_TRIGGER_TEST.V_EMPS.COUNT LOOP
      V_EMP_NO     := SCOTT.PKG_TRIGGER_TEST.V_EMPS(I).EMPNO;
      V_MANAGER_NO := SCOTT.PKG_TRIGGER_TEST.V_EMPS(I).MGR;
      V_EMP_DATE   := SCOTT.PKG_TRIGGER_TEST.V_EMPS(I).HIREDATE;
      V_DEPT_NO    := SCOTT.PKG_TRIGGER_TEST.V_EMPS(I).DEPTNO;

      DBMS_OUTPUT.PUT_LINE('EMP_NO: '||V_EMP_NO||' MANAGER_NO: '||V_MANAGER_NO||' EMP_DATE: '||V_EMP_DATE||' DEPT_NO: '||V_DEPT_NO);

      V_STEP := 'GET MAX SALARY';
      SELECT NVL(MAX(SAL),0)
        INTO V_MAX_SALARY
        FROM SCOTT.EMP_TEST
       WHERE DEPTNO = V_DEPT_NO;

      V_STEP := 'PRINT MAX SALARY AFTER INSERTED RECORDS';
      DBMS_OUTPUT.PUT_LINE('MAX SALARY: '||V_MAX_SALARY);
      DBMS_OUTPUT.PUT_LINE('');

END LOOP;

EXCEPTION
WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error at : '||V_STEP);
       DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
END TIA_T_RESULT_HEAD;

-- 测试结果:
SQL> INSERT INTO SCOTT.EMP_TEST
2 SELECT 7768,'MANTISXF','SALESER',7369,SYSDATE,3000,NULL,30 FROM DUAL
3 UNION ALL
4 SELECT 7780,'FENG','MONTIORER',7368,SYSDATE,5000,NULL,20 FROM DUAL
5 ;

-- TRIGGER SCOTT.GET_EMP_ROW START --
-- TRIGGER SCOTT.GET_EMP_ROW START --
-- TRIGGER SCOTT.TIA_T_RESULT_HEAD START --
EMP_NO: 7768 MANAGER_NO: 7369 EMP_DATE: 28-9月 -08 DEPT_NO: 30
MAX SALARY: 3000

EMP_NO: 7780 MANAGER_NO: 7368 EMP_DATE: 28-9月 -08 DEPT_NO: 20
MAX SALARY: 5000

2 rows inserted

Executed in 0.25 seconds

SQL> COMMIT;

Commit complete

Executed in 0 seconds

SQL> SELECT * FROM EMP_TEST;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH      CLERK      7902 1980-12-17     800.00               20
7768 MANTISXF   SALESER    7369 2008-9-28 1   3000.00               30
7780 FENG       MONTIORER 7368 2008-9-28 1   5000.00               20

Executed in 0.031 seconds

SQL> INSERT INTO SCOTT.EMP_TEST VALUES(8866,'优快云','MANAGER',7369,SYSDATE,8000,NULL,10);

-- TRIGGER SCOTT.GET_EMP_ROW START --
-- TRIGGER SCOTT.TIA_T_RESULT_HEAD START --
EMP_NO: 7768 MANAGER_NO: 7369 EMP_DATE: 28-9月 -08 DEPT_NO: 30
MAX SALARY: 3000

EMP_NO: 7780 MANAGER_NO: 7368 EMP_DATE: 28-9月 -08 DEPT_NO: 20
MAX SALARY: 5000

EMP_NO: 8866 MANAGER_NO: 7369 EMP_DATE: 28-9月 -08 DEPT_NO: 10
MAX SALARY: 8000

1 row inserted

Executed in 0.015 seconds

SQL> COMMIT;

Commit complete

Executed in 0.015 seconds

SQL> SELECT * FROM EMP_TEST;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH      CLERK      7902 1980-12-17     800.00               20
7768 MANTISXF   SALESER    7369 2008-9-28 1   3000.00               30
7780 FENG       MONTIORER 7368 2008-9-28 1   5000.00               20
8866 优快云       MANAGER    7369 2008-9-28 1   8000.00               10

Executed in 0.015 seconds