关于oracle空值的解决方法
软件学院
关于oracle的空值问题及解决方法
专业班级：
学号：
姓名：
关于oracle空值的解决方法
一、引言
在做“查询年工资（sal+comm）*12>5000”的练习题时，发现查询结果并不全面，因为有的员工的comm为空，导致查询结果并不全面（comm为空的行并未显示）。经过上网查找资料及亲自验证，确认使用函数nvl（comm，0）可解决该问题。
二、空值的生成及特点
1. 空值的生成
如果一列没有非空（NOT NULL）完整性限制，那么其缺省的值为空值，即如果插入一行时未指定该列的值，则其值为空值。
使用SQL语句INSERT插入行，凡未涉及到的列，其值为空值；涉及到的列，如果其值确实为空值，插入时可以用NULL来表示（对于字符型的列，也可以用''来表示）。
2. 空值的特点
空值没有任何值与 0、空字符串或空格不同。在where条件中, Oracle认为结果为NULL的条件为FALSE 带有这样条件的select语句不返回行 并且不返回错误信息。但NULL和FALSE是不同的。空值在排序时比其他数据都大并且空值不能索引。
- 1 -
- 2 -
三、遇到的问题
输入命令SQL> select * from emp ;的结果如图1-1所示。
图1-1 查询员工表结果
输入SQL> select ename,sal,comm from emp where(sal+comm)*12>5000;
查询员工年工资的结果如图1-2所示。
图1-2 查询员工年工资结果
输入SQL> select ename,sal,comm from emp where sal>2000;查询月工资大于2000的员工，可看出该类员工的年工资均大于5000，但在上一个查询语句的结果中并没有显示他们，结果如图1-3所示。
图1-3 查询员工工资大于2000的结果
四、解决方案及示例
运用函数nvl（comm，0）可解决空值的问题，nvl（comm，0）表示当comm 为空值的时候，将其赋值为0，这样再次查询员工年工资的时候，得到的结果就全面了。
下面用命令行的方式一步步显示解决方法：
SQL> select * from emp; //查看emp的表结构
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月-81
- 3 -
1600 300 30
7521 W ARD SALESMAN 7698 22-2月-81 1250 500 30
7566 JONES MANAGER 7839 02-4月-81 2975 20
7654 MARTIN SALESMAN 7698 28-9月-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月-81 2850 30
7782 CLARK MANAGER 7839 09-6月-81 2450 10
7788 SCOTT ANALYST 7566 19-4月-87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月-81 1500 0 30
7876 ADAMS CLERK 7788 23-5月-87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月-82 1300 10
已选择14行。
SQL> conn sys/Oracle11 as sysdba;
已连接。
SQL> grant global query rewrite to scott; //授权
授权成功。
SQL> grant create any index to scott;
授权成功。
SQL> conn scott/admin;
已连接。
SQL> create index sal_comm on emp ((sal+comm)*12,sal,comm) tablespace users; //创建基于函数的索引
索引已创建。
- 4 -
SQL> select ename,sal,comm from emp where(sal+comm)*12>5000; //未使用函数nvl（comm，0）查询一年工资大于五千的员工
ENAME SAL COMM
---------- ---------- ----------
TURNER 1500 0
W ARD 1250 500
ALLEN 1600 300
MARTIN 1250 1400
SQL> select ename,sal,comm from emp where sal>2000; //查询月工资大于2000的员工，可看出该类员工的年工资均大于5000，
但在上一个查询语句的结果中并没有显示他们
ENAME SAL COMM
---------- ---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
已选择6行。
SQL> select ename,sal,comm,sal+comm from emp; //查询ename，sal，comm，sal+comm
ENAME SAL COMM SAL+COMM
---------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 300 1900
W ARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
- 5 -
已选择14行。
SQL> --由上面的两个查询结果可知，任何含有空值的算术表达式其运算结果都为空值，即：凡是comm为空值的行，sal+comm均为空值
SQL> --使用函数nvl（comm，0）后的操作及查询结果
SQL> select ename,sal,comm,sal+nvl(comm,0) from emp where (sal+nvl(comm,0))*12>5000;
ENAME SAL COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
SMITH 800 800
ALLEN 1600 300 1900
W ARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 0 1500
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
已选择14行。
由此可见，通过函数nvl（comm，0）可解决出现的空值问题。
五、结论
在解决本次课堂练习中的空值问题的过程中，让我明白了，作为一名学习软件工程专业的学生，应该加强上机操作的能力，即提高实际动手的能力，只有在实践中才能发现问题。而且，解决问题的过程虽然很费事，需要查询很多相关资料和亲自的验证，但解决问题的过程中我们拓宽了知识，在解决问题后内心也得到了极大的满足。在今后的学习中，我会更加注重实际操作的过程和提高解决问题的能力。
- 6 -