触发器中操作LOB

在触发器中可以像其他数据类型一样处理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/