对于NULL值的处理-Oracle

之前使用Mysql的比较多,所以有意识的认为Oracle对于NULL的处理与Mysql是一样,促成这种错误的认识。

一、查询数据

假设查询EMP[Table],员工没有佣金的数据,查询条件:comm = 0

1
SELECT * FROM EMP WHERE COMM=0;

Mysql: 能直接带出满足字段条件的记录,无论是NULL值

Oracle: 显然是行不通的,需要对NULL值进行预处理,才能带出查询的记录,否则查询的结果没有NULL值的结果。

二、解决办法

1. 使用NVL2(expr1,expr2,expr3)函数

功能:

如果参数表达式expr1值为NULL,则NVL2()函数返回参数表达式expr3的值;

如果参数表达式expr1值不为NULL,则NVL2()函数返回参数表达式expr2的值。

即:NVL2(表达式,不为空设值,为空设值)。

因此,将上述sql语句改成:

1
SELECT * FROM EMP WHERE NVL2(COMM,COMM,0) = 0;

2. 使用DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

功能:

value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。

当每个value值被测试:

如果value的值为if1,Decode 函数的结果是then1;

如果value等于if2,Decode函数结果是then2;

如果value结果不等于给出的任何配对时,Decode 结果就返回else 。

因此,将上述sql语句改成:

1
SELECT * FROM EMP WHERE DECODE(COMM,NULL,0) = 0;

三、问题原因

先看三个例子:

1. 例子

1
2
3
4
5
6
7
8
9
10
11
declare
a number(2):= 1;
b number(3):= null;
begin
if a = b then
dbms_output.put_line('a等于b');
else
dbms_output.put_line('a不等于b');
end if;
end;
--输出:a不等于b

上述例子,很显然输出:a不等于b,后面的例子结果如何呢?

2. 例子

1
2
3
4
5
6
7
8
9
10
11
declare
a number(2):= 1;
b number(3):= null;
begin
if a <> b then
dbms_output.put_line('a不等于b');
else
dbms_output.put_line('a等于b');
end if;
end;
--输出:a等于b

上述例子,竟然出乎意料的输出:a等于b,为什么?
其实,任何值与null对比,返回的结果都是null,这就是例子2输出的结果是:a等于b。

3. 例子

1
2
3
4
5
6
7
8
9
10
11
declare
a number(2):= 1;
b number(3):= null;
begin
if a is not null and b is not null and a=b then
dbms_output.put_line('a等于b');
else
dbms_output.put_line('a不等于b');
end if;
end;
--输出:a等于b

上述例子,对于值的比较,是先判断值是否为null,再判断值的大小。这样子才能保证值正确的比较。

评论