Sql Certificate 简明教程

SQL - Using Conditional Expressions

General Functions

通用函数用于处理数据库中的 NULL 值。通用 NULL 处理函数的目标是将 NULL 值替换为备用值。我们将在下面简要了解这些函数。

NVL

NVL 函数为 NULL 值替换一个备用值。

NVL( Arg1, replace_with )

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

如果 arg1 是字符值，则 Oracle 会将替换字符串转换为与 arg1 兼容的数据类型，然后再对其进行比较，并以 expr1 的字符集返回 VARCHAR2。如果 arg1 是数字，则 Oracle 会确定数字优先级最高的参数，将另一个参数隐式转换为该数据类型，并返回该数据类型。

下面的 SELECT 语句如果员工尚未分配给任何工作，即将显示“n/a”，即 JOB_ID 为 NULL。否则，它会显示实际 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 )

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

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

NULLIF

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

NULLIF (expr1, expr2)

请注意，第一个参数可以是求值为 NULL 的表达式，但不能为字面上的 NULL。函数执行需要两个参数。

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

SELECT	NULLIF (12, 12)
FROM DUAL;

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

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

COALESCE

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;

Conditional Functions

Oracle 提供条件函数 DECODE 和 CASE，即使在 SQL 语句中也可以施加条件。

The DECODE function

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

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

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

事实上，Oracle 在使用 DECODE 函数时将两个空值视为相等的。

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

DECOD
-----
EQUAL

如果表达式为 null，则 Oracle 返回第一个搜索的结果，该搜索也是 null。DECODE 函数中的组件数量最多为 255。

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

CASE expression

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