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()函数类似于程序中的if…else 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进行操作，采用依次判断表达式的方式完成，如果表达式1为null，则显示表达式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  寻香径  阅读(557)  评论(0)  编辑  收藏  举报