Oracle11新特性——PLSQL新特性（六）

打算寫一系列的文章介紹11g的新特性和變化。

11g中PL/SQL新增了很多特性，在效能和易用性方面做了不少的提升，還有一些功能性的增強。

這篇介紹一下PLSQL新增的複合觸發器。

Oracle11新特性——PLSQL新特性（一）：http://yangtingkun.itpub.net/post/468/395965

Oracle11新特性——PLSQL新特性（二）：http://yangtingkun.itpub.net/post/468/396571

Oracle11新特性——PLSQL新特性（三）：http://yangtingkun.itpub.net/post/468/396994

Oracle11新特性——PLSQL新特性（四）：http://yangtingkun.itpub.net/post/468/397350

Oracle11新特性——PLSQL新特性（五）：http://yangtingkun.itpub.net/post/468/398314

11g中對於觸發器部分有了一定的增強，主要表現在兩個方面。一個是對觸發器的觸發順序可以進行控制。另一個是可以定義一個複合觸發器。

上一篇介紹了觸發器的觸發順序，這一篇來介紹一下複合觸發器。複合觸發器中可以包括BEFORE STATEMENT、BEFORE EACH ROW、AFTER EACH ROW和AFTER STATEMENT四個部分，將四種型別的觸發器整合在一個觸發器中，如果需要多個型別的觸發器配合使用，採用複合觸發器會顯得邏輯更加清晰，而且不容易出現錯誤。在複合觸發器中定義的變數可以在不同型別的觸發語句中使用，不再需要使用外部包儲存中間結果。而且利用複合觸發器的批次操作還可以提高觸發器的效能。

下面先看一個簡單的COMPOUND TRIGGER的語法：

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> CREATE OR REPLACE TRIGGER TRI_COMPOUND FOR INSERT OR UPDATE OR DELETE ON T
2 COMPOUND TRIGGER
3 BEFORE STATEMENT IS
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('BEFORE STATEMENT');
6 END BEFORE STATEMENT;
7
8 BEFORE EACH ROW IS
9 BEGIN
10 DBMS_OUTPUT.PUT_LINE('BEFORE EACH ROW');
11 END BEFORE EACH ROW;
12
13 AFTER EACH ROW IS
14 BEGIN
15 DBMS_OUTPUT.PUT_LINE('AFTER EACH ROW');
16 END AFTER EACH ROW;
17
18 AFTER STATEMENT IS
19 BEGIN
20 DBMS_OUTPUT.PUT_LINE('AFTER STATEMENT');
21 END AFTER STATEMENT;
22 END;
23 /

觸發器已建立

SQL> SET SERVEROUT ON
SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB;
BEFORE STATEMENT
BEFORE EACH ROW
AFTER EACH ROW
BEFORE EACH ROW
AFTER EACH ROW
BEFORE EACH ROW
AFTER EACH ROW
AFTER STATEMENT

已建立3行。

瞭解了COMPOUND觸發器的語法，下面看看如何利用COMPOUND TRIGGER來簡化變異表的處理。在以前的一篇文章中，介紹了：透過觸發器複製包含LONG型別的表：http://yangtingkun.itpub.net/post/468/41936

裡面包括了變異表觸發器的處理方法，下面用COMPOUND TRIGGER來解決這個問題：

SQL> CREATE TABLE T_LONG (ID NUMBER PRIMARY KEY, COMMENTS LONG);

表已建立。

SQL> CREATE TABLE T_LONG_LOG (ID NUMBER PRIMARY KEY, COMMENTS CLOB);

表已建立。

SQL> CREATE OR REPLACE TRIGGER TRI_T_LONG_COMPOUND FOR INSERT ON T_LONG
2 COMPOUND TRIGGER
3 TYPE T_NUMBER IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4 V_ID T_NUMBER;
5 BEFORE EACH ROW IS
6 BEGIN
7 V_ID(V_ID.COUNT + 1) := :NEW.ID;
8 END BEFORE EACH ROW;
9
10 AFTER STATEMENT IS
11 BEGIN
12 FORALL I IN 1..V_ID.COUNT
13 INSERT INTO T_LONG_LOG SELECT ID, TO_LOB(COMMENTS) FROM T_LONG WHERE ID = V_ID(I);
14 END AFTER STATEMENT;
15 END;
16 /

