插入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語句都不是必須的。去掉這些不必要的步驟，可以有效的降低程式和資料庫互動次數，減少單個操作呼叫的語句數量，從而提高操作的效能。