如何在 Oracle 中测试列是否等于 empty_clob()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/521490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I test if a column equals empty_clob() in Oracle?
提问by Hank Gay
The na?ve FOO = empty_clob()complains about incompatible types. I tried Googling, but (once again) had little success searching for help with Oracle. Thanks.
天真的FOO = empty_clob()抱怨不兼容的类型。我尝试过谷歌搜索，但（再一次）在寻求 Oracle 帮助时收效甚微。谢谢。
采纳答案by Justin Cave
If you are trying to do the comparison in PL/SQL, you can just test equality as Igor's solution does
如果您尝试在 PL/SQL 中进行比较，您可以像 Igor 的解决方案一样测试相等性
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 dummy clob;
3 BEGIN
4 dummy := empty_clob();
5 IF dummy = empty_clob() THEN
6 dbms_output.put_line( 'Dummy is empty' );
7 ELSE
8 dbms_output.put_line( 'Dummy is not empty' );
9 END IF;
10* END;
SQL> /
Dummy is empty
PL/SQL procedure successfully completed.
If you are trying to do this in SQL, thougyh, you need to use the DBMS_LOB.COMPARE function. A LOB column in a table is really a LOB locator (i.e. pointer), so what you really care about is that the value pointed to by the LOB is comparable to the value pointed to by the LOB locator returned by the EMPTY_CLOB() function.
如果您尝试在 SQL 中执行此操作，那么您需要使用 DBMS_LOB.COMPARE 函数。表中的一个 LOB 列实际上是一个 LOB 定位器（即指针），所以你真正关心的是 LOB 指向的值与 EMPTY_CLOB() 函数返回的 LOB 定位器指向的值是可比的。
SQL> desc bar
Name Null? Type
----------------------------------------- -------- ------------------------
FOO CLOB
SQL> insert into bar values ('123');
1 row created.
SQL> insert into bar values( empty_clob() );
1 row created.
SQL> insert into bar values( empty_clob() );
1 row created.
SQL> ed
Wrote file afiedt.buf
1 select count(*)
2 from bar
3* where dbms_lob.compare( foo, empty_clob() ) = 0
SQL> /
COUNT(*)
----------
2
SQL> ed
Wrote file afiedt.buf
1 select count(*)
2 from bar
3* where dbms_lob.compare( foo, empty_clob() ) != 0
SQL> /
COUNT(*)
----------
1
回答by Steve K
Are you just wanting to check for a CLOB that doesn't have any length? While not exactly what your asking, it's basically the same thing?
你只是想检查一个没有任何长度的 CLOB 吗？虽然不完全是你要问的，但基本上是一样的？
select *
from bar
where dbms_lob.getlength(foo) = 0;
Here is the complete test:
这是完整的测试：
SQL> create table bar (foo clob);
Table created.
SQL> insert into bar values (empty_clob());
1 row created.
SQL> select *
2 from bar
3 where dbms_lob.getlength(foo) = 0;
FOO
--------------------------------------------------------------------------------
回答by Igor Zelaya
something like this should work for initialization:
这样的事情应该适用于初始化：
DECLARE
dummy clob;
dummy2 clob;
BEGIN
dummy := empty_clob();
IF dummy = empty_clob() THEN
dummy2 := dummy;
END IF;
END;
回答by user6735282
A simple way to test for empty clobs in SQLplus is to convert all the CLOBS to varchar2 (using the TO_CHAR function) before performing the test:
在 SQLplus 中测试空 clob 的一种简单方法是在执行测试之前将所有 CLOBS 转换为 varchar2（使用 TO_CHAR 函数）：
SELECT *
FROM table1
WHERE TO_CHAR(table1.column1) IS NULL
