使用条件表达式

常规函数

常规函数用于处理数据库中的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.与其他null处理函数不同，第一个参数不能为NULL.

语法:

 
 NULLIF (expr1，expr2)

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

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

 
 SELECT NULLIF(12,12)
 FROM DUAL;

同样，在查询下方返回'SUN'，因为两个字符串不相等.

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

COALESCE

COALESCE函数，一种更通用的NVL形式，返回参数中的第一个非null表达式名单.它至少需要两个必需参数，但最大参数没有限制.

语法:

 
 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(表达式，搜索，结果[，搜索，结果] ... [，默认])

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

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

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

DECOD
-----
EQUAL

如果expression为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