oracle部分函数使用汇总 NULLIF函数 decode ，COALESCE，concat ，nvl,nvl2

1. NULLIF函数

   NULLIF函数语法为NULLIF（表达式1，表达式2），如果表达式1和表达式2相等则返回空值，如果表达式1和表达式2不相等则返回表达式1的结果。

   注意：表达式1和表达式2应该是相同数据类型，表达式1不能用字符null。 select nullif(1,1) from dual;
       select nullif(2,1) from dual;
       select nullif('2','1') from dual;
       select nullif('2',1) from dual;---ora-00932:数据类型不一致，应为char，但却获得number
       select nullif(2,'1') from dual;---ora-00932:数据类型不一致，应为 number ，但却获得char
       select nullif(sysdate, sysdate+1) from dual;
       select nullif('20140605',sysdate) from dual;---ora-00932:数据类型不一致，应为char，但却获得date
       select nullif(sysdate,'20140605') from dual;---ora-00932:数据类型不一致，应为date ，但却获得charchar
       select nullif(null,1) from dual;--ora-00932
       select nullif('','1') from dual;

2. decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

drop table tmp_2;
create table tmp_2 (aa varchar2(10),bb integer,cc varchar2(10));
insert into tmp_2(aa) values('a');
insert into tmp_2(aa) values('b');
insert into tmp_2(aa) values('c');
insert into tmp_2(bb) values('1');
insert into tmp_2(aa) values('1');
insert into tmp_2(cc) values('1');
insert into tmp_2(aa,cc) values('aa','aa');

commit;

select decode(aa,'a','1') ,a.* from tmp_2 a;
select decode(aa,'','1') ,a.* from tmp_2 a;
select decode(aa,null,'1') ,a.* from tmp_2 a;
select decode(bb,'>100','b') ,a.* from tmp_2 a;
select decode(bb,>100,'b') ,a.* from tmp_2 a;--ora-00936 缺失表达式
select decode(aa,aa,'呵呵') ,a.* from tmp_2 a;
select decode(aa,bb,'呵呵') ,a.* from tmp_2 a;--ora-01722无效数字
select decode(aa,cc,'呵呵') ,a.* from tmp_2 a;

select decode(bb,1,2) ,a.* from tmp_2 a;
select decode(bb,1,'2') ,a.* from tmp_2 a;
select decode(bb,1,'2','hh') ,a.* from tmp_2 a;
select decode(bb,1,2,'hh') ,a.* from tmp_2 a;--ora-01722

select decode(bb,1,2,'1') ,a.* from tmp_2 a;

select decode(bb,1,2,'2',4) ,a.* from tmp_2 a;
select decode(bb,1,2,'a',4) ,a.* from tmp_2 a;--ora-01722

select decode(bb,'ss',2,1,4) ,a.* from tmp_2 a;
select decode(bb,1,4,'ss',2) ,a.* from tmp_2 a;--ora-01722

1、值1……值n 必须是同一类型
2、翻译值1……翻译值n 必须是同一类型
3、Oralce会自动将翻译值2……翻译值n和缺省值的类型转换成与翻译值1相同的类型（值1……值n 同理）
4、如果翻译值2……翻译值n和缺省值中有不能转换成与翻译值1相同的类型，则报错：ORA-01722（值1……值n 同理）

3.COALESCE

(表达式1,表达式2,...,表达式n)，n>=2,此表达式的功能为返回第一个不为空的表达式，如果都为空则返回空值。
注意：所有表达式必须为同一类型

select coalesce(1,1,2,'hh') from dual;--ora-00932

select coalesce(1,1,2,'1') from dual;--ora-00932

select coalesce('1',1,2) from dual;--ora-00932

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

4.concat 只能连接两个字符串，concat（字符串1，字符串2），若参数非字符串，则可隐式转换

  || ：可实现多个字段串的连接，若参数非字符串，则可隐式转换

select concat('1','a') from dual;
select concat(1,'a') from dual;
select concat(sysdate,'a') from dual;
select concat(1,sysdate) from dual;
select concat(1,2,3) from dual;--ora--00903参数个数无效

5.nvl

nul函数将一个null值转换为一个实际的值。
数据类型可以是日期，数字，字符。
数据类型必须匹配：
   nvl(num1,num2)
   nvl(date1,date2)
   nvl(char1, char2)

nvl能够转换任何数据类型，但是转换的数据类型返回值必须是nvl(expr1,expr2)第一个参数expr的类型。如：date,number, varchar2 or char ,或者expr2可隐式转换成expr1类型

6.nvl2

nvl2(expre1,expre2,expre3)函数
如果第一个参数不为空，那么返回第二个参数，如果为空，返回第三个参数。
参数1可以是任何数据类型。参数2和参数3也可以是任何数据类型，除了long类型之外。
如果后两个参数数据类型不一样，数据库将会把第三个参数的数据类型转换为第二个参数的数据类型。