觸發器已建立

SQL> INSERT INTO T_LONG SELECT ROWNUM, TNAME FROM TAB;

已建立5行。

SQL> COL COMMENTS FORMAT A40
SQL> SELECT * FROM T_LONG;

ID COMMENTS
---------- ----------------------------------------
1 T
2 T_LONG
3 T_LONG_LOG
4 T_SESSION
5 T_SESSION_STAT

SQL> SELECT * FROM T_LONG_LOG;

ID COMMENTS
---------- ----------------------------------------
1 T
2 T_LONG
3 T_LONG_LOG
4 T_SESSION
5 T_SESSION_STAT

對比一下就可以看出，使用COMPOUND觸發器要比建立三個觸發器加一個包要簡化很多，而且初始化，處理，清除等所有的步驟都在一起，也不容易出錯。

而且由於COMPOUND所有的程式碼可以集中在一起，現在很多操作可以批次處理，這樣COMPOUND還可以提高效能。

現在仍然使用第一個例子，為T增加一張LOG表，對T表所有的INSERT都同時插入到LOG表中，對比一下COMPOUND TRIGGER和普通TRIGGER的效能差異：

SQL> CREATE TABLE T_LOG (ID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> TRUNCATE TABLE T;

表被截斷。

下面建立兩種不同的觸發器，二者的功能一致，都是向T_LOG表中插入T表新插入的資料：

SQL> CREATE OR REPLACE TRIGGER TRI_COMPOUND FOR INSERT ON T DISABLE
2 COMPOUND TRIGGER
3 TYPE T_NUMBER IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4 TYPE T_VARCHAR2 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
5 V_ID T_NUMBER;
6 V_NAME T_VARCHAR2;
7 AFTER EACH ROW IS
8 BEGIN
9 V_ID(V_ID.COUNT + 1) := :NEW.ID;
10 V_NAME(V_NAME.COUNT + 1) := :NEW.NAME;
11 END AFTER EACH ROW;
12
13 AFTER STATEMENT IS
14 BEGIN
15 FORALL I IN 1..V_ID.COUNT
16 INSERT INTO T_LOG VALUES (V_ID(I), V_NAME(I));
17 END AFTER STATEMENT;
18 END;
19 /

觸發器已建立

SQL> CREATE OR REPLACE TRIGGER TRI_A_EACHROW AFTER INSERT ON T
2 FOR EACH ROW DISABLE
3 BEGIN
4 INSERT INTO T_LOG VALUES (:NEW.ID, :NEW.NAME);
5 END;
6 /

觸發器已建立

兩個觸發器都處於DISABLE狀態，向T表插入資料，然後依次ENABLE其中的一個觸發器，重複插入操作，對比三次的效能：

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

已建立68345行。

SQL> TRUNCATE TABLE T;

表被截斷。

SQL> SET TIMING ON
SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

已建立68345行。

已用時間: 00: 00: 00.75
SQL> TRUNCATE TABLE T;

表被截斷。

SQL> ALTER TRIGGER TRI_COMPOUND ENABLE;

觸發器已更改

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

已建立68345行。

已用時間: 00: 00: 05.59
SQL> TRUNCATE TABLE T;

表被截斷。

SQL> TRUNCATE TABLE T_LOG;

表被截斷。

SQL> ALTER TRIGGER TRI_COMPOUND DISABLE;

觸發器已更改

SQL> ALTER TRIGGER TRI_A_EACHROW ENABLE;

觸發器已更改

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

已建立68345行。

已用時間: 00: 00: 17.31

第一次不記錄時間，為了避免CACHE的影響，後面三次記錄時間，分別對應不啟用觸發器、啟用COMPOUND觸發器和啟動AFTER EACH ROW觸發器三種情況。對比三次的執行時間，可以看到使用了COMPOUND的FORALL批次處理功能，獲得的效能提高還是非常明顯的。