Oracle中条件函数有哪些

小编给大家分享一下Oracle中条件函数有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

成都创新互联是一家专注于网站建设、成都网站制作与策划设计,龙口网站建设哪家好?成都创新互联做网站,专注于网站建设十年,网设计领域的专业建站公司;建站业务涵盖:龙口等地区。龙口做网站价格咨询:028-86922220

条件函数

NVL

NVL函数评估任何数据类型的列或者表达式是不是空值。如果原始项是空值，返回备选的非空值；否则，返回原始项。

NVL函数有两个强制参数。语法:NVL(original,ifnull)。其中original表示要测试的项，如果original项计算为空，就返回ifnull。original和ifnull参数的数据类型必须一致。它们必须是相同的类型，或者可能将ifnull隐式转换为original参数的类型。NVL函数返回值的数据类型与original参数的数据类型相同。

select nvl(1234) from dual;--错误

select nvl(null,1234) from dual; ---1234

select nvl(substr('abc',4),'No substring exists') from dual;

NVL2

NVL2函数是对NVL函数的增强，但功能非常类似。NVL2函数评估任何数据类型的列或者表达式是不是空值。如果第一项不是空值，那么返回第二个参数，否则返回第三个参数。

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;

select decode(1234,123,123,'No match') from dual; --‘No match'ORA-01722: invalid number

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 iftureN]

[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;

搜索的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语句时，就会不断搜索匹配的比较表达式，直到找到匹配项为止。

select

case

when length(substr(1234,1,3)) = 1 then 'length of substring is 1'

when length(substr(1234,1,3)) = 2 then 'length of substring is 2'

when length(substr(1234,1,3)) = 3 then 'length of substring is 3'