LOB欄位EMPTY_LOB和NULL的區別

前不久寫過一篇文章，描述如果表包含了觸發器，在透過IMP匯入資料的時候，原本的EMPTY_LOB將被轉化為NULL。有朋友在文章的回覆中問，EMPTY_LOB和NULL的區別，這裡就簡單描述一下。

包含觸發器的LOB表執行IMP導致EMPTY_LOB變為空：http://yangtingkun.itpub.net/post/468/495024

說實話，二者其實差別還是相當大的。一個表示的未知，另一個表示的空的大物件。需要注意空的大物件並不是空的概念：

SQL> CREATE TABLE T_LOB
  2  (ID NUMBER, 
  3  NAME VARCHAR2(30),
  4  CONTENTS CLOB);

表已建立。

SQL> INSERT INTO T_LOB
  2  VALUES (1, 'TEST NULL', NULL);

已建立 1 行。

SQL> INSERT INTO T_LOB
  2  VALUES (2, 'EMPTY LOB', EMPTY_CLOB());

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> COL CONTENTS FORMAT A30
SQL> SELECT *
  2  FROM T_LOB
  3  WHERE CONTENTS IS NULL;

        ID NAME                           CONTENTS
---------- ------------------------------ ------------------------------
         1 TEST NULL

使用IS NULL作為條件進行判斷，EMPTY_LOB是查詢不到的。

利用DBMS_LOB.GETLENGTH也可以看出二者的區別：

SQL> SELECT ID, NAME, DBMS_LOB.GETLENGTH(CONTENTS)
  2  FROM T_LOB;

        ID NAME                           DBMS_LOB.GETLENGTH(CONTENTS)
---------- ------------------------------ ----------------------------
         1 TEST NULL
         2 EMPTY LOB                                                 0

從佔用空間上二者也有明顯的區別：

SQL> DELETE T_LOB WHERE ID = 2;

已刪除 1 行。

SQL> BEGIN      
  2  FOR I IN 1..10000 LOOP
  3  INSERT INTO T_LOB
  4  VALUES (1, 'TEST NULL', NULL);
  5  END LOOP;
  6  COMMIT;
  7  END;
  8  /

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_LOB')

PL/SQL 過程已成功完成。

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T_LOB';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T_LOB                               10001         28          13

SQL> TRUNCATE TABLE T_LOB;

表被截斷。

SQL> INSERT INTO T_LOB
  2  VALUES (2, 'EMPTY LOB', EMPTY_CLOB());

已建立 1 行。

SQL> BEGIN
  2  FOR I IN 1..10000 LOOP
  3  INSERT INTO T_LOB
  4  VALUES (2, 'EMPTY LOB', EMPTY_CLOB());
  5  END LOOP;
  6  COMMIT;
  7  END;
  8  /

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_LOB')

PL/SQL 過程已成功完成。

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T_LOB';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T_LOB                               10001         80         116

雖然EMPTY_LOB沒有包含LOB內容，但是LOB頭資訊已經存在，因此需要佔用不小的空間。

二者最大的區別在於，EMPTY_LOB雖然沒有LOB的內容，但是已經做好了插入LOB內容的準備，使用者獲取到LOB的頭資訊後就可以直接插入資料了：

SQL> TRUNCATE TABLE T_LOB;

表被截斷。

SQL> INSERT INTO T_LOB
  2  VALUES (1, 'TEST NULL', NULL);

已建立 1 行。

SQL> INSERT INTO T_LOB
  2  VALUES (2, 'EMPTY LOB', EMPTY_CLOB());

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> DECLARE
  2  V_LOB CLOB;
  3  BEGIN
  4  SELECT CONTENTS 
  5  INTO V_LOB
  6  FROM T_LOB
  7  WHERE ID = 2
  8  FOR UPDATE;
  9  DBMS_LOB.WRITEAPPEND(V_LOB, 10, '0123456789');
 10  COMMIT;
 11  END;
 12  /

PL/SQL 過程已成功完成。

SQL> DECLARE
  2  V_LOB CLOB;
  3  BEGIN
  4  SELECT CONTENTS 
  5  INTO V_LOB
  6  FROM T_LOB
  7  WHERE ID = 1
  8  FOR UPDATE;
  9  DBMS_LOB.WRITEAPPEND(V_LOB, 10, '0123456789');
 10  COMMIT;
 11  END;
 12  /
DECLARE
*
第 1 行出現錯誤:
ORA-06502: PL/SQL: 數字或值錯誤 : invalid LOB locator specified: ORA-22275
ORA-06512: 在 "SYS.DBMS_LOB", line 833
ORA-06512: 在 line 9

SQL> SELECT *
  2  FROM T_LOB;

        ID NAME                           CONTENTS
---------- ------------------------------ ------------------------------
         1 TEST NULL
         2 EMPTY LOB                      0123456789

而對於NULL來說，顯然是不能直接修改的。