oracle辅助函数

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;

转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。

 

 

　　1、BIN_TO_NUM(n1,n2...n) 将一组位向量转换为等价的十进制形式。

　　例如：SELECT BIN_TO_NUM(1,1,0) FROM DUAL;

　　2、CAST(c as newtype) 将指定字串转换为指定类型，基本只对字符类型有效，比如char,number,date,rowid等。此类转换有一个专门的表列明了哪种类型可以转换为哪种类型，此处就不作酹述。

　　例如：SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;

　　3、CHARTOROWID(c) 将字符串转换为rowid类型

　　例如：SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;

　　4、ROWIDTOCHAR(rowid) 转换rowid值为varchar2类型。返回串长度为18个字节。

　　例如：SELECT ROWIDTOCHAR(rowid) FROM DUAL;

　　5、TO_MULTI_BYTE(c) 将指定字符转换为全角并返回char类型字串

　　例如：SELECT TO_MULTI_BYTE('ABC abc　中华') FROM DUAL;

　　6、TO_SINGLE_BYTE(c) 将指定字符转换为半角并返回char类型字串

　　例如：SELECT TO_SINGLE_BYTE('ABC　abc中华') FROM DUAL;

 

 

1、COALESCE(n1,n2,....n) 返回序列中的第一个非空值

　　例如：SELECT COALESCE(null,5,6,null,9) FROM DUAL;

　　2、DUMP(exp[,fmt[,start[,length]]])

　　dump是个功能非常强悍的函数，对于深入了解oracle存储的人而言相当有用。所以对于我们这些仅仅只是应用的人而言就不知道能将其应用于何处了。此处仅介绍用法，不对其功能做深入分析。

　　如上所示，dump拥有不少参数。其本质是以指定格式，返回指定长度的exp的内部表示形式的varchar2值。fmt含4种格式：8||10||16||17，分别表示8进制，10进制，16进制和单字符，默认为10进制。start参数表示开始位置，length表示以,分隔的字串数。

　　例如：SELECT DUMP('abcdefg',17,2,4) FROM DUAL;

　　3、EMPTY_BLOB,EMPTY_CLOB 这两个函数都是返回空lob类型，通常被用于insert和update等语句以初始化lob列，或者将其置为空。EMPTY表示LOB已经被初始化，只不过还没有用来存储数据。

　　4、NLS_CHARSET_NAME(n) 返回指定数值对应的字符集名称。

　　例如：SELECT NLS_CHARSET_NAME(1) FROM DUAL;

　　5、NLS_CHARSET_ID(c) 返回指定字符对应的字符集id。

　　例如：SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;

　　6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一个NCHAR值的声明宽度(以字符为单位).n1是该值以字节为单位的长度，n2是该值的字符集ID

　　例如：SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;

 

 

4、BITAND(n1,n2) 位与运算，这个太有意思了，虽然没想到可能用到哪里，详细说明一下：

　　假设3,9做位与运算，3的二进制形式为：0011，9的二进制形式为：1001，则结果是0001，转换成10进制数为1。

　　例如：SELECT BITAND(3,9) FROM DUAL;

　　5、CEIL(n) 返回大于或等于n的最小的整数值

　　例如：SELECT ceil(18.2) FROM DUAL;

　　考你一下，猜猜ceil(-18.2)的值会是什么呢

　　6、FLOOR(n) 返回小于等于n的最大整数值

　　例如：SELECT FLOOR(2.2) FROM DUAL;

　　再猜猜floor(-2.2)的值会是什么呢

　　7、BIN_TO_NUM(n1,n2,....n) 二进制转向十进制

　　例如：SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;