PL/SQL实用函数汇总

数值函数

sign(n)函数，n为正数返回1，返回0，负数返回-1： SELECT SIGN (- 20 ), SIGN ( 0 ), SIGN ( '20' ) FROM DUAL; 结果如下：

CEIL(n)返回大于等于输入参数的最小整数。

FLOOR(n)返回小于等于输入参数的最大整数。

sqrt：开方

power(n,m)返回n的m次方。

round(num,n)返回参数四舍五入之后得到的结果，n为正整数的时候，表示四舍五入到小数点第n位；n为负数的时候，表示四舍五入到小数点左边第n位。 SELECT round ( 111.11111 , 4 ), round ( 111.11111 ,- 1 ), round ( 111.11111 , 1 ) FROM DUAL;

trunc(num,n)截取num，当n为正整数的时候，表示截取小数点后n为；n为负数的时候，表示截取小数点左边第n位。 SELECT trunc ( 111.11111 , 4 ), trunc ( 111.11111 ,- 1 ), trunc ( 111.11111 , 1 ) FROM DUAL;

---

字符串函数

length和lengthb比较： length返回字符串长度，lengthb返回字节长度。 SELECT length ( 'ab阳杨aa' ), lengthb ( 'ab阳杨aa' ) FROM DUAL;

substr(str,index1,n) 取字符串str，位置index1后面的n个字符，index1取1或0都代表第一个字符。如果index1为负数，表示从右向左算。

instr(str,sub,dire,times) 获取字符串str，的子串sub的位置。dire取1代表从左到右找，dire取-1代表从右到左找。times代表找第几个。如果times超出范围，函数返回0。

replace(str1,str2,str3)函数： 返回str1中所有str2被str3替换之后的内容。

rpad(str1,n,str2)和lpad(str1,n,str2)函数: 字符串填充函数，str1用str2填充，直到字符串的长度为n。 两者区别：rpad填充str1右边，lpad填充str1左边。 例子： SELECT rpad ( 'test' , 10 , '*' ), lpad ( 'test' , 10 , '*' ) FROM dual

trim()默认删除空格

---

日期函数

sysdate：没有参数，返回系统当前时间

systimestamp：没有参数，返回当前时间戳

add_months(date,integer)月份+integer： 例子：SELECT add_months(sysdate ,1) FROM dual

last_day(date)：返回指定月份的最后一天。

trunc(date,str)：trunc除了用来截取数字之外，还能用来截取日期，截取日期的时候，str可以是'yyyy'|'mm'|'dd'|'hh24'|'mi'|'q'('q'表示季度)，默认'dd'，并且返回的是时间不是字符串。 例如：SELECT sysdate ,trunc( sysdate,'mi' ),case when sysdate >trunc( sysdate,'mi' ) then 1 else 0 end iscompare FROM dual; 输出： 例如：SELECT sysdate,trunc (sysdate, 'yyyy'),trunc (sysdate, 'mm'),trunc (sysdate, 'dd'),trunc (sysdate, 'hh24'),trunc (sysdate, 'mi') FROM dual; 输出：

---

转换函数

bin_to_num(date...)二进制转十进制： SELECT bin_to_num( 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 ) FROM dual

to_char：数字、日期转字符串，格式化数字

to_number：字符串转数字

to_date：字符串转日期('yyyy-MM-dd HH24:mi:ss')

---

null函数

coalesce(expr)：返回第一个不为null的表达式结果。 SELECT coalesce ( null , null , 1 , null ) FROM dual;

NVL( string1, replace_with)函数: 如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值。 引申一下，此NVL的作用与SQLserver 中的 ISNULL( string1, replace_with) 一样。

---

分析函数

rollup()函数：放在group by后面，除了按分组使用集合函数外，还会按照参数顺序分别求和。 例子： select earnmonth, area, sum (personincome) from earnings group by rollup (earnmonth,area); 输出： 以上红框部分是对应求和结果，先按Area求和，再按earnmonth求和。

cube()函数：和rollup函数差不多，但是比rollup函数更全面，会根据括号中所有参数的组合数进行分组。

