1、nvl(expr1,expr2) 如exp1是空，则返回exp2,否则返回expr1; 2、nvl2(expr1,expr2,expr3) 如果exp1是空，则返回expr3,否则返回expr2; 3、coalesce(expr[,expr1]...) 返回参数里面第一个非空; with test as ( select 'c11' col_1, '' col_2, 'c31' col_3 fro
1、nvl(expr1,expr2)
如exp1是空值，则返回exp2,否则返回expr1;
2、nvl2(expr1,expr2,expr3)
如果exp1是空值，则返回expr3,否则返回expr2;
3、coalesce(expr[,expr1]...)
返回参数里面第一个非空值;
with test as
( select 'c11' col_1, '' col_2, 'c31' col_3 from dual union all
  select '' col_1, 'c21' col_2, 'c32' col_3 from dual union all
  select 'c13' col_1, 'c22' col_2, '' col_3 from dual union all
  select '' col_1, 'c23' col_2, 'c33' col_3 from dual union all
  select 'c14' col_1, '' col_2, 'c34' col_3 from dual union all
  select 'c15' col_1, '' col_2, '' col_3 from dual
)
select col_1, nvl(col_1, col_1) exp_1,
       col_2, nvl2(col_2,col_2||',','is null') exp_2,
       col_3, coalesce(col_1, col_2, col_3) exp_3
from test;
col_1     exp_1 col_2   exp_2    col_3  exp_3
--------- ----- ------- -------  -----  -----
c11       c11           is null  c31    c11
                c21     c21,     c32    c21
c13       c13   c22     c22,            c13
                c23     c23,     c33    c23
c14       c14           is null  c34    c14
c15       c15           is null         c15
6 rows selected