今天在写sql语句的时候，碰到了字段值是null时导致判断跟预
期不通问题
写了sql语句 where条件是莫字段！=1
结果字段默认null的数据都没有查出来，百度了下才知道
1 null值的介绍
NULL 是 中特有的数据类型，当⼀条记录的某个列为 NULL ，则表⽰这个列的值是未知的、是不确定的。既然是未知的，就有⽆数种的可能性。因此， NULL 并不是⼀个确定的值。 这是 NULL 的由来、也是 NULL 的基础，所有和 NULL 相关的操作的结果都可以从 NULL 的概念推导出来。
2 oracle中的null值介绍
在不知道具体有什么数据的时候，即未知，可以⽤NULL， 称它为空，ORACLE中，含有空值的表列长度为零。允许任何⼀种数据类型的字段为空，除了以下两种情况：
a、主键字段（primary key）；
b、定义时已经加了NOT NULL限制条件的字段
3 中null值说明：
a、等价于没有任何值、是未知数。
b、NULL与0、空字符串、空格都不同。
c、对空值做加、减、乘、除等运算操作，结果 仍为空。
d、NULL的处理使⽤NVL函数。
e、⽐较时使⽤关键字⽤“is null”和“is not null”。
f、空值不能被索引，所以查询时有些符合条件的数据可能查不出来， count(expr)中，⽤nvl(列名,0)处理后再查。
g、排序时⽐其他数据都⼤（索引默认是降序排列，⼩→⼤）， 所以NULL值总是排在最后。
IS NULL 和IS NOT NULL 是不可分割的整体，改为IS 或IS NOT都是错误的，从上⾯我们看到了NULL 和空字符串的区别。
任何和NULL 的⽐较操作，如<>、=、<=等都返回UNKNOWN（这⾥的unknown就是null，它单独使⽤和布尔值false类似）.
判断和⽐较规则总结如下：
判断和⽐较规则总结表
Oracle有nvl、nvl2、nullif、coalesce等函数专门处理null
5.1 nvl（expr1,expr2）
描述：如果expr1是null，那么⽤expr2作为返回值，不是null则返回pr1与expr2⼀般是类型相同的，如果类型不同
则会采⽤⾃动转换，转换失败则报错。
SQL*PLUS中数值类型右对齐，字符左对齐；通过第三条语句可以看出null和‘’还是有区别的。
SQL> select nvl(null,0) from dual;
NVL(NULL,0)
-----------
SQL> select nvl(to_char(null),0) from dual;
N
-
SQL> select nvl('',0) from dual;
N
-
5.2 nvl2函数
语法：nvl2（expr1,expr2,expr3)
描述：expr1如果是null，则返回expr3，否则返回expr2
expr2和expr3类型不同，expr3类型转换为expr2类型
SQL> select nvl2(null,'1',2) from dual;
N
-
2
expr2为null，返回值类型和expr3⼀致
SQL> select nvl2(null,null,2) from dual;
NVL2(NULL,NULL,2)
-----------------
2
SQL> select nvl2(null,null,'2') from dual;
N
-
2
不同类型之间的转换，⾃动转换不成功，则报错
SQL> select nvl2(null,1,'b') from dual;
select nvl2(null,1,'b') from dual
*
ERROR at line 1:
ORA-01722: invalid number（⽆效数字）
5.3 nullif函数
语法：nullif(expr1,expr2)
描述：判断expr1和expr2是否相等，若相等则返回null，否则返回expr1.要求expr1与expr2类型必须相同 SQL> select nullif(1,3) from dual;
NULLIF(1,3)
-----------
1
SQL> select nullif(1,1) from dual;
NULLIF(1,1)
-----------
SQL> select nullif('1',1) from dual;
select nullif('1',1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
SQL> select nullif('ab','ab ') from dual;
NU
--
SQL> select nullif(null,1) from dual;
select nullif(null,1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
SQL> select nullif(to_char(null),'1') from dual;
N
-
5.4 coalesce函数
语法：coalesce（expr1,expr2,…,exprn)
描述：从左到右返回第⼀个为⾮null的值，若所有的列表元素都为null，则返回null。要求所有都必须为同⼀类型。
SQL> select coalesce(null,null,null) from dual;
C
-
SQL> select coalesce(null,1,2) from dual;
COALESCE(NULL,1,2)
------------------
1
SQL> select coalesce(1,'1',1) from dual;
select coalesce(1,'1',1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
6 null与索引
Oracle中的B*Tree索引，并不存储全为null的列，
虽然在表中建⽴了符合UNIQUE 索引，但是全为null的⾏还是可以插⼊的，⽽不是全为null的重复⾏则不可以插⼊。因为在UNIQUE约束中，（null，null）和（null，null）是不同的，当然在其他⼀些情况，⽐如说分组、集合操作中都认为全是null是相等的
SQL> create table t(a number,b number);
Table created.
SQL> create unique index idx_t on t(a,b);
Index created.
SQL> insert into t values(null,null);
1 row created.
SQL> insert into t values(1,null);
1 row created.
SQL> insert into t values(null,1);
1 row created.
SQL> commit;
SQL> insert into t values(1,null);
insert into t values(1,null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_T) violated
7 null的排序
order by默认升序（asc），这时候null是排在最后的，如果指定降序那么null是排在最前⾯的，认为null最⼤。
但是可以⽤nulls first和nulls last进⾏调整。
SQL> select * from emp
2 order by comm asc;
SQL> select * from emp
2 order by comm desc;
SQL> select *from emp
2 order by comm asc nulls first;
SQL> select *from emp
2 order by comm desc nulls last;
8 null与性能的关系
Not null约束，定义约束是要付出消耗性能的代价的，由下⾯的测试可以看出虽然约束检查的很快，但
是有时候还是很消耗资源的，⾄少在这个例⼦上是这样的，不需要not null约束，除⾮必要，不要乱定义约束。
SQL> set serveroutput on
SQL> declare
2 v_value number not null :=0;
3 start_time number;
4 end_time number;
5 begin
6 start_time :=DBMS_UTILITY.GET_TIME;
7 FOR i in 0..100000000 LOOP
8 v_value :=i;
9 end LOOP;
10 end_time :=DBMS_UTILITY.GET_TIME;
11 DBMS_OUTPUT.PUT_LINE(end_time-start_time);
12 END;
13 /
1043
PL/SQL procedure successfully completed.
SQL> declare
2 v_value number;
3 start_time number;
4 end_time number;
5 begin
6 start_time :=DBMS_UTILITY.GET_TIME;
7 FOR i IN 0..100000000 LOOP
8 v_value :=i;
9 end LOOP;
isnull的用法10 end_time :=DBMS_UTILITY.GET_TIME;
11 DBMS_OUTPUT.PUT_LINE(end_time-start_time);
12 END;
13 /
767
9 动态语句中的绑定变量与null
在PL/SQL中动态SQL和动态PL/SQL经常使⽤绑定变量，这个绑定变量有个要求，就是不能直接传⼊字⾯量null值，因为
PL/SQL中动态语句要求传⼊的绑定变量必须是SQL类型，⽽字⾯量null是⽆类型的，null字⾯量传⼊是不可以的。当然可以采⽤多种⽅法，如果⼀定要传⼊null，则可以将null改为空字符串、TO_NUMBER,TO_CHAR,TO_DATE等函数进⾏转换，或定义⼀个未初始化的变量、直接传⼊变量等。
SQL> create table test(id number,name varchar2(10),birth date);
Table created.
SQL> insert into test values(1,'aa',SYSDATE);
1 row created.
SQL> insert into test values(null,'aa',SYSDATE);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> declare
2 v_sql varchar2(4000);
3 begin
4 v_sql :='update test set birth=:vbirth where id is null';
5 execute immediate v_sql using null;
6 commit;
7 end;
8 /
execute immediate v_sql using null;
*
ERROR at line 5:
ORA-06550: line 5, column 31:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
SQL> declare
2 v_sql varchar2(4000);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。