为什么 SQLPLUS 将空和 null CLOB 显示为 null？

Question

SQLPLUS 似乎以一种我没有预料到的方式显示 CLOB 的 null 和空字符串。

在 SQLPLUS 中尝试以下操作（我使用的是 Oracle 10g 服务器）。创建一个带有 CLOB 的表，并插入 null、空 clob 和我认为的空字符串：

create table tableA (field1 number, field2 clob);
insert into tableA values (1, null);
insert into tableA values (2, empty_clob());
insert into tableA values (3, '');

好的，让我们做一些查询，但首先我们需要告诉 SQLPLUS 为我们清楚地显示 null：

set null {NULL}

对于以下查询，我本以为只返回第 1 行，但它返回 2：

select * from tableA where field2 is null;

field1   field 2
-----------------------
1        {NULL} 
3        {NULL} 

嗯，所以 '' 在 CLOB 中存储为 null？

好的，根据该结果，我现在希望以下查询返回所有 3 行，但仅在第 1 行和第 3 行中显示 {NULL} 。然而我得到这个结果：

select * from tableA;

field1   field 2
-----------------------
1        {NULL} 
2        {NULL} 
3        {NULL} 

这很令人困惑。我认为只有 2 个空值，尽管我最初预计有 1 个空值。那么这里发生了什么？ set null 不适用于 CLOB，如果是，我应该使用什么来代替？

我实际上正在尝试使用 null CLOB 值解决另一个问题，但这种令人困惑的行为让我运行了一段时间，所以我想在继续之前了解这一点。

预先感

谢博兹

Answer 1

Oracle 不区分 NULL 和空字符串。这是众所周知的违反 SQL 标准的行为。

这就是默认字符串类型为 VARCHAR2 而不是 VARCHAR 的原因。

在当前发布的所有版本中它们都是相同的，但不建议使用VARCHAR。这是因为 VARCHAR 应该区分 NULL 和空字符串，而标准中没有描述的 VARCHAR2 则不能。

Answer 2

我认为这与 SQL*Plus 处理空字符串和空值的方式有关。

empty_clob() 不会创建 NULL 值，而是创建长度为零的 CLOB。

当您查询 field2 为空的行时，不会返回第 2 行，这一事实证明了这一点。

使用我的 JDBC 工具，我可以用不同的颜色突出显示为空的列，并且带有empty_clob() 的行不会突出显示，而其他两行则突出显示。

所以我想说这是对 SQL*Plus 中 set null 选项的错误处理。通过以下语句，您将看到差异：

select field1, 
       nvl(field2, 'THIS IS NULL')
from tableA;

对我来说，这显示：

field1   field 2
-----------------------
1        THIS IS NULL 
2        
3        THIS IS NULL

Answer 3

看看第 2 行中的实际内容会很有趣。根据 文档（见下文）empty_clob() 将返回一个没有数据的初始化 clob 定位（对于 null 问题有些不清楚）。

当在 SQL 查询工具中执行 SQL 语句时，Oracle 倾向于隐式地将 CLOB 转换为以任意长度截断的字符串。

用途

EMPTY_BLOB 和 EMPTY_CLOB 返回一个空 LOB 定位器，可用于初始化 LOB 变量，或者在 INSERT 或 UPDATE 语句中将 LOB 列或属性初始化为 EMPTY。 EMPTY 表示 LOB 已初始化，但未填充数据。