1）NVL(expr1, expr2)

expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致

SCOTT@ORA11GR2>select nvl(null,0),nvl(2,0) from dual;

NVL(NULL,0) NVL(2,0)

----------- ----------

0 2

SCOTT@ORA11GR2>

2）NVL2(expr1, expr2, expr3)

expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型

SCOTT@ORA11GR2>select nvl2(null,1,2),nvl2(3,1,2) from dual;

NVL2(NULL,1,2) NVL2(3,1,2)

-------------- -----------

2 1

SCOTT@ORA11GR2>

SCOTT@ORA11GR2>select nvl(null,0),nvl(2,0) from dual;

NVL(NULL,0) NVL(2,0)

----------- ----------

0 2

SCOTT@ORA11GR2>select nvl2(null,1,2),nvl2(3,1,2) from dual;

NVL2(NULL,1,2) NVL2(3,1,2)

-------------- -----------

2 1

SCOTT@ORA11GR2>select nvl2(null,1,2),nvl2(3,1,'A') from dual;

select nvl2(null,1,2),nvl2(3,1,'A') from dual

*

ERROR at line 1:

ORA-01722: invalid number

之所以报错，是因为字符串'A'做了一个隐式转换，转换为与1相同的数值型，'A'转换为数值型，肯定是转换失败的，所以报此错误，下面的两条语句则执行正常，因为它们均隐式转换成功。

SCOTT@ORA11GR2>select nvl2(null,1,2),nvl2(3,1,'5') from dual;

NVL2(NULL,1,2) NVL2(3,1,'5')

-------------- -------------

2 1

SCOTT@ORA11GR2>select nvl2(null,1,2),nvl2(3,'b',1) from dual;

NVL2(NULL,1,2) N

-------------- -

2 b

SCOTT@ORA11GR2>

3）NULLIF(expr1, expr2)

expr1, expr2相等则返回NULL，不等则返回expr1

SCOTT@ORA11GR2>select nullif(1,1),nullif(2,1) from dual;

NULLIF(1,1) NULLIF(2,1)

----------- -----------

2

SCOTT@ORA11GR2>

4）LNNVL(condition)

LNNVL 等价于 IS NULL OR condition IS NOT TRUE

create table t_null(id number(5) primary key,name varchar2(10),age number(3));

insert into t_null values(1,'zhangsan',20);

insert into t_null values(2,'lisi',21);

insert into t_null values(3,'wangwu',null);

insert into t_null values(4,'zhaoliu',19);

commit;

SCOTT@ORA11GR2>select * from t_null;

ID NAME AGE

---------- ---------- ----------

1 zhangsan 20

2 lisi 21

3 wangwu

4 zhaoliu 19

SCOTT@ORA11GR2>

要求：查询age小于等于20或者age为null的所有记录

最容易想到的办法完成

SCOTT@ORA11GR2>SELECT * FROM t_null WHERE age <= 20 or age is null;

ID NAME AGE

---------- ---------- ----------

1 zhangsan 20

3 wangwu

4 zhaoliu 19

SCOTT@ORA11GR2>

用lnnvl函数完成，lnnvl返回age为null的所有值，age>20 not true,那么即为age<=20

SCOTT@ORA11GR2>SELECT * FROM t_null WHERE lnnvl(age > 20);

ID NAME AGE

---------- ---------- ----------

1 zhangsan 20

3 wangwu

4 zhaoliu 19

SCOTT@ORA11GR2>

5) COALESCE(expr1 , expr2 ... exprN)

返回expr1,expr2….,exprN中,第一个非null值

SCOTT@ORA11GR2>select COALESCE(null,1,null,2) from dual;

COALESCE(NULL,1,NULL,2)

-----------------------

1

SCOTT@ORA11GR2>

6) CASE

实现IF THEN THEN逻辑，可以做比较的判断

语法：

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

SCOTT@ORA11GR2>with t as (

select 1 as sno,60 as score from dual union all

select 2,70 from dual union all

select 3,59 from dual)

select sno,score,case when score>=60 then 'Congratulations' else 'Thank you' end as ocm from t;

SNO SCORE OCM

---------- ---------- ---------------

1 60 Congratulations

2 70 Congratulations

3 59 Thank you

SCOTT@ORA11GR2>

7) DECODE

实现IF THEN THEN逻辑，只可以做等值判断

语法：

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])

SCOTT@ORA11GR2>with t as(

select decode(rownum,1,2,mod(rownum,2)) as gender from dual connect by rownum<10)

select gender as sex,decode(gender,0,'M',1,'F','U') as gender from t;

SEX G

---------- -

2 U

0 M

1 F

0 M

1 F

0 M

1 F

0 M

1 F

9 rows selected.

SCOTT@ORA11GR2>

当然，decode也可以想办法实现上面case的例子，如下：

SCOTT@ORA11GR2>with t as (

select 1 as sno,60 as score from dual union all

select 2,70 from dual union all

select 3,59 from dual)

select sno,score,decode(sign(score-60),-1,'Thank you','Congratulations') as ocm from t;

SNO SCORE OCM

---------- ---------- ---------------

1 60 Congratulations

2 70 Congratulations

3 59 Thank you

SCOTT@ORA11GR2>

ITPub博客

【函数】通用单行函数

最新文章