触发器中操作LOB-CSDN博客

在触发器中可以像其他数据类型一样处理LOB字段，但是仅局限于SQL语句对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之类的工具才能获取到。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-619364/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-619364/