SQL - 使用条件表达式

通用函数

通用函数用于处理数据库中的 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 列值替换值，还可以为非 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;

条件函数

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

DECODE 函数

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

语法

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

DECODE 函数按顺序将表达式与每个搜索值进行比较。如果表达式和搜索参数之间存在相等性，则返回相应的result。如果没有匹配，则返回默认值（如果已定义），否则返回 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 表达式

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

打印页面