luobo2ni 发表于 2018-9-11 07:11:26

oracle中null值的使用

  oracle中null值的使用
  
  在Oracle数据库中,如果一个表中的列没有值的话,我们可以说是空值,比如IT员工,假如没有提成的话,提成列就应该是空。对于空值,Oracle数据库使用null来表示,空即没有的意思。null不是空格,空格是字符类型,表示一个字符,null也不是0,0是数字类型,是一个数值。在通常的B*Tree索引中,null值是不存储的,所以如果where的谓语是column=null的话,将不能使用到索引。
  我们来看下面的一个小例子,为了举例,我建了一张测试表DEMO
  scott@DB01> create table demo as select empno,ename,sal,comm,mgr,deptno from emp;
  Table created.
  scott@DB01> select * from demo;
  EMPNO ENAME             SAL       COMM      MGR   DEPTNO
  ---------- ---------- ---------- ---------- ---------- ----------
  7369 SMITH             800                  7902         20
  7499 ALLEN            1600      300       7698         30
  7521 WARD             1250      500       7698         30
  7566 JONES            2975                  7839         20
  7654 MARTIN         1250       1400       7698         30
  7698 BLAKE            2850                  7839         30
  7782 CLARK            2450                  7839         10
  7788 SCOTT            3000                  7566         20
  7839 KING             5000                               10
  7844 TURNER         1500          0       7698         30
  7876 ADAMS            1100                  7788         20
  7900 JAMES             950                  7698         30
  7902 FORD             3000                  7566         20
  7934 MILLER         1300                  7782         10
  14 rows selected.
  与Sql Server数据库不同的是,在Oracle数据库当中,null也不等于null,如果我们要通过条件筛选找到具有空值的记录,需要使用is(not) null 方法。
  scott@DB01> select empno,ename,sal from demo where comm=null;
  no rows selected
  这里我们留下一个小的疑问,where后面的表达式,返回的是null(空)还是false(假)?
  scott@DB01> select empno,ename,sal from demo where comm is null;
  EMPNO ENAME             SAL
  ---------- ---------- ----------
  7369 SMITH             800
  7566 JONES            2975
  7698 BLAKE            2850
  7782 CLARK            2450
  7788 SCOTT            3000
  7839 KING             5000
  7876 ADAMS            1100
  7900 JAMES             950
  7902 FORD             3000
  7934 MILLER         1300
  10 rows selected.
  其实在Oracle数据库中,我们一般这样来定义,在一个表达式当中,如果有null值的存在,将导致整个表达式为空。当然这也不是绝对的正确,对于Oracle来说,条件判定表达式就是例外。
  下面的语句是由于null从而导致整个表达式为空的例子(例子中计算全年工资包括提成),当然就这个问题而言,很好解决,我们可以使用NVL函数.
  scott@DB01> select empno,ename,sal,sal*12+comm from demo;
  EMPNO ENAME             SAL SAL*12+COMM
  ---------- ---------- ---------- -----------
  7369 SMITH             800
  7499 ALLEN            1600       19500
  7521 WARD             1250       15500
  7566 JONES            2975
  7654 MARTIN         1250       16400
  7698 BLAKE            2850
  7782 CLARK            2450
  7788 SCOTT            3000
  7839 KING             5000
  7844 TURNER         1500       18000
  7876 ADAMS            1100
  7900 JAMES             950
  7902 FORD             3000
  7934 MILLER         1300
  14 rows selected.
  很明显,由于有的员工comm(提成)为null,导致计算的工资(SAL*12+COMM)为空,这样明显是不对!!!
  正确写法,使用了NVL函数
  scott@DB01> select empno,ename,sal,sal*12+nvl(comm,0)tsal from demo;
  EMPNO ENAME             SAL       TSAL
  ---------- ---------- ---------- ----------
  7369 SMITH             800       9600
  7499 ALLEN            1600      19500
  7521 WARD             1250      15500
  7566 JONES            2975      35700
  7654 MARTIN         1250      16400
  7698 BLAKE            2850      34200
  7782 CLARK            2450      29400
  7788 SCOTT            3000      36000
  7839 KING             5000      60000
  7844 TURNER         1500      18000
  7876 ADAMS            1100      13200
  7900 JAMES             950      11400
  7902 FORD             3000      36000
  7934 MILLER         1300      15600
  14 rows selected.
  我们再来看下面的非常简单的子查询的例子
  scott@DB01> select empno,ename from demo
  where empno not in (select mgr from demo);
  no rows selected
  如果我们简单来理解字面的意思的话,上面的Sql语句要实现的是:查找DEMO表中纯粹的员工,也就是干活的,呵呵,但是上边的语句并没有得到正确的结果。为什么呢?你是否可以这样来理解,not in 相当于 !=and!=and != 这样的结构呢?
  通过下面的查询,我们发现7839的mgr为空,也就是说,在我们上边的子查询中,有一个值为空
  scott@DB01> select empno,ename,mgr from demo;
  EMPNO ENAME          MGR
  ---------- ----------         ----------
  7369 SMITH            7902
  7499 ALLEN            7698
  7521 WARD             7698
  7566 JONES            7839
  7654 MARTIN         7698
  7698 BLAKE            7839
  7782 CLARK            7839
  7788 SCOTT            7566
  7839 KING
  7844 TURNER         7698
  7876 ADAMS            7788
  7900 JAMES            7698
  7902 FORD             7566
  7934 MILLER         7782
  14 rows selected.
  select empno,ename from demo where empno not in (select mgr from demo);这个SQL的条件部分可以转换为empno!=7902 and empno!=7698 and ......and empno !=null(引号部分做了省略),对于empno!=null 返回的值还是null,对于这样的逻辑表达式来说,true and null的结果是null,从而导致整个表达式为空,在这里null作为假条件来处理,所以我们看不到查询结果也就可以理解了。
  再来看下面的语句,我们把NOT IN修改成IN ,却发现是可以得到正确的结果的。不过,我们应该明白,要实现的目的完全相反了,我们找到的是经理,哪怕他手下只有一个小兵。
  scott@DB01> select empno,ename from demo where empnoin (select mgr from demo);
  EMPNO ENAME
  ---------- ----------
  7902 FORD
  7698 BLAKE
  7839 KING
  7566 JONES
  7788 SCOTT
  7782 CLARK
  6 rows selected.
  为什么呢?select empno,ename from demo where empnoin (select mgr from demo);这个SQL我们可以把条件部分转换为empno = 7902 or empno=7698 or ......or empno=null,对于empno=null 返回的值还是null,但是对于这样的逻辑表达式来说,true or null的结果是true,所以我们可以看到查询的结果。
  相同的思路,我们可以用集合操作来实现(很明显是差集)
  select empno,ename,job from emp
  minus
  select empno,ename,jobfrom emp where empno in ( select mgr from emp);
  那对于我们想要找到纯粹的员工,应该怎样来写呢?很简单,null值对于本例的结果没有影响,通过条件过滤掉就可以了。语句如下
  scott@DB01> select empno,ename from demo
  2>where empno not in (select mgr from demo where mgr is not null);
  EMPNO ENAME
  ---------- ----------
  7369 SMITH
  7499 ALLEN
  7521 WARD
  7654 MARTIN
  7844 TURNER
  7876 ADAMS
  7900 JAMES
  7934 MILLER
  8 rows selected
  空值在排序的时候总是出现在大值的一端,但有时候也会带来一些麻烦
  scott@DB01> select * from demo;
  EMPNO ENAME             SAL       COMM   DEPTNO
  ---------- ---------- ---------- ---------- ----------
  7369 SMITH             800                  20
  7499 ALLEN            1600      300         30
  7521 WARD             1250      500         30
  7566 JONES            2975                  20
  7654 MARTIN         1250       1400         30
  7698 BLAKE            2850                  30
  7782 CLARK            2450                  10
  7788 SCOTT            3000                  20
  7839 KING             5000                  10
  7844 TURNER         1500          0         30
  7876 ADAMS            1100                  20
  7900 JAMES             950                  30
  7902 FORD             3000                  20
  7934 MILLER         1300                  10
  14 rows selected.
  当我们根据comm列做降序排序的时候,空值显示到了上边
  scott@DB01> select * from demo order by comm desc;
  EMPNO ENAME             SAL       COMM   DEPTNO
  ---------- ---------- ---------- ---------- ----------
  7369 SMITH             800                  20
  7782 CLARK            2450                  10
  7902 FORD             3000                  20
  7900 JAMES             950                  30
  7876 ADAMS            1100                  20
  7566 JONES            2975                  20
  7698 BLAKE            2850                  30
  7934 MILLER         1300                  10
  7788 SCOTT            3000                  20
  7839 KING             5000                  10
  7654 MARTIN         1250       1400         30
  7521 WARD             1250      500         30
  7499 ALLEN            1600      300         30
  7844 TURNER         1500          0         30
  14 rows selected.
  为了过滤掉空值,我们可以借助nulls last
  scott@DB01> select * from demo order by comm desc nulls last;
  EMPNO ENAME             SAL       COMM   DEPTNO
  ---------- ---------- ---------- ---------- ----------
  7654 MARTIN         1250       1400         30
  7521 WARD             1250      500         30
  7499 ALLEN            1600      300         30
  7844 TURNER         1500          0         30
  7788 SCOTT            3000                  20
  7839 KING             5000                  10
  7876 ADAMS            1100                  20
  7900 JAMES             950                  30
  7902 FORD             3000                  20
  7934 MILLER         1300                  10
  7698 BLAKE            2850                  30
  7566 JONES            2975                  20
  7369 SMITH             800                  20
  7782 CLARK            2450                  10
  14 rows selected.
  我们在开始的时候提了个小问题,comm=null,返回的结果是null还是false呢?
  其实这个问题可以扩展,”=”可以用”>”、” set serveroutput on
  scott@DB01> declare
  2       i boolean;
  3begin
  4       i := false;
  5       if (null=1) = i then
  6          dbms_output.put_line('return false!');
  7       elsif (null=1) is null then
  8          dbms_output.put_line('return null!');
  9       end if;
  10end;
  11/
  return null!
  PL/SQL procedure successfully completed.
NULL 不 大于/小于/等于/不等于 任何值(包括NULL本身),有且仅有:NULL IS NULL
  begin
  if(2>null) then
  dbms_output.put_line('2>null');
  elsif(2null) then
  dbms_output.put_line('null>null');
  elsif(null
页: [1]
查看完整版本: oracle中null值的使用