前不久写过一篇文章，描述如果表包含了触发器，在通过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来说，显然是不能直接修改的。

ITPub博客

LOB字段EMPTY_LOB和NULL的区别

最新文章