Oracle中nvl()和nvl2()函數範例詳解

2022-05-18 13:03:39

1.nvl：根據引數1是否為null返回引數1或引數2的值

【函數格式】：nvl(expression1,expression2)

【函數說明】：

若expression1值為null，則該函數返回expression2；
若expression1值不為null，則該函數返回expression1；
若expression1、expression2的值均為null，則該函數返回null。

【特別說明】：引數expression1和expression2可以是字元型、數位型或日期型，但引數expression1與expression2的型別必須一致。

【樣例展示一】：expression1值為null：

expression2為字元型：

select nvl(null,'ABCD') from dual;        --返回：ABCD
select nvl(null,'abcd') from dual;        --返回：abcd
select nvl(null,'12345') from dual;        --返回：12345

expression2為數值型：

select nvl(null,12345) from dual;        --返回：12345
select nvl(null,00000) from dual;        --返回：0
select nvl(null,-12345) from dual;        --返回：-12345
select nvl(null,123.45) from dual;        --返回：123.45
select nvl(null,-123.45) from dual;        --返回：-123.45

expression2為日期時間型：

select nvl(null,sysdate) from dual;        --返回：2022/2/25 11:54:18
select nvl(null,to_date('2022/2/25 11:54:18','yyyy-mm-dd hh24:mi:ss')) from dual;                        --返回：2022/2/25 11:54:18
select nvl(null,to_date('2022/2/25','yyyy-mm-dd')) from dual;        --返回：2022/2/25
select nvl(null,to_date('2022','yyyy')) from dual;        --返回：2022/2/1

【樣例展示二】：expression1值不為null：

expression1為字元型：

select nvl('ABCD','abcd') from dual;        --返回：ABCD
select nvl('abcd','ABCD') from dual;           --返回：abcd
select nvl('12345','54321') from dual;        --返回：12345

expression1為數值型：

select nvl(12345,null) from dual;        --返回：12345
select nvl(00000,11111) from dual;        --返回：0
select nvl(-12345,null) from dual;        --返回：-12345
select nvl(123.45,-123.45) from dual;        --返回：123.45
select nvl(-123.45,123.45) from dual;        --返回：-123.45

expression1為日期時間型：

select nvl(sysdate,null) from dual;        --返回：2022/2/25 12:18:23
select nvl(to_date('2022/2/25 12:18:23','yyyy-mm-dd hh24:mi:ss'),null) from dual;                        --返回：2022/2/25 12:18:23
select nvl(to_date('2022/2/25','yyyy-mm-dd'),null) from dual;        --返回：2022/2/25
select nvl(to_date('2022','yyyy'),null) from dual;        --返回：2022/2/1

【樣例展示三】：expression1、expression2值均為null：

select nvl(null,null) from dual;        --返回空值
select nvl('',null) from dual;        --返回空值
select nvl(null,'') from dual;        --返回空值
select nvl('','') from dual;        --返回空值

2.nvl2：根據引數1是否為null返回引數2或引數3的值

【函數格式】：nvl2(expression1,expression2,expression3)

【函數說明】：

若expression1值不為null，則該函數返回expression2值；
若expression1值為null，則該函數返回expression3值；
若expression1、expression2、expression3值均為null，則該函數返回null。

【特別說明】：

expression1的型別不需要與expression2和expression3的型別保持一致；
expression2與expression3的型別儘量保持一致，若不一致，當expression1為null時，則expression3會自動轉換為expression2的型別，若兩個資料型別之間無法轉換，則會報錯。

【樣例展示一】：expression1值不為null：

expression2和expression3均為字元型：

select nvl2(0,'ABCD','abcd') from dual;        --返回：ABCD
select nvl2('a','ABCD','1234') from dual;        --返回：ABCD
select nvl2(sysdate,'1234','abcd') from dual;        --返回：1234

expression2和expression3均為數值型：

select nvl2(0,12345,54321) from dual;        --返回：12345
select nvl2('a',123.45,543.21) from dual;        --返回：123.45
select nvl2(sysdate,-12345,-54321) from dual;        --返回：-12345

expression2和expression3資料型別不一致：

注意：此時expression1有值，因此該函數直接返回expression2的值，即使expression3的型別無法轉換為expression2的型別也不會報錯。

select nvl2(0,'ABCD',54321) from dual;        --返回：ABCD
select nvl2('a','abcd',543.21) from dual;        --返回：abcd
select nvl2(sysdate,'12345',-54321) from dual;        --返回：12345

【樣例展示二】：expression1值為null：