Oracle：plsql常用函数汇总

1.在sqlplus下，实现中-英字符集转换

alter session set nls_language=&＃039;american&＃039;;
alter session set nls_language=&＃039;simplified chinese&＃039;;

2.运算符

算术运算符：+-*/

连接运算符：||

比较运算符：>，>=，=，!=，<，<=

逻辑运算符：not，and，or

集合运算符：intersect ，union，union all， minus（对应集合的列数和数据类型相同，union不包括重复行，union all包括重复行）

3.日期函数

1）add_months：日期加减日期后的日期

2）last_day：该月最后一天的日期

3）months_between：日期之间的月份

4）next_day(date,char)：返回下个星期的日期，day为1-7或星期日-星期六，1表示星期日

5）round：舍入到最接近的日期（round(参数,&＃039;day&＃039;):day:舍入到最接近的星期日）

6）trunc：截断到最接近的日期

7）greatest：返回值列表的最大值，若是日期，则返回最晚值

8）least：返回值列表的最小值，若是日期，则返回最早值

4.字符函数

1）substr：截取字符串

select substr(&＃039;serqewrgret&＃039;,1,3)substrs from dual;

2）||，wm_concat()，concat：连接字符

3）decode：选择函数

4）length：计算列中数据的长度

5）replace：替换函数

select replace(&＃039;abc&＃039;,&＃039;b&＃039;,&＃039;xy&＃039;) from dual;

6）ltrim、rtrim、trim：去掉字符串中的空格

select ltrim(&＃039; a&＃039;) s1, rtrim(&＃039;b &＃039;) s2, trim(&＃039; c &＃039;) s3 from dual;

7）去掉前导和后缀

select trim(leading 9 from 9998767999) s1,
trim(trailing 9 from 9998767999) s2,
trim(9 from 9998767999) s3
from dual;

8）lower：小写，upper：转大写，initcap：首字母转大写

select lower(&＃039;hha&＃039;) s1, upper(&＃039;hhei&＃039;) s2, initcap(&＃039;hhehe&＃039;) s3 from dual;

9）translate：替换掉需要替换的字符，一个替换一个……

select translate(&＃039;abc&＃039;,&＃039;b&＃039;,&＃039;a3&＃039;) from dual;

10）lpad（左填充），rpad（右填充）：填充

select lpad(&＃039;a&＃039;,6,&＃039;!&＃039;) s1, rpad(&＃039;b&＃039;,6,&＃039;=&＃039;) s2 from dual;

11）nvl(x1,x2)：x1为空，则显示为x2的值，否则就是x1

12）nvl2(x,x1,x2)：x不为空，则显示为x1，否则显示为x2

13）nullif(x1,x2)：若x1的值与x2的值相等，则返回为空，否则返回x1

14）coalesce(表达式1,表达式2,表达式3,表达式4,……,表达式n)：返回第一个不为空的表达式

select ename,coalesce(sal,100),sal,comm from emp;

5.数字函数

1）保留小数位：round四舍五入，trunc直接截断

select round(874.96,2) s1,trunc(874.9412,2) s2 from dual;

2）取整：ceil向上取整，floor向下取整

select ceil(2087.2) s1, floor(2087.2) s2 from dual;

3）power：取幂，sqrt：平方根

select power(3,2) s1,sqrt(9) s2 from dual;

4）求余

select mod(1,2) from dual;

5）sign：返回值的符号(正数返回为1，负数为-1)

select sign(-30),sign(30) from dual;

6.分组函数

min，max，avg，count，sum

1）group by 和having

例：部门56的最高工资，最低工资，平均工资，总人数，有工作的人数，工种数量及工资总和

select deptno,
max(ename),
max(sal),
min(ename),
min(sal),
avg(sal),
count(*),
count(job),
count(distinct(job)),
sum(sal)
from emp
group by deptno
having deptno = 56;

2）stddev：返回一组值的标准偏差

select x,stddev(y) from xx group by x;

3）variance：返回一组值的方差

select x,variance(y) from xx group by x;

4）rollup：按分组的第一个列进行统计和最后的小计

select x,y,sum(z) from emp group by x,y; select x,y,sum(z) from emp group by rollup(x,y);

5）cube：按分组的所有的列进行统计和最后的小计

select x,y,sum(z) from emp group by cube(x,y);

7.聚合函数/排序函数+over(partition by column order by column)

常见的有：

row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)

8.其他

1）user：返回登录的用户名

select user from dual;

2）vsize：返回表达式所需的字节数

select vsize(&＃039;i am xx;&＃039;) from dual; --8