8.4 在SELECT语句中应用条件表达式
前面简要介绍了嵌套函数，本节将正式讨论这个概念。同时还介绍能够处理NULL值和支持表达式中的条件逻辑的条件函数。
8.4.1 嵌套函数
嵌套函数使用一个函数的输出作为另一个函数的输入。函数总是返回一个结果。因此，当给函数提供输入参数时，可以像考虑字面值那样考虑函数调用。可以将单行函数嵌套到任何深度。函数的一般形式如下所示：
Function1(parameter1, parameter2, ...) = result1
用函数调用替换其他函数的参数会生成一个表达式，如下所示：
F1( param1.1, F2( param2.1, param2.2, F3( param3.1)), param1.3)
先计算嵌套函数，之后再将它们的返回值作为其他函数的参数输入。按照由内向外的顺序计算嵌套函数。前面的表达式可以像下面这样计算：
(1) 计算F3(param3.1) ，其返回值作为函数F2的第三个参数，可以称之为：param2.3.
(2) 计算F2(param2.1, param2.2, param2.3)，其返回值作为函数F1的第二个参数，即param1.2。
(3) 计算F1(param1.1, param1.2, param1.3)，将结果返回到调用程序。
本示例中函数F3嵌套了三层。考察下面的查询：
select next_day(last_day(sysdate)-7, 'tue') from dual;
SELECT列表中有三个函数，由内到外分别是SYSDATE、LAST_DAY和NEXT_DAY。查询的计算如下所述：
(1) 首先计算最里面的函数。SYSDATE返回当前日期。假定当前日期为28-OCT-2009。
(2) 接着计算次内层的函数。LAST_DAY('28-OCT-2009')返回十月最后一天的日期，即31-OCT- 2009。
最后计算NEXT_DAY('24-OCT-2009', 'tue')函数，该查询返回当月最后一个星期二的日期，在本例中为27-OCT-2009。
提示：
我们通常都希望构造一个由许多嵌套函数调用组成的复杂表达式，但这种方法需要许多实践和经验。形成查询解决方案的概念，并将它分解为组件函数调用。DUAL表对于单独函数调用的专门测试和调试非常有用。测试和调试较小的组件并不断组合它们，直到形成最终的表达式为止。
8.4.2 条件函数
条件逻辑也称为if-then-else逻辑，它表示依据满足某种条件的数据值选择执行路径。条件函数依据计算比较条件来返回不同值。这一类别中的函数简化了NULL值的处理，包括NVL、NVL2、NULLIF和COALESCE函数。通用条件逻辑由DECODE函数和CASE表达式实现。DECODE函数专用于Oracle，而CASE表达式是与ANSI SQL兼容的。
1．NVL函数
NVL函数评估任何数据类型的列或者表达式是不是空值。如果原始项是空值，返回备选的非空值；否则，返回原始项。
NVL函数有两个强制参数。其语法是NVL(original, ifnull)，其中original表示要测试的项，如果original项计算为空，就返回ifnull。original和ifnull参数的数据类型必须一致。它们必须是相同的类型，或者可能将ifnull隐式转换为original参数的类型。NVL函数返回值的数据类型与original参数的数据类型相同。考察下面三个查询：
查询1: select nvl(1234) from dual;
查询2: select nvl(null, 1234) from dual;
查询3: select nvl(substr('abc', 4), 'No substring exists') from dual;
因为NVL函数有两个强制参数，因此查询1返回错误“ORA-00909: invalid number of arguments”。查询2在测试null关键字并发现它为空值之后，返回1234。查询3包含一个嵌套的SUBSTR函数，它试图从三个字符的字符串中提取第四个字符，但函数返回空值，接着执行NVL (null, 'No substring exists')函数，它返回字符串’No substring exists'。
提示：
NVL函数可用于将NULL数字值转换为0，因此基于NVL函数的算法不会返回NULL值。
2．NVL2函数
NVL2函数是对NVL函数的增强，但功能非常类似。NVL2函数评估任何数据类型的列或者表达式是不是空值。如果第一项不是空值，那么返回第二个参数，否则返回第三个参数。注意，NVL函数不是这样，因为如果原始项不是空值，它就返回原始项。
NVL2函数有三个强制参数。其语法是NVL2(original, ifnotnull, ifnull)，其中original表示被测试的项。如果original不是空值，就返回ifnotnull；如果original是空值，就返回ifnull。ifnotnull和ifnull参数的数据类型必须一致，它们不能是LONG数据类型。它们可以是相同的类型，或者可以将ifnull转换为ifnotnull参数的类型。NVL2函数返回的数据类型与ifnotnull参数的数据类型相同。考察下面三个查询：
查询1: select nvl2(1234, 1, 'a string') from dual;
查询2: select nvl2(null, 1234, 5678) from dual;
查询3: select nvl2(substr('abc', 2), 'Not bc', 'No substring') from dual;
查询1中的ifnotnull项是一个数字，而ifnull参数是一个字符串。因为它们之间的数据类型不兼容，因此会返回错误“ORA-01722: invalid number”。查询2返回ifnull参数，即5678。查询3使用SUBSTR函数提取字符“bc”，并计算NVL2('bc', 'Not bc', 'No Substring')函数，返回ifnotnull参数，即字符串’Not bc'。
3．NULLIF函数
NULLIF函数测试两项的相等性。如果它们相等，函数就返回空值，否则返回这两个测试项的第一项。
NULLIF函数有两个可以是任何数据类型的强制参数。其语法是NULLIF(ifunequal, comparison_ term)，其中比较参数ifunequal和comparison_term。如果它们相同，返回NULL。如果它们不同，就返回ifunequal参数。考察下面的查询：
查询1: select nullif(1234, 1234) from dual;
查询2: select nullif('24-JUL-2009', '24-JUL-09') from dual;
查询1返回空值，因为这两个参数相同。查询2中的字符字面值没有隐式转换为DATE项，NULLIF函数将它们作为两个字符串进行比较。由于字符串的长度不同，因此返回ifunequal参数，即24-JUL-2009。
图8-4说明了如何将NULLIF嵌套为NVL2函数的参数。NULLIF函数本身将嵌套在表达式里的SUBSTR和UPPER字符函数作为它的ifunequal参数。为名字为4个字符长的员工比较EMAIL列和一个表达式，该表达式的形式是将FIRST_NAME的第一个字符连接到LAST_NAME列的大写字母形式。如果这两项相等，NULLIF返回空值，否则返回计算后的ifunequal参数。这个返回值用作NVL2的参数。NVL2函数提供描述性文本，该文本依据是否匹配模式对行进行分类。
图8-4 NVL2和NULLIF函数
4．COALESCE函数
COALESCE函数从参数列表中返回第一个非空值。如果所有参数为空，那么返回空值。
COALESCE函数有两个强制参数和任何数量的可选参数。其语法是COALESCE(expr1, expr2, …, exprn)，如果expr1不是空值，就返回它，否则，如果expr2不是空值，就返回它，以此类推。COALESCE是NVL函数的一般形式，如下面两个等式所示：
COALESCE(expr1, expr2) = NVL(expr1, expr2)
COALESCE(expr1, expr2, expr3) = NVL(expr1, NVL(expr2, expr3))
如果找到非空值，COALESCE返回的数据类型与第一个非空参数的数据类型相同。为了避免出现“ORA-00932: inconsistent data types”错误，所有非空参数的数据类型必须与第一个非空参数的数据类型一致。考察下面三个查询：
查询1: select coalesce(null, null, null, 'a string') from dual;
查询2: select coalesce(null, null, null) from dual;
查询3: select coalesce(substr('abc', 4), 'Not bc', 'No substring') from dual
查询1返回第四个参数：一个字符串，因为这是出现的第一个非空参数。查询2返回空值，因为所有参数都是空值。查询3计算它的第一个参数，即嵌套的SUBSTR函数，发现它是空的。第二个参数为非空，因此返回字符串’Not bc'。
考点：
如果已经熟悉NVL，通用函数NVL2的参数可能引起混淆。如果它是非空的， NVL(original, ifnull)返回 original，否则返回 ifnull。如果 original 非空， NVL2(original, ifnotnull, ifnull)函数返回ifnotnull，否则返回ifnull。可能会出现上述混淆，因为NVL函数的第二个参数是 ifnull，而NVL2函数的第二个参数是ifnotnull。请记住函数中参数位置的含义。
5．DECODE函数
DECODE函数通过测试前两项的相等性来实现if-then-else条件逻辑，如果它们相等，则返回第三个参数；如果它们不相等，则可能返回另一项。
DECODE函数至少使用三个强制参数，但可以使用更多参数。该函数的语法是DECODE (expr1, comp1, iftrue1, [comp2, iftrue2...[ compN, iftrueN]], [iffalse])。这些参数的计算如下面的伪代码示例所示：
If expr1 = comp1 then return iftrue1
else if expr1 = comp2 then return iftrue2
...
...
else if expr1 = compN then return iftrueN
else return null | iffalse;
在代码中expr1与comp1比较。如果它们相等，那么返回iftrue1。如果expr1不等于comp1，那么接下来会出现什么情况取决于可选参数comp2和iftrue2是否存在。如果它们存在，就比较expr1和comp2。如果这两者相等，那么返回iftrue2。如果不相等，则接下来发生的情况取决于compN、iftrueN对是否存在，继续这个循环，直到没有比较项存在为止。如果没有匹配项，并且如果定义了iffalse参数，那么返回iffalse。如果iffalse参数不存在，并且没有匹配，那么返回空值。
DECODE函数的所有参数都可以是表达式。返回的数据类型与第一个匹配比较选项的数据类型相同。表达式expr1被隐式转换为第一个比较参数comp1的数据类型。计算其他比较参数comp2…compn时，也会将它们隐式转换为与comp1相同的数据类型。DECODE认为两个空值相等，因此如果expr1是空值，并且comp3是出现的第一个空值比较参数，那么就会返回对应的结果参数iftrue3。考察下面三个查询：
查询1: select decode(1234, 123, '123 is a match') from dual;
查询2: select decode(1234, 123, '123 is a match', 'No match') from dual;
查询3: select decode('search', 'comp1', 'true1', 'comp2', 'true2',
'search', 'true3', substr('2search', 2, 6), 'true4', 'false') from dual;
查询1比较数字1234与第一个比较项123。因为它们不相等，因此无法返回第一个结果项。而且，因为没有定义默认的iffalse参数，所以返回空值。查询2与查询1相同，除了定义iffalse参数之外。因为1234不等于123，所以返回字符串’No match'。查询3搜索比较参数查找匹配项。字符项’comp1’和’comp2’不匹配 search，因此不会返回结果’true1’和’true2'。在第三个比较项’comp3' (参数6)中找到匹配项，它包含字符串search。因此，返回包含字符串'true3’的第三个结果项iftrue3 (参数7)。注意，因为发现了匹配，所以不需要再进行搜索。因此，虽然第四个比较项(参数8)也匹配expr1，但不会计算这个表达式，因为在前面的比较项中已经找到了匹配项。
6．CASE表达式
其实所有第三和第四代编程语言都可实现case语句。和DECODE函数一样，CASE表达式使用if-then-else条件逻辑。CASE表达式有两个变体。简单的CASE表达式列出条件搜索项一次，由每个比较表达式来测试与搜索项的相等性。搜索的CASE表达式列出每个比较表达式的单独条件。
CASE表达式至少使用三个强制参数，但可以使用更多参数。其语法取决于是使用简单CASE表达式还是使用搜索的CASE表达式。简单CASE表达式的语法如下所示：
CASE search_expr
WHEN comparison_expr1 THEN iftrue1
[WHEN comparison_expr2 THEN iftrue2
...
WHEN comparison_exprN THEN iftrueN
ELSE iffalse]
END
简单CASE表达式包含在CASE…END代码块内，由至少一个WHEN…THEN语句组成。在最简单的情况下——只有一个WHEN…THEN语句，search_expr与comparison_expr1进行比较。如果它们相等，那么返回结果iftrue1。如果不相等，那么返回一个空值，除非定义了ELSE组件，这种情况下，返回默认的iffalse值。当CASE表达式中有多个WHEN…THEN语句时，就会不断搜索匹配的比较表达式，直到找到匹配项为止。
搜索、比较和结果参数可以是列值、表达式或者字面值，但必须都是相同的数据类型。考察下面的查询：
select
case substr(1234, 1, 3)
when '134' then '1234 is a match'
when '1235' then '1235 is a match'
when concat('1', '23') then concat('1', '23')||' is a match'
else 'no match'
end
from dual;
派生自SUBSTR(1234,1,3)的搜索表达式是字符串123。第一个WHEN...THEN语句比较字符串134和123。因为它们不相等，所以没有计算结果表达式。第二个WHEN…THEN语句比较字符串1235和123，它们也不相等。第三个WHEN…THEN语句将来自CONCAT('1', '23')表达式的结果(即123)与搜索表达式进行比较。因为它们相同，因此返回第三个结果表达式'123 is a match'。
检索DEPARTMENT_ID值为100的员工的LAST_NAME和HIRE_DATE列，以及两个数字表达式和一个CASE表达式，如图8-5所示。
图8-5 简单的CASE表达式
假设SYSDATE是27-JAN-2015。别名为YEARS的数字表达式返回用工作的月数除以12之后的截取值。依据工作的年数定义5类忠诚等级，这形成了CASE语句中的搜索表达式。数据集中没有行匹配第一个WHEN…THEN语句中的比较表达式，如图8-5所示，有4行匹配剩下的WHEN…THEN语句，且ELSE语句也捕获了两行。
搜索的CASE表达式的语法如下所示：
CASE
WHEN condition1 THEN iftrue1
[WHEN condition2 THEN iftrue2
...
WHEN conditionN THEN iftrueN
ELSE iffalse]
END
搜索的CASE表达式包含在CASE…END代码块内，由至少一个WHEN…THEN语句组成。在最简单的情况下——只有一个WHEN…THEN语句，计算condition1；如果它为true，那么返回结果iftrue1。如果不是，那么就返回一个空值，除非定义了ELSE组件，这种情况下，返回默认的iffalse值。当CASE表达式中有多个WHEN…THEN语句时，就会不断搜索匹配的比较表达式，直到找到匹配项为止。下面的查询使用搜索的CASE表达式，检索到的结果集与在图8-5中获得的结果集相同：
select last_name, hire_date, sysdate,
trunc(months_between(sysdate, hire_date)) months,
trunc(months_between(sysdate, hire_date)/12) years,
case
when trunc(months_between(sysdate, hire_date)/12) < 7 then 'Intern'
when trunc(months_between(sysdate, hire_date)/12) < 8 then 'Junior'
when trunc(months_between(sysdate, hire_date)/12) < 9 then 'Intermediate'
when trunc(months_between(sysdate, hire_date)/12) < 10 then 'Senior'
else 'Furniture'
end Loyalty
from employees
where department_id = 100
order by years;
练习8-3 使用DECODE函数
要求查询HR.LOCATIONS表中的一些行，这些行的COUNTRY_ID列为值US。需要别名为LOCATION_INFO的表达式计算STATE_PROVINCE列值，返回不同的信息，如表8-9所示。依据LOCATION_INFO表达式排序输出。
表8-9 不同STATE_PROVINCE的返回值
(1) 启动SQL Developer或SQL*Plus，连接到HR模式。
(2) 有几种方法计算LOCATION_ID表达式，可以使用CASE表达式或者DECODE函数。下面的解决方案使用CASE表达式。
select case state_province
when 'Washington' then 'Headquarters'
when'Texas' then 'Oil Wells'
when 'California' then city
when 'New Jersey' then street_address
end location_info, state_province, city, street_address, country_id
from locations where country_id='US' order by location_info;
8.5 本章知识点回顾
描述SQL中可用的各种类型的转换函数
● 当调用像TO_CHAR这样的函数改变值的数据类型时，就会发生显式转换。TO_CHAR函数实现日期到字符和数字到字符数据类型的转换。
● 使用TO_DATE转换函数可以将字符项显式转换为日期值。
● 使用TO_NUMBER转换函数可以将字符项转换为数值。
使用TO_CHAR、TO_NUMBER和TO_DATE转换函数
● TO_CHAR函数返回VARCHAR2类型的项。
● 格式模型或者格式掩码描述一些模式，字符串必须匹配这些模式以便于准确和一致地转换为数字或者日期项。
● 使用TO_CHAR函数从日期中提取的字符项(如月和日名称)会自动添加空格，在格式掩码前面添加fm修饰符可以删除这些空格。
● TO_DATE函数有一个 fx 修饰符，它指定要转换的字符串和日期格式掩码必须准确匹配。
在SELECT语句中应用条件表达式
● 嵌套函数使用一个函数的输出作为另一个函数的输入。
● NVL函数原封不动地返回首项，或者如果首项为空值，则返回备选项。
● 如果首项是空值，NVL2函数会返回新的if-null项；如果首项不是空值，则返回可代替的if-not-null项。
● NULLIF函数测试两项的相等性。如果它们相等，则函数返回空值，否则返回被测试的两项的第一项。
● COALESCE函数从参数列表中返回第一个非空值。如果所有参数都为空值，那么返回空值。
● DECODE函数以及简单的CASE表达式和搜索的CASE表达式用于实现if-then-else条件逻辑。