oracle 辅助函数

辅助函数(Miscellaneous Single-Row Functions)

1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数内实现if else的功能。
exp做为初始参数。s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前面的对比也都没有为真，则返回空。
毫无疑问，decode是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。

例如：

select decode('a2','a1','true1','a2','true2','default') from dual; 

2、GREATEST(n1,n2,...n) 返回序列中的最大值
例如：

SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL; 

3、LEAST(n1,n2....n) 返回序列中的最小值
例如：

SELECT LEAST(15,5,75,8) LEAST FROM DUAL; 

4、NULLIF(c1,c2)
Nullif也是个很有意思的函数。逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END
例如：

SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL; 

5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。
例如：

SELECT NVL(null, '12') FROM DUAL; 

6、NVL2(c1,c2,c3) 大家可能都用到nvl，但你用过nvl2吗？如果c1非空则返回c2,如果c1为空则返回c3
例如：

select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual; 

7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。
例如：

create table tmp3( 
rootcol varchar2(10), 
nodecol varchar2(10) 
); 

insert into tmp3 values ('','a001'); 
insert into tmp3 values ('','b001'); 
insert into tmp3 values ('a001','a002'); 
insert into tmp3 values ('a002','a004'); 
insert into tmp3 values ('a001','a003'); 
insert into tmp3 values ('a003','a005'); 
insert into tmp3 values ('a005','a008'); 
insert into tmp3 values ('b001','b003'); 
insert into tmp3 values ('b003','b005'); 

select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/') 
from tmp3 
start with rootcol = 'a001' 
connect by prior nodecol =rootcol; 

8、SYS_CONTEXT(c1,c2[,n]) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。
Oracle9i提供内置了一个命名空间USERENV，描述了当前session的各项信息，其拥有下列参数：
? CURRENT_SCHEMA:当前模式名；
? CURRENT_USER:当前用户；
? IP_ADDRESS:当前客户端IP地址；
? OS_USER:当前客户端操作系统用户；
等等数十项，更详细的参数列还请大家直接参考Oracle Online Documents