Oracle 常用函数 详解(8)

NVL2函数有三个强制参数。语法:NVL2(original,ifnotnull,ifnull),其中original表示被测试的项。如果original不是空值，就返回ifnotnull；如果original是空值，就返回ifnull。ifnotnull和ifnull参数的数据类型必须一致或者ifnull参数可以转换为ifnotnull参数的数据类型，它们不能是LONG数据类型。它们可以是相同的类型，或者可以将ifnull转换为ifnotnull参数的类型。NVL2函数返回的数据类型与ifnotnull参数的数据类型相同。

select nvl2(1234,1,'a string') from dual; --错误

select nvl2(null,1234,5678) from dual;  --5678

select nvl2(substr('abc',2),'Not bc','No substring') from dual;

      NULLIF

NULLIF函数测试两项的相等性。如果它们相等，函数就返回空值，否则返回这两个测试项的第一项。

NULLIF函数有两个可以是任何数据类型的强制参数，两个参数类型必须一致，第一个参数不可以为空。语法:NULLIF（ifunequal,comparison_term)，其中比较参数ifunequal和comparison_term。如果它们相同，返回NULL。如果它们不同，返回ifunequal参数。

select nullif(1234,1234) from dual;

select nullif('24-JUL-2009','24-JUL-09') from dual;

                   select nullif(1,null) from dual;  --1

                   select nullif(null,null) from dual;----返回ORA-00932: inconsistent datatypes: expected - got CHAR

      COALESCE

COALESCE函数从参数列表中返回第一个非空值。如果所有参数为空，那么返回空值。

COALESCE函数有两个强制参数和任何数量的可选参数。语法:COALESCE(expr1,expr2,...,exprn)，如果expr1不是空值，就返回它，否则，如果expr2不是空值，就返回它，以此类推。COALESCE函数是NVL函数的一般形式：

COALESCE(expr1,expr2)=NVL(expr1,expr2)

COALESCE(expr1,expr2)=NVL(expr1,NVL(expr2,expr3))

如果找到非空值，COALESCE返回的数据类型与第一个非空参数的数据类型相同。为了避免出现“ORA-00931:inconsistent date types”错误，所有非空参数的数据类型必须与第一个非空参数的数据类型一致。

select coalesce(null,null,null,'a string') from dual;

select coalesce(null,null,null) from dual;

select coalesce(substr('abc',4),'not bc','no substring') from dual;

select coalesce(substr('abc',4),'not bc',123) from dual;--错误

      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 iftrue 2

...

...

else if expr1 = compN then return iftrueN

else return null | iffalse;

DECODE 函数的所有参数都可以是表达式。返回的数据类型与第一个匹配比较选项的数据类型相同的。表达式expr1被隐式转换为第一个比较参数comp1的数据类型。计算其他比较参数comp2...compn时，也会将它们隐式转换为comp1相同的数据类型。DECODE 认为两个空值相等，因此如果expr1是空值，并且comp3是出现的第一个空值比较参数，那么就会返回对应的结果参数iftrue3。

select decode(1234,123,'123 is a match') from dual;

select decode(1234,123,'123 is a match','No match') from dual;

select decode('search','comp1','true1','comp2','true2','search','true3',substr('2search',2,6),'true4','false') from dual;

select decode(null,'comp1','true1','comp2','true2',null,'true3','false') from dual;