觸發器中操作LOB

如果看不懂上面的含義，沒有關係，下面透過一個例子來說明：

SQL> CREATE TABLE T          
  2  (ID NUMBER,
  3  TEXT CLOB);

Table created.

下面在T表上新增一個觸發器，檢查插入的LOB列的長度，並在LOB列後加入一個字元傳“ END”：

SQL> CREATE OR REPLACE TRIGGER T
  2  BEFORE INSERT ON T
  3  FOR EACH ROW
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(:NEW.TEXT));
  6     :NEW.TEXT := :NEW.TEXT || ' END';
  7  END;
  8  /

Trigger created.

SQL> SET SERVEROUT ON
SQL> SET LONG 10000000 

首先嚐試直接插入記錄：

SQL> INSERT INTO T      
  2  VALUES (1, 'ABCDEFG');
7

1 row created.

SQL> SELECT * FROM T;

        ID TEXT
---------- --------------------------------------------------------------------------------
         1 ABCDEFG END

下面透過PL/SQL中巢狀SQL的方式插入，繫結字串的長度是32767：

SQL> DECLARE
  2     V_STR VARCHAR2(32767) := RPAD('A', 32767, 'A');
  3  BEGIN
  4     INSERT INTO T VALUES (2, V_STR);
  5     COMMIT;
  6  END;
  7  /
32767

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE ID = 2;

        ID TEXT
---------- --------------------------------------------------------------------------------
         2 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
.
.
.
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA END

下面測試一下透過PL/SQL，利用臨時LOB，直接插入LOB物件的方式：

SQL> DECLARE
  2     V_LOB CLOB;
  3     V_STR VARCHAR2(32767) := RPAD('A', 32767, 'A');
  4  BEGIN
  5     DBMS_LOB.CREATETEMPORARY(V_LOB, TRUE);
  6     FOR I IN 1..5 LOOP
  7             DBMS_LOB.WRITEAPPEND(V_LOB, 32767, V_STR);
  8     END LOOP;
  9     INSERT INTO T VALUES (3, V_LOB);
 10     COMMIT;
 11  END;
 12  /
163835

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE ID = 3;

        ID TEXT
---------- --------------------------------------------------------------------------------
         3 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
.
.
.
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
           AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA END

下面看一下透過SQL語句INSERT AS SELECT的方式插入LOB型別：

SQL> INSERT INTO T
  2  SELECT ID + 3, TEXT
  3  FROM T;
11
32771
163839

3 rows created.

SQL> SELECT ID,
  2  DBMS_LOB.SUBSTR(TEXT, 10, DBMS_LOB.GETLENGTH(TEXT) - 9) TEXT,
  3  DBMS_LOB.GETLENGTH(TEXT) LENGTH
  4  FROM T;

        ID TEXT                                                             LENGTH
---------- ------------------------------------------------------------ ----------
         1 BCDEFG END                                                           11
         2 AAAAAA END                                                        32771
         3 AAAAAA END                                                       163839
         4 FG END END                                                           15
         5 AA END END                                                        32775
         6 AA END END                                                       163843

6 rows selected.

可以看到，無論上面哪種方式，觸發器都可以正常的處理LOB，而且處理方式和其他資料型別並沒有什麼不同。不過下面這種透過PL/SQL直接處理LOB的方式，觸發器就無能為力了：

SQL> DECLARE
  2     V_LOB CLOB;
  3     V_STR VARCHAR2(32767) := ' PL/SQL DIRECT WRITE LOB';
  4  BEGIN
  5     INSERT INTO T VALUES (7, EMPTY_CLOB) RETURN TEXT INTO V_LOB;
  6     DBMS_LOB.WRITEAPPEND(V_LOB, LENGTH(V_STR), V_STR);
  7     COMMIT;
  8  END;
  9  /
0

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE ID = 7;

        ID TEXT
---------- ------------------------------------------------------------
         7  END PL/SQL DIRECT WRITE LOB

觸發器也工作了，但是獲取到的LOB長度是0，而且追加的內容也寫到了LOB開頭的部分。

其實這並非是觸發器的問題，因為對於觸發器而言，觸發的SQL語句實際上確實只是寫入了一個空的LOB，而LOB隨後的內容是透過PL/SQL程式碼透過LOB的定位符直接寫入的，這個操作就不是觸發器能捕獲的了。

舉個不太恰當的例子，比如透過直接路徑方式裝載記錄，或者利用EXCHANGE PARTITION方式是的資料進入到表中，都是不會導致觸發器觸發的，而這種PL/SQL方式寫入LOB也是一樣的。利用觸發器沒有辦法獲取到這部分資訊，而只能透過類似LOGMINER之類的工具才能獲取到。