oracle 判断varchar2类型是不是为空'' ,用什么判断符VV_LIST:='DSSSFDFDSFDS';
IF(VV_LIST <>'')THEN
DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
ELSE
DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
END IF;结果却打印了BBBBBBBBBBBBBBBBBBBBBBBB
IF(VV_LIST <>'')THEN
DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
ELSE
DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
END IF;结果却打印了BBBBBBBBBBBBBBBBBBBBBBBB
''和null不一样的
IF(VV_LIST IS NULL)THEN
DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
ELSE
DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
END IF;
IS NOT NULL可以赋值=NULL,
但在where条件中只能是IS (NOT)NULL
我说的'' 和非''的判断,不包括NULL
条件判断是否为空,不能用=NULL,只能IS NULL OR IS NOT NULL OR NOT IS NULL
可以赋值=NULL
VV_LIST:='DSSSFDFDSFDS';
IF length(trim(VV_LIST)) > 0 THEN
DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
ELSE
DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
END IF;
dbms_output.put_line(''''' is null');
END IF;
输出:'' is null这说明,在oracle pl/sql里,把''当成null处理了
for example:SQL> set serveroutput on
SQL> declare
2 str varchar(20):='helloworld';
3 begin
4 if str<>'' then
5 dbms_output.put_line('is not null');
6 else
7 dbms_output.put_line('is null');
8 end if;
9 end;
10 /
is nullPL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> declare
2 str varchar(20):='helloworld';
3 begin
4 if str is not null then
5 dbms_output.put_line('is not null');
6 else
7 dbms_output.put_line('is null');
8 end if;
9 end;
10 /
is not nullPL/SQL procedure successfully completed.
IF(VV_LIST <>'')THEN
DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
ELSE
DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
END IF; 结果却打印了BBBBBBBBBBBBBBBBBBBBBBBB这个一定输出的是 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
因为''是null
任何非空变量和null比较的结果都是不成立的
所以走else
IF(VV_LIST IS NOT NULL)THEN
DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
ELSE
DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
END IF;
2 vv_list char(100);
3 begin
4 VV_LIST := 'DSSSFDFDSFDS';
5 IF (VV_LIST <> '') THEN
6 DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
9 END IF;
10 end;
11 /
BBBBBBBBBBBBBBBBBBBBBBBB
PL/SQL procedure successfully completed
SQL> declare
2 vv_list char(100);
3 begin
4 VV_LIST := 'DSSSFDFDSFDS';
5 IF (VV_LIST <> ' ') THEN
6 DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
9 END IF;
10 end;
11 /
2 vv_list char(100);
3 begin
4 VV_LIST := 'DSSSFDFDSFDS';
5 IF (VV_LIST <> ' ') THEN
6 DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
9 END IF;
10 end;
11 /
PPPPPPPPPPPPPPPPP
PL/SQL procedure successfully completed
最後寫成 is null is not null
只有中间加了空格才不一样
IF(VV_LIST <>' ')THEN
DBMS_OUTPUT.PUT_LINE('PPPPPPPPPPPPPPPPP');
ELSE
DBMS_OUTPUT.PUT_LINE('BBBBBBBBBBBBBBBBBBBBBBBB');
END IF;加个空格