使用条件表达式

一般功能

通用函数用于处理数据库中的 NULL 值。一般 NULL 处理函数的目标是用替代值替换 NULL 值。我们将在下面简要介绍这些功能。

NVL

NVL 函数用替代值替换 NULL 值。

句法：

NVL( Arg1, replace_with )

在语法中，这两个参数都是必需的。请注意，NVL 函数适用于所有类型的数据类型。并且原始字符串和替换的数据类型必须处于兼容状态，即相同或由 Oracle 隐式转换。

如果 arg1 是字符值，则 oracle 在比较之前将替换字符串转换为与 arg1 兼容的数据类型，并返回 expr1 字符集中的 VARCHAR2。如果 arg1 是数字，则 Oracle 确定具有最高数字优先级的参数，将另一个参数隐式转换为该数据类型，并返回该数据类型。

如果员工尚未分配到任何工作，即 JOB_ID 为 NULL，则下面的 SELECT 语句将显示“n/a”。否则，它将显示实际的 JOB_ID 值。

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

作为对 NVL 的增强，Oracle 引入了一个函数，不仅可以替换 NULL 列的值，还可以替换 NOT NULL 列的值。NVL2 函数可用于替换 NULL 和非 NULL 值的替代值。

句法：

NVL2( string1, value_if_NOT_null, value_if_null )

如果员工的 JOB_CODE 为 NULL，则下面的 SELECT 语句将显示“Bench”。对于 JOB CODE 的明确非空值，它将显示常量值“Job Assigned”。

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

NULLIF 函数比较两个参数 expr1 和 expr2。如果 expr1 和 expr2 相等，则返回 NULL；否则，它返回 expr1。与其他空处理函数不同，第一个参数不能为空。

句法：

NULLIF (expr1, expr2)

请注意，第一个参数可以是计算结果为 NULL 的表达式，但它不能是文字 NULL。这两个参数都是函数执行所必需的。

以下查询返回 NULL，因为两个输入值 12 相等。

SELECT	NULLIF (12, 12)
FROM DUAL;

同样，下面的查询返回 ‘SUN’，因为两个字符串不相等。

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

合并

COALESCE 函数是 NVL 的一种更通用的形式，它返回参数列表中的第一个非空表达式。它需要最少两个强制参数，但最大参数没有限制。

句法：

COALESCE (expr1, expr2, ... expr_n )

考虑下面的 SELECT 查询。它选择输入到员工地址字段的第一个非空值。

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

有趣的是，COALESCE 函数的工作类似于 IF..ELSIF..ENDIF 构造。上面的查询可以重写为 –

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

条件函数

Oracle 提供了条件函数 DECODE 和 CASE 以在 SQL 语句中强加条件。

解码功能

该函数是 IF..THEN..ELSE 条件过程语句的 SQL 等价物。DECODE 适用于所有数据类型的值/列/表达式。

句法：

DECODE (expression, search, result [, search, result]... [, default])

DECODE 函数按顺序将表达式与每个搜索值进行比较。如果表达式和搜索参数之间存在相等，则返回相应的结果。如果不匹配，则返回默认值，如果已定义，则返回 NULL。在任何类型兼容性不匹配的情况下，oracle 内部会进行可能的隐式转换以返回结果。

事实上，在使用 DECODE 函数时，Oracle 认为两个空值是等效的。

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

如果 expression 为空，则 Oracle 返回第一次搜索的结果，该结果也为空。DECODE 函数中的最大组件数为 255。

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

CASE 表达式

CASE 表达式的工作原理与 DECODE 相同，但在语法和用法上有所不同。

句法：

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

Oracle 搜索从左开始向右移动，直到找到真条件，然后返回与其关联的结果表达式。如果没有发现任何条件为真，并且存在 ELSE 子句，则 Oracle 返回用 else 定义的结果。否则，Oracle 返回 null。

CASE 表达式中的最大参数数为 255。所有表达式都计入此限制，包括简单 CASE 表达式的初始表达式和可选的 ELSE 表达式。每个 WHEN … THEN 对算作两个参数。为避免超出此限制，您可以嵌套 CASE 表达式，以便 return_expr 本身是一个 CASE 表达式。

SELECT first_name, CASE	WHEN salary < 200 THEN 'GRADE 1'
			WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
			ELSE 'GRADE 3'
		   END CASE
FROM employees;	

ENAM    CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1