欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle:plsql常用函数汇总

程序员文章站 2022-06-21 07:54:56
1.在sqlplus下,实现中-英字符集转换 alter session set nls_language='american'; alter session set nls_...

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

alter session set nls_language='american';
alter session set nls_language='simplified chinese'; 

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(参数,'day'):day:舍入到最接近的星期日)

6)trunc:截断到最接近的日期

7)greatest:返回值列表的最大值,若是日期,则返回最晚值

8)least:返回值列表的最小值,若是日期,则返回最早值

4.字符函数

1)substr:截取字符串

select substr('serqewrgret',1,3)substrs from dual;

2)||,wm_concat(),concat:连接字符

3)decode:选择函数

4)length:计算列中数据的长度

5)replace:替换函数

select replace('abc','b','xy') from dual;

6)ltrim、rtrim、trim:去掉字符串中的空格

select ltrim(' a') s1, rtrim('b ') s2, trim(' c ') 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('hha') s1, upper('hhei') s2, initcap('hhehe') s3 from dual;

9)translate:替换掉需要替换的字符,一个替换一个……

select translate('abc','b','a3') from dual;
10)lpad(左填充),rpad(右填充):填充
select lpad('a',6,'!') s1, rpad('b',6,'=') 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('i am xx;') from dual; --8