SQL Fundamentals || Single-Row Functions || 通用函数 General function || (NVL,NVL2,NULLIF,DECODE,CASE,COALESCE)

 SQL Fundamentals || Oracle SQL语言

 

 

 

Oracle自己提供的有特色的函数;

 

NVL()DECODE()是通用函数的基础函数,其他函数都在此函数之上进行功能扩充。

 

函数名称

描述

NVL(数字 | 列 , 默认值)

如果显示的数字是null的话,则使用默认数值表示

使用NVL()函数处理null

    • 在数据库之中,null是无法进行计算的,即,在一个数学计算之中如果存在了null,则最后的结果也肯定是null
    • 使用NVL()函数解决年薪为null的情况
      • SELECT empno,ename,job,hiredate,(sal + NVL(comm,0)) * 12 年薪 FROM emp ;

NVL2(数字 | 列 , 返回结果一(不为空显示) , 返回结果二(为空显示))

判断指定的列是否是null,如果不为null则返回结果一,为空则返回结果二

    • NVL2()函数是在Oracle 9i之后增加的一个新的功能函数,相比较NVL()函数,NVL2()函数可以同时对为null或不为null进行分别判断并返回不同的结果。
    • 查询每个雇员的编号、姓名、年薪(sal + comm)、基本工资、奖金
      • SELECT empno , ename , NVL2(comm , sal+comm,sal) , sal , comm FROM emp ;

NULLIF(表达式一,表达式二)

比较表达式一和表达式二的结果是否相等,如果相等返回NULL,如果不等返回表达式一

      • NULLIF(表达式一, 表示式二)函数的主要功能判断两个表达式的结果是否相等,如果相等则返回NULL,不相等则返回表达式一。
      • 验证NULLIF()函数
        • SELECT NULLIF(1,1) , NULLIF(1,2) FROM dual ;
        • 验证NULLIF()函数
          • SELECT empno , ename , job , LENGTH(ename) , LENGTH(job) , NULLIF(LENGTH(ename),LENGTH(job)) nullif

FROM emp ;

DECODE(列 | 值 , 判断值1,显示结果1, 判断值2,显示结果2,…, 默认值)

 

java语言中,if…else判断都是逻辑条件,而在DECODE()里面判断的就是数值.

多值判断,如果某一个列(或某一个值)与判断值相同,则使用指定的显示结果输出,如果没有满足条件,则显示默认值

      • DECODE()函数是Oracle中最有特色的一个函数,DECODE()函数类似于程序中的ifelse if…else if…else,但是判断的内容都是一个具体的值,DECODE()函数的语法如下:
        • DECODE( | 表达式,1,输出结果,2,输出结果,... , 默认值)
      • 测试DECODE()函数
        • SELECT DECODE(2,1,'内容为一',2,'内容为二') , DECODE(2,1,'内容为一','没有条件满足') FROM dual ;
      • 现在雇员表中的工作有以下几种:
        • CLERK:业务员
        • SALESMAN:销售人员
        • MANAGER:经理
        • ANALYST:分析员
        • PRESIDENT:总裁
      • 要求可以查询雇员的姓名、职位、基本工资等信息,但是要求将所有的职位信息都替换为中文显示。
        • SELECT ename, sal ,

        DECODE(job,

              'CLERK','业务员',

              'SALESMAN','销售人员',

              'MANAGER','经理',

              'ANALYST','分析员',

              'PRESIDENT','总裁') job

FROM emp ;

      • DECODE()函数中只判断部分内容
        • SELECT ename, sal ,

        DECODE(job,

              'CLERK','业务员',

              'SALESMAN','销售人员',

              'MANAGER','经理') job

FROM emp ;

    • 没有判断的内容会变为空,所以要使用DECODE函数就要全部判断.

CASE 列 | 数值 WHEN 表达式1 THEN 显示结果1 … ELSE 表达式N …END

用于实现多条件判断,在WHEN之后编写条件,而在THEN之后编写条件满足的显示操作,如果都不满足则使用ELSE中的表达式处理

      • CASE表达式是在Oracle 9i引入的,功能与DECODE()函数有些类似,都是执行多条件判断。不过严格来讲CASE表达式本身并不属于一种函数的范畴,它的主要功能是针对于给定的列或者是字段进行依次判断,在WHEN中编写判断语句,而在THEN中编写处理语句,最后如果都不满足则使用ELSE进行处理。
      • 显示每个雇员的姓名、工资、职位,同时显示新的工资(新工资的标准为:办事员增长10%、销售人员增长20%、经理增长30%、其他职位的人增长50%
        • SELECT ename, sal ,

        CASE job WHEN 'CLERK' THEN sal * 1.1

                  WHEN 'SALESMAN' THEN sal * 1.2

                  WHEN 'MANAGER' THEN sal * 1.3

        ELSE      sal * 1.5

        END 新工资

FROM emp ;

COALESCE(表达式1, 表达式2 , …表达式n)

将表达式逐个判断,如果表达式1的内容是null,则显示表达式2,如果表达式2的内容是null,则显示表达式3,依次类推,如果表达式n的结果还是null,则返回null

      • COALESCE(表达式1,表达式2,表达式3,…表达式n)函数的主要功能是对null进行操作,采用依次判断表达式的方式完成,如果表达式1null,则显示表达式2的内容,如果表达式2的内容为空,则显示表达式3的内容,依次类推,判断到最后如果还是null,则最终的显示结果就是null
      • 验证COALESCE()函数的功能
        • SELECT ename, sal, comm, COALESCE(comm,100,2000) , COALESCE(comm,null,null)

FROM emp

 

posted @ 2017-07-24 19:18  寻香径  阅读(566)  评论(0编辑  收藏  举报