透過觸發器複製包含LONG型別的表

前兩天論壇上有人問如何才能透過觸發器來實現對包含LONG或LONG RAW型別欄位的表的複製。給他做了個最簡單的例子，在這裡記錄一下。

由於在觸發器中不能對LONG或LONG RAW型別的欄位進行引用，所以原本最簡單的方式直接將:NEW.COL插入到新表的方法無法奏效，必須採用其他方法解決。

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

表已建立。

SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY, COMMENTS BLOB);

表已建立。

SQL> CREATE OR REPLACE TRIGGER TRI_T BEFORE INSERT ON T FOR EACH ROW
2 BEGIN
3 INSERT INTO T1 VALUES (:NEW.ID, :NEW.COMMENTS);
4 END;
5 /
CREATE OR REPLACE TRIGGER TRI_T BEFORE INSERT ON T FOR EACH ROW
*
ERROR 位於第 1 行:
ORA-04093: 不允許在觸發器中引用 LONG 型別的列

只能透過查詢原表的方式，不過查詢原表會面臨一個變異表的問題，解決這個變異表的問題一般是透過三個觸發器和一個程式包來完成。

SQL> CREATE OR REPLACE PACKAGE P_RECORD_ID AS
2 TYPE T_NUMBER IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_ID T_NUMBER;
4 END;
5 /

程式包已建立。

SQL> CREATE OR REPLACE TRIGGER TRI_B_T BEFORE INSERT ON T
2 BEGIN
3 P_RECORD_ID.V_ID.DELETE;
4 END;
5 /

觸發器已建立

SQL> CREATE OR REPLACE TRIGGER TRI_B_R_T BEFORE INSERT ON T FOR EACH ROW
2 BEGIN
3 P_RECORD_ID.V_ID(P_RECORD_ID.V_ID.COUNT + 1) := :NEW.ID;
4 END;
5 /

觸發器已建立

SQL> CREATE OR REPLACE TRIGGER TRI_A_T AFTER INSERT ON T
2 BEGIN
3 FOR I IN 1..P_RECORD_ID.V_ID.COUNT LOOP
4 INSERT INTO T1 SELECT ID, TO_LOB(COMMENTS) FROM T WHERE ID = P_RECORD_ID.V_ID(I);
5 END LOOP;
6 END;
7 /

觸發器已建立

簡單測試一下：

SQL> INSERT INTO T VALUES (1, '1234567890ABCD');

已建立 1 行。

SQL> SELECT * FROM T;

ID C
---------- -
1 1

SQL> COL COMMENTS FORMAT A50
SQL> SELECT ID, CAST(COMMENTS AS RAW(255)) COMMENTS FROM T1;

ID COMMENTS
---------- --------------------------------------------------
1 1234567890ABCD

SQL> INSERT INTO T VALUES (2, 'A');

已建立 1 行。

SQL> SELECT ID, CAST(COMMENTS AS RAW(255)) COMMENTS FROM T1;

ID COMMENTS
---------- --------------------------------------------------
1 1234567890ABCD
2 0A

SQL> INSERT INTO T SELECT ID + 2, 'ABC' FROM T;

已建立2行。

SQL> SELECT ID, CAST(COMMENTS AS RAW(255)) COMMENTS FROM T1;

ID COMMENTS
---------- --------------------------------------------------
1 1234567890ABCD
2 0A
3 0ABC
4 0ABC

對於8i版本，由於PL/SQL的編譯器還不認識TO_LOB函式，可以改用動態SQL：

SQL> CREATE OR REPLACE TRIGGER TRI_A_T AFTER INSERT ON T
2 BEGIN
3 FOR I IN 1..P_RECORD_ID.V_ID.COUNT LOOP
4 EXECUTE IMMEDIATE 'INSERT INTO T1 SELECT ID, TO_LOB(COMMENTS) FROM T WHERE ID = :ID'
5 USING P_RECORD_ID.V_ID(I);
6 END LOOP;
7 END;
8 /

觸發器已建立

對於LONG、LONG RAW型別的複製，用觸發器並不是個好的辦法，唯一的好處就是相對簡單，而且不需要修改程式，完全在資料庫級上實現，不過效率肯定不會太高

ref: http://yangtingkun.itpub.net/post/468/41936