插入LOB对象的方法

其实以前写过类似的文章,但是都是在其他例子中,没有专门针对这个问题进行过描述,最近发现,还有很多人不清楚,插入一个包含LOB对象的记录需要几个步骤。
在客户的环境中,发现插入一条包含LOB的记录,居然用了四个步骤:

SQL> CREATE TABLE T_LOB (ID NUMBER, NAME VARCHAR2(30), CONTENTS CLOB);
表已创建。
SQL> DECLARE
2 V_CLOB CLOB;
3 V_STR VARCHAR2(32767) := LPAD('A', 4000, 'A');
4 BEGIN
5 INSERT INTO T_LOB 
6 VALUES (1, 'A', EMPTY_CLOB());
7 SELECT CONTENTS 
8 INTO V_CLOB
9 FROM T_LOB
10 WHERE ID = 1
11 FOR UPDATE;
12 DBMS_LOB.WRITE(V_CLOB, 4000, 1, V_STR);
13 UPDATE T_LOB 
14 SET CONTENTS = V_CLOB
15 WHERE ID = 1;
16 COMMIT;
17 END;
18 /
PL/SQL 过程已成功完成。

可以看到,为了插入一条包含LOB的记录,客户首先插入EMPTY_CLOB,然后通过SELECT预计获取LOB的定位符,通过DBMS_LOB包写入数据,最后通过UPDATE语句,对LOB列进行更新。
在上面的步骤中,UPDATE不是必须的,即使不对LOB列执行UPDATE操作,也会导致记录的插入:

SQL> DECLARE
2 V_CLOB CLOB;
3 V_STR VARCHAR2(32767) := LPAD('B', 4000, 'B');
4 BEGIN
5 INSERT INTO T_LOB 
6 VALUES (2, 'B', EMPTY_CLOB());
7 SELECT CONTENTS 
8 INTO V_CLOB
9 FROM T_LOB
10 WHERE ID = 2
11 FOR UPDATE;
12 DBMS_LOB.WRITE(V_CLOB, 4000, 1, V_STR);
13 COMMIT;
14 END;
15 /
PL/SQL 过程已成功完成。

而事实上,这里的SELECT FOR UPDATE语句同样是不必要的,这个语句可以由INSERT的RETURN语句来代替:

SQL> DECLARE
  2  V_CLOB CLOB;
  3  V_STR VARCHAR2(32767) := LPAD('C', 4000, 'C');
  4  BEGIN
  5  INSERT INTO T_LOB 
  6  VALUES (3, 'C', EMPTY_CLOB())
  7  RETURN CONTENTS INTO V_CLOB;
  8  DBMS_LOB.WRITE(V_CLOB, 4000, 1, V_STR);
  9  COMMIT;
 10  END;
 11  /
PL/SQL 过程已成功完成。
SQL> SET LONG 40
SQL> SELECT * FROM T_LOB;
        ID NAME                           CONTENTS
---------- ------------------------------ ----------------------------------------
         3 C                              CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
         1 A                              AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         2 B                              BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

插入包含LOB的记录,除了DBMS_LOB部分一般是必不可少的,此外只需要INSERT语句本身,其他的SELECT和UPDATE语句都不是必须的。去掉这些不必要的步骤,可以有效的降低程序和数据库交互次数,减少单个操作调用的语句数量,从而提高操作的性能。

This entry was posted in ORACLE and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *