oracle常用函数

1、wmsys.wm_concat和listagg

将多行数据整合到一行，用逗号分隔。

在oracle 12c数据库中，是listagg

2、dbms_random.random和dbms_random.value(0,100)

--随机排列
select * from t_znf order by dbms_random.random;
--随机整数
select trunc(dbms_random.value(1,100)) from dual; 

3、decode

decode(字段，result1，value1，result2,value2,...,default)

相当于if..else if ..else .

select a.*,decode(id,1,'ID=1',2,'ID=2',3,'ID=3','ID默认值') from t_znf a ;

当t_znf.id = 1,返回'ID=1'，为2时，返回'ID=2'，为3时，返回'ID=3'，当ID值为其他时，返回默认值：'ID默认值'。

4、NULLIF

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

select a.*,nullif(id,3) from t_znf a;

5、NVL2

NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
含义是：如果该函数的第一个参数不为空，返回第二个参数的值，如果第一个参数的值为空，则返回第三个参数值。个人感觉有点像java的三目预算   expr1?expr1:expr3

select a.*,nvl2(a.state,a.state,a.t_desc) from t_znf a ;

当t_znf.state 不为空，返回 state，为空时，返回 a.t_desc；

 

6、substr

substr(字段,开始位置,截取长度)，注意字符串的开始字段是1。

select a.*, substr(a.t_desc,1,5),a.rowid from t_znf a;

 7、length

计算字段长度

select A.*,LENGTH(A.T_DESC) from T_ZNF A;

8、replace

replace(exp，old_str,new_str)：用new_str 替换掉 exp 中所有的 old_str。

select a.*,replace(a.t_desc,'A','BBB') from t_znf a;

用BBB代替t_znf.t_desc中的A

可以用replace 去掉字符串中所有的空格

select replace('  hello world  ',' ','') from dual;

9、TRIM、RTRIM、LTRIM

trim(exp)：去除 exp字符串前后的空格

rtrim(exp):去除 exp字符串右边(right)的空格，即去除字符串尾部空格

ltrim(exp):去除 exp字符串左边(left)的空格，即去除字符串开头空格

如果需要去除所有空格，用replace

select trim('  hello world  ') from dual;

去除尾部空格

去除开头的空格

10、round

按精度四舍五入

11、mod

取余函数，该函数可以用于并列处理 数据的更新。比如：存过更新一个超大表，可以对ID进行取余，进而并发执行存过。

CREATE OR REPLACE PROCEDURE P_update_T_ZNF(seq in number) 
  CURSOR C1 IS
    select t.ID
      from T_ZNF t
     where t.flag = 'N' --是否已经处理的标识
       and mod(t.id, 10) = seq
       and rownum < 160001;

BEGIN
--数据处理
END;

可以打开10个窗口并列执行 call P_update_T_ZNF（seq）,seq的值为0-9，这样大大加快的数据的处理速度。
曾经有个数据的更新，执行了一个晚上也没有执行完，后面用取余函数，并发执行，两个小时执行完了。

12、sign符号函数

SIGN(n)函数是一个符号函数，他的功能是返回（1或0或-1）

当 n < 0 返回 -1
   n = 0 返回  0
   n > 0 返回  1

DECODE(VALUE, if1,then1, if2,then2, if3,then3, DEFAULT)

3、SIGN()与DECODE()结合使用示例:
当LEVEL<5输出low
当LEVEL<8输出mid
  ELSE输出high

SELECT LEVEL, DECODE(-1, SIGN(LEVEL-5),'low',   
                SIGN(LEVEL-8),'mid',   
                'high')   
FROM dual CONNECT BY LEVEL < 10;