Sql Certificate 简明教程

SQL - Using Conditional Expressions

General Functions

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

General functions are used to handle NULL values in database. The objective of the general NULL handling functions is to replace the NULL values with an alternate value. We shall briefly see through these functions below.

NVL

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

The NVL function substitutes an alternate value for a NULL value.

NVL( Arg1, replace_with )

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

In the syntax, both the parameters are mandatory. Note that NVL function works with all types of data types. And also that the data type of original string and the replacement must be in compatible state i.e. either same or implicitly convertible by Oracle.

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

If arg1 is a character value, then oracle converts replacement string to the data type compatible with arg1 before comparing them and returns VARCHAR2 in the character set of expr1. If arg1 is numeric, then Oracle determines the argument with highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

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

The SELECT statement below will display 'n/a' if an employee has been not assigned to any job yet i.e. JOB_ID is NULL. Otherwise, it would display the actual JOB_ID value.

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

NVL2

作为 NVL 的增强，Oracle 引入了一个函数来替代不仅是 NULL 列的值，还有 NOT NULL 列的值。NVL2 函数可用于替换 NULL 及非 NULL 值的备用值。

As an enhancement over NVL, Oracle introduced a function to substitute value not only for NULL columns values but also for NOT NULL columns. NVL2 function can be used to substitute an alternate value for NULL as well as non NULL value.

NVL2( string1, value_if_NOT_null, value_if_null )

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

The SELECT statement below would display 'Bench' if the JOB_CODE for an employee is NULL. For a definite not null value of JOB CODE, it would show constant value 'Job Assigned'.

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

NULLIF

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

The NULLIF function compares two arguments expr1 and expr2. If expr1 and expr2 are equal, it returns NULL; else, it returns expr1. Unlike the other null handling function, first argument can’t be NULL.

NULLIF (expr1, expr2)

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

Note that first argument can be an expression that evaluates to NULL, but it can’t be the literal NULL. Both the parameters are mandatory for the function to execute.

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

The below query returns NULL since both the input values, 12 are equal.

SELECT	NULLIF (12, 12)
FROM DUAL;

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

Similarly, below query return 'SUN' since both the strings are not equal.

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

COALESCE

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

COALESCE function, a more generic form of NVL, returns the first non-null expression in the argument list. It takes minimum two mandatory parameters but maximum arguments has no limit.

COALESCE (expr1, expr2, ... expr_n )

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

Consider the below SELECT query. It selects the first not null value fed into address fields for an employee.

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

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

Interestingly, the working of COALESCE function is similar to IF..ELSIF..ENDIF construct. The query above can be re-written as -

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 语句中也可以施加条件。

Oracle provides conditional functions DECODE and CASE to impose conditions even in SQL statement.

The DECODE function

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

The function is the SQL equivalence of IF..THEN..ELSE conditional procedural statement. DECODE works with values/columns/expressions of all data types.

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

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

DECODE function compares expression against each search value in order. If equality exists between expression and search argument, then it returns the corresponding result. In case of no match, default value is returned, if defined, else NULL. In case of any type compatibility mismatch, oracle internally does possible implicit conversion to return the results.

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

As a matter of fact, Oracle considers two nulls to be equivalent while working with DECODE function.

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

DECOD
-----
EQUAL

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

If expression is null, then Oracle returns the result of the first search that is also null. The maximum number of components in the DECODE function is 255.

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

CASE expression

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

CASE expressions works on the same concept as DECODE but differs in syntax and usage.

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。

Oracle search starts from left and moves rightwards until it finds a true condition, and then returns result expression associated with it. If no condition is found to be true, and an ELSE clause exists, then Oracle returns result defined with else. Otherwise, Oracle returns null.

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

The maximum number of arguments in a CASE expression is 255. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN …​ THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself is a CASE expression.

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