rollup和cube区别： 如果是ROLLUP(A, B, C)的话，GROUP BY顺序 (A、B、C) (A、B) (A) 最后对全表进行GROUP BY操作。 如果是GROUP BY CUBE(A, B, C)，GROUP BY顺序 (A、B、C) (A、B) (A、C) (A)， (B、C) (B) (C)， 最后对全表进行GROUP BY操作。

grouping函数 在以上例子中，是用rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认该记录是由哪个字段得出来的grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0 例子： select decode ( grouping (earnmonth), 1 , '所有月份' , earnmonth) 月份, decode ( grouping (area), 1 , '全部地区' , area) 地区, sum (personincome) 总金额 from earnings group by cube (earnmonth, area) order by earnmonth, area nulls last ; 输出： nulls last 是将空值放到最后。

---

其他函数

decode(语句，值，返回值1，返回值2)，如果”语句==值“返回“返回值1”否则返回“返回值2”。 1通常我们这么写: select count(*) from 表 where 性别 ＝ 男； select count(*) from 表 where 性别 ＝ 女； 要想显示到一起还要union一下，太麻烦了 用decode呢，只需要一句话 select sum(decode(性别，男，1，0）)，sum(decode(性别，女，1，0）) from 表 2 例：表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序。这时，就可以非常轻松的使用Decode完成要求了。 select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3) 3 进行数字的行列转换 如 student subject grade --------- ---------- -------- student1 语文 80 student1 数学 70 student1 英语 60 student2 语文 90 student2 数学 80 student2 英语 100 …… 转换为 语文 数学 英语 student1 80 70 60 student2 90 80 100 …… 语句如下：select student, sum(decode(subject,'语文', grade,null)) "语文", sum(decode(subject,'数学', grade,null)) "数学", sum(decode(subject,'英语', grade,null)) "英语" from table group by student;

WMSYS.WM_CONCAT() 聚集函数将多行结果用逗号隔开，以一行的形式展现出来.

nullif(ex1,ex2): 值相等返空，否则返回第一个值 例：如果工资和佣金相等，则显示空，否则显示工资 select nullif(sal,comm),sal,comm from emp;

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的). 与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码． row_number(),rank(),dense_rank()后面都是跟 over(partition by col1 order by col2)三者的区别在于： 1.row_number()得到的结果在同组内没有重复的数字； 2.rank()和dense_rank()的区别是： --rank()是跳跃排序，有两个第二名时接下来就是第四名 --dense_rank()是连续排序，有两个第二名时仍然跟着第三名

分类统计 (并显示信息) A B C -- -- ---------------------- m a 2 n a 3 m a 2 n b 2 n b 1 x b 3 x b 2 x b 4 h b 3 select a,b,c,sum(c) over(partition by a) from t2 得到结果： A B C SUM(C)OVER(PARTITION BY A) -- -- ------- ------------------------ h b 3 3 m a 2 4 m a 2 4 n a 3 6 n b 2 6 n b 1 6 x b 3 9 x b 2 9 x b 4 9 over (partition by col1) 和 group by 不同的是，over (partition by col1)不会将相同的元组合并，但是group by会。

lag( EXPRESSION ,<OFFSET>,<DEFAULT>) over(order by col)和lead( EXPRESSION ,<OFFSET>,<DEFAULT>) over(order by col)： lag取前OFFSET行的数据，lead取后OFFSET行的数据。 例子： with tmp as (( select '杨焕滨' as name , 1 mark FROM DUAL) union all ( select '杨焕滨' as name , 2 mark FROM DUAL) union all ( select '杨焕滨' as name , 3 mark FROM DUAL) union all ( select '杨焕滨' as name , 4 mark FROM DUAL) union all ( select '杨焕滨' as name , 5 mark FROM DUAL) union all ( select '杨晓滨' as name , 1 mark FROM DUAL) union all ( select '杨晓滨' as name , 2 mark FROM DUAL) union all ( select '杨晓滨' as name , 3 mark FROM DUAL) union all ( select '杨晓滨' as name , 4 mark FROM DUAL) union all ( select '杨晓滨' as name , 5 mark FROM DUAL)) SELECT NAME ,MARK, LAG (MARK, 3 , 0 ) OVER ( ORDER BY NAME ,MARK) FROM TMP; 输出：