如何在Oracle中测试列是否等于empty_clob()? [英] How do I test if a column equals empty_clob() in Oracle?

问题描述

天真的FOO = empty_clob()抱怨类型不兼容.我尝试了Googling，但是(再次一次)在寻求Oracle的帮助方面收效甚微.谢谢.

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.

推荐答案

如果您尝试在PL/SQL中进行比较，则可以像Igor的解决方案一样测试相等性

If you are trying to do the comparison in PL/SQL, you can just test equality as Igor's solution does

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.

如果您试图在SQL Thougyh中执行此操作，则需要使用DBMS_LOB.COMPARE函数.表中的LOB列实际上是LOB定位符(即指针)，因此您真正关心的是LOB指向的值与EMPTY_CLOB()函数返回的LOB定位器指向的值相当.

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> 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

这篇关于如何在Oracle中测试列是否等于empty_clob()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！