CSDN博客

img mantisXF

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

发表于2008/9/28 17:26:00  987人阅读

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,'CSDN','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 CSDN       MANAGER    7369 2008-9-28 1   8000.00               10

Executed
in 0.015 seconds

阅读全文
0 0

相关文章推荐

img
取 消
img