oracle null和 ''的区别

SQL> set serveroutput on
SQL> declare l_t varchar2(10);
  2  begin
  3          l_t := '';
  4          if l_t is null then
  5             dbms_output.put_line('null');
  6          end if;
  7  end;
  8  /
null
PL/SQL procedure successfully completed

上面代码说明,varchar2的时候null和''是等价的

SQL> set serveroutput on
SQL> declare l_t char(10);
  2  begin
  3          l_t := '';
  4          if l_t is null then
  5             dbms_output.put_line('null');
  6          else
  7             dbms_output.put_line('not null');
  8          end if;
  9  end;
 10  /
not null
PL/SQL procedure successfully completed

上面的代码说明在char的时候null不等于''

SQL> create table test_null (a char(10),b varchar2(10));
Table created


SQL> insert into test_null values('','');
1 row inserted


SQL> select * from test_null;
A          B
---------- ----------


SQL> select nvl2(a,'not null','null'),nvl2(b,'not null','null') from test_null;
NVL2(A,'NOTNULL','NULL') NVL2(B,'NOTNULL','NULL')
------------------------ ------------------------
null                     null


SQL> 

说明char和varchar2作为列的时候null和''是一样的,char作为变量申明的时候,null和''是不一样的,值设定为' ',长度为char定义的长度