[Oracle数据库学习]三、单行函数（3）

D2

嵌套函数

单行函数可以嵌套；

嵌套函数的执行顺序是由内向外，如下面从F1开始到F2、F3，逐层计算。

F3(F2(F1(col, arg1), arg2), arg3)

SELECT employee_name, NVL(TO_CHAR(commission_pct),'Null')
FROM employees
WHERE commission_pct IS NULL;

EMPLOYEE_NAME	NVL(TO_CHAR(COMMISSION_PCT),'NULL')
Alice	Null

 注：这里的NVL函数要求输入的两个参数类型相同，先执行TO_CHAR后在执行NVL。

 

通用函数

下面的函数适用于任何数据类型，包括空值。

 

NVL函数

NVL(value1, value2)

将空值转换成指定值，可以是日期、字符、数字。

这个函数的意思是如果value1的值为null,那么函数返回value2的值  ,如果value1不为空,那么就返回value1的值。

需要注意的是：value1和value2要保持字段类型相同。

注：https://blog.csdn.net/caohaicheng/article/details/14455387

SELECT employee_name, salary, 
NVL(commission_pct, 0),(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

EMPLOYEE_NAME	SALARY	NVL(COMMISSION_PCT,0)	AN_SAL
Alice	10000	0	120000
Bob	5000	0.12	67200
Cindy	6000	0.1	79200
Donald	4000	0.3	62400
Elsa	8000	0.12	107520

 

NVL2函数

NVL2(value1, value2, value3)

这个函数的意思是如果value1的值为null，则函数返回value3，否则函数返回value2，也就是说函数永远不会返回value1。

需要注意的是：参数value2、value3可以是除了LONG类型之外的任意数据类型。

注：https://blog.csdn.net/caohaicheng/article/details/14455387

SELECT employee_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') income
FROM employees 
WHERE job_id IN ('AM', 'PM');

EMPLOYEE_NAME	SALARY	COMMISSION_PCT	INCOME
Alice	10000	(null)	SAL
Donald	4000	0.3	SAL+COMM

 

NULLIF函数

NULLIF(expression1, expression2)

给定两个参数expression1和expression2，如果两个参数相等，则返回NULL；否则就返回第一个参数。
等价于：

Case WHEN Expression1=Expression2 Then NULL 
        ELSE Expression1    

注：https://www.cnblogs.com/shy1766IT/p/6725581.html

SELECT employee_name, employee_id, department_id,
NULLIF(employee_id, department_id) result
FROM employees 
WHERE job_id IN ('AM', 'PM');

EMPLOYEE_NAME	EMPLOYEE_ID	DEPARTMENT_ID	RESULT
Alice	1	1	(null)
Donald	4	2	4

 

COALESCE函数

COALESCE(expression1, expression2, ..., expressionn)

依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

SELECT employee_name, commission_pct, salary, 
COALESCE(commission_pct, salary, 10) comm
FROM employees

EMPLOYEE_NAME	COMMISSION_PCT	SALARY	COMM
Cindy	0.1	6000	0.1
Bob	0.12	5000	0.12
Elsa	0.12	8000	0.12
Donald	0.3	4000	0.3
Alice	(null)	10000	10000

 注：Alice的commission_pct为空，因此返回salary。

条件表达式

两种方式：　CASE表达式和DECODE函数。

CASE表达式

CASE expr WHEN comparison_expr1 THEN return_expr1
        [WHEN comparison_expr2 THEN return_expr2
        WHEN comparison_exprn THEN return_exprn
        ELSE else_expr]
END

 

SELECT employee_name, job_id, salary,
CASE job_id WHEN 'DEV' THEN 1.10*salary
            WHEN 'QA' THEN 1.15*salary
            WHEN 'PM' THEN 1.20*salary
            ELSE salary 
END "REVISED_SALARY"
FROM employees;

EMPLOYEE_NAME	JOB_ID	SALARY	REVISED_SALARY
Alice	PM	10000	12000
Bob	DEV	5000	5500
Cindy	QA	6000	6900
Donald	AM	4000	4000
Elsa	DEV	8000	8800

 

DECODE函数

DECODE(col|expression, search1, result1 
                                [, search2, result2,...,]
                                [, default])                    

SELECT employee_name, salary,
DECODE (TRUNC(salary/2000, 0),0, 0.00,
        1, 0.09,
        2, 0.20,
        3, 0.30,
        4, 0.40,
        5, 0.42,
        6, 0.44,
        0.45) TAX_RATE
FROM employees
WHERE department_id = 1;

EMPLOYEE_NAME	SALARY	TAX_RATE
Alice	10000	0.42
Bob	5000	0.2

decode函数的两种形式

第一种形式：

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN
　　　　RETURN(返回值1)
ELSIF 条件=值2 THEN
　　　　RETURN(返回值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(返回值n)
ELSE
　　　　RETURN(缺省值)
END IF

第二种形式

decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是：当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3。
当然，值1、值2、值3也可以是表达式，这个函数使得某些sql语句简单了许多。
————————————————
注：

版权声明：本文为CSDN博主「qichangjian」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qichangjian/java/article/details/88975499

 

第一种形式：

SELECT employee_name, job_id, salary,
DECODE(job_id, 'DEV', 1.10*salary,
               'QA', 1.15*salary,
               'PM', 1.20*salary,
               salary) REVISED_SALARY
FROM employees;

EMPLOYEE_NAME	JOB_ID	SALARY	REVISED_SALARY
Alice	PM	10000	12000
Bob	DEV	5000	5500
Cindy	QA	6000	6900
Donald	AM	4000	4000
Elsa	DEV	8000	8800

注：job_id需要为varchar，不能是char。

第二种形式：

SELECT employee_name, job_id, salary,
DECODE(job_id, 'DEV', 1.10*salary, salary) REVISED_SALARY
FROM employees;

EMPLOYEE_NAME	JOB_ID	SALARY	REVISED_SALARY
Alice	PM	10000	10000
Bob	DEV	5000	5500
Cindy	QA	6000	6000
Donald	AM	4000	4000
Elsa	DEV	8000	8800

 

总结：

本节介绍了嵌套函数、通用函数和条件表达式。

1）嵌套函数：单行函数可以嵌套，执行从内到外；

2）通用函数：NVL、NVL2、NULLIF、COALESCE；

3）条件表达式：CASE、DECODE。