Oracle常用函数整理

1.  单行函数：操作一行数据，返回一个结果

常用的单行函数有：

字符串函数：对字符串操作。

数字函数：对数字进行计算，返回一个数字。

日期函数：对日期和时间进行处理。

转换函数：可以将一种数据类型转换为另外一种数据类型。

2.  聚合函数(多行函数、分组函数、组函数)：操作多行数据，并返回一个结果。比如 SUM

一、字符串函数

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。

常用的字符函数：

 

函数	说明	示例	示例结果
ASCII(X)	返回字符X的ASCII码	SELECT ASCII('a') FROM dual;	97
CONCAT(X,Y)	连接字符串X和Y	SELECT CONCAT('Hello','world') FROM dual;	Helloworld
INSTR(X,STR[,START][,N)	从X中查找str，可以指定从start开始，也可以指定从n开始	SELECT INSTR('Hello world','or') FROM dual;	8
LENGTH(X)	返回X的长度	SELECT LENGTH('Hello') FROM dual;	5
LOWER(X)	X转换成小写	SELECT LOWER('Hello') FROM dual;	hello
UPPER(X)	X转换成大写	SELECT UPPER('hello') FROM dual;	HELLO
LTRIM(X[,TRIM_STR])	把X的左边截去trim_str字符串，缺省截去空格	SELECT LTRIM('=Hello=','=') FROM dual;	Hello=
RTRIM(X[,TRIM_STR])	把X的右边截去trim_str字符串，缺省截去空格	SELECT RTRIM('=Hello=','=') FROM dual;	=Hello
TRIM([TRIM_STR  FROM]X)	把X的两边截去trim_str字符串，缺省截去空格	SELECT TRIM('='FROM'=Hello=') FROM dual;	Hello
REPLACE(X,old,new)	在X中查找old，并替换成new	SELECT REPLACE('ABCDE','CD','AAA')FROM dual;	ABAAAE
TRANSLATE(char, from, to)	在char中把from字符逐一替换为to	SELECT TRANSLATE('123456789','4564','abcd') FROM DUAL;	123abc789
REGEXP_REPLACE(sourceStr, patternStr[, replacedStr])	sourceStr标识要被替换的字段名或字符串，patternStr被替换字符串所需要匹配的正则表达式，replacedStr表示要替换成的字符串。如果没有replacedStr则删除正则表达式所匹配的字符串。	SELECT REGEXP_REPLACE('A9是8C7','[^0-9]','0') FROM dual	090807
SUBSTR(X,start[,length])	返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾	SELECT SUBSTR('ABCDE',2,3) FROM dual;	BCD
INITCAP(char)	把每个字符串的第一个字符换成大写	SELECT INITCAP('mr.ecop') FROM dual;	Mr.Ecop
WM_CONCAT(列名)	把列值以“，”分隔开，显示为字符串	SELECT WM_CONCAT(NAME) NAME FROM TEST;	a,b,c,d,e

 

二、数字函数

数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。

 

函数	说明	示例
ABS(X)	X的绝对值	ABS(-3)=3
ACOS(X)	X的反余弦	ACOS(1)=0
COS(X)	余弦	COS(1)=0.54030230586814
CEIL(X)	大于或等于X的最小值	CEIL(5.4)=6
FLOOR(X)	小于或等于X的最大值	FLOOR(5.8)=5
LOG(X,Y)	X为底Y的对数	LOG(2，4)=2
MOD(X,Y)	X除以Y的余数	MOD(8，3)=2
POWER(X,Y)	X的Y次幂	POWER(2，3)=8
ROUND(X[,Y])	X在第Y位四舍五入	ROUND(3.456，2)=3.46
SQRT(X)	X的平方根	SQRT(4)=2
TRUNC(X[,Y])	X在第Y位截断	TRUNC(3.456，2)=3.45

 

说明：

1.  ROUND(X[,Y])，四舍五入。

在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。

y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。

y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

2.  TRUNC(x[,y])，直接截取，不四舍五入。

在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。

Y是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。

y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

三、日期函数

日期函数对日期进行运算。常用的日期函数有：

1、ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。

d 表示日期，n 表示要加的月数。

例：SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

结果：

2、LAST_DAY(d)，返回指定日期当月的最后一天。

例： SELECT SYSDATE,last_day(SYSDATE) FROM dual;

 结果：

3、ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式

模型。默认 fmt 为 DDD，即月中的某一天。

  ① 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。

  ② 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。

  ③ 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。

  ④ 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。

例：SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

结果：

与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作， TRUNC 与 ROUND 非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。

4、EXTRACT(fmt FROM d)，提取日期中的特定部分。

fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。

HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。

例：

SELECT SYSDATE "date",
       EXTRACT(YEAR FROM SYSDATE)"year",
       EXTRACT(MONTH FROM SYSDATE)"month",
       EXTRACT(DAY FROM SYSDATE)"day",
       EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",
       EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",
       EXTRACT(SECOND FROM SYSTIMESTAMP)"second"

 

FROM dual;

结果：

 

四、转换函数

转换函数将值从一种数据类型转换为另外一种数据类型。常见的转换函数有：

1、ge'fd(d|n[,fmt])

把日期和数字转换为制定格式的字符串。Fmt是格式化字符串

代码演示：TO_CHAR对日期的处理

SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;

结果：

代码解析：

在格式化字符串中，使用双引号对非格式化字符进行引用

针对时间日期的格式化，格式化字符有：

参数	说明	示例
Y	Y或YY或YYY 年的最后一位，两位，三位	SELECT TO_CHAR(SYSDATE,'YYY') FROM dual;
Q	季度,1-3月为第一季度	SELECT TO_CHAR(SYSDATE,'Q') FROM dual;
MM	月份数	SELECT TO_CHAR(SYSDATE,' MM') FROM dual;
RM	月份的罗马表示	SELECT TO_CHAR(SYSDATE,' RM') FROM dual;
month	用9个字符表示的月份名	SELECT TO_CHAR(SYSDATE,'month') FROM dual;
ww	当年第几周	SELECT TO_CHAR(SYSDATE,'ww') FROM dual;
w	本月第几周	SELECT TO_CHAR(SYSDATE,'w') FROM dual;
DDD	当年第几天,一月一日为001 ,二月一日032	SELECT TO_CHAR(SYSDATE,'DDD') FROM dual;
DD	当月第几天	SELECT TO_CHAR(SYSDATE,'DD') FROM dual;
D	星期几	SELECT TO_CHAR(SYSDATE,'D') FROM dual;
DY	星期几的缩写	SELECT TO_CHAR(SYSDATE,'DY') FROM dual;
hh12	12小时制小时数	SELECT TO_CHAR(SYSDATE,'hh12') FROM dual;
hh24	24小时制小时数	SELECT TO_CHAR(SYSDATE,'hh24') FROM dual;
Mi	分钟数	SELECT TO_CHAR(SYSDATE,'Mi') FROM dual;
ss	秒数	SELECT TO_CHAR(SYSDATE,'ss') FROM dual;

针对数字的格式化，格式化字符有：

 

参数	示例	说明
9	999	指定位置处显示数字
.	9.9	指定位置返回小数点
,	99,99	指定位置返回一个逗号
$	$999	数字开头返回一个美元符号
EEEE	9.99EEEE	科学计数法表示
L	L999	数字前加一个本地货币符号
PR	999PR	如果数字式负数则用尖括号进行表示

 

代码演示：TO_CHAR对数字的处理

SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual;

 结果：

2、TO_DATE(X,[,fmt])

把一个字符串以fmt格式转换成一个日期类型

3、TO_NUMBER(X,[,fmt])

把一个字符串以fmt格式转换为一个数字

代码演示：TO_NUMBER函数

SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;

 结果：

五、其它单行函数

1、NVL(X,VALUE)

如果X为空，返回value，否则返回X

2、NVL2(x,value1,value2)

如果x非空，返回value1，否则返回value2 。

3、DECODE(x,value1,return1,value2,return2,.....,valueN,returnN, returnEnd) 

如果x的值等于value1，则返回return1 ，等于value2，返回return2，以此类推，returnEnd是缺省值，当X的值均不满足条件时，返回returnEnd。

等同于：

IF X = value1 THEN
　　　　return1
ELSIF 条件=value2 THEN
　　　　return2
　　　　......
ELSIF 条件=valueN THEN
　　　　returnN
ELSE
　　　  returnEnd
END IF
 

 

4、PIVOT(列转行)

PIVOT（聚合函数 for 列名 in （定义列名和范围）），其中in（''）中可以指定别名和子查询。

代码演示：

create table demo(id int,name varchar(20),nums int);  ---- 创建表
insert into demo values(1, '苹果', 1000);
insert into demo values(2, '苹果', 2000);
insert into demo values(3, '苹果', 4000);
insert into demo values(4, '橘子', 5000);
insert into demo values(5, '橘子', 3000);
insert into demo values(6, '葡萄', 3500);
insert into demo values(7, '芒果', 4200);
insert into demo values(8, '芒果', 5500);

 

select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));

 

结果：

5、UNPIVOT(行转列)

UNPIVOT（新定义列名1 for 新定义列名2 in（定义列名和范围）），其中in（''）中可以指定别名和子查询。

代码演示：

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO
  FROM (SELECT E.ENAME,
               E.JOB,
               E.SAL,
               E.DEPTNO,
               RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) RANK  --在按部门划分的基础上，工资从高到低分级，级别RANK从1开始依次递增
          FROM EMP E) E
WHERE E.RANK = 1 ;
 

 

 select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )

 

结果：

 

三、OVER()分析函数

分析函数：聚合函数（如sum()、max()等）可以计算基于组的某种聚合值，但是聚合函数对于某个组只能返回一行记录。若想对于某组返回多行记录，则需要使用分析函数。

备注：使用OVER分析函数时，分组需用使用partition by。

分区partiton by 与 group by的区别

group by会将结果集按照指定字段进行聚合，结果集会缩减，在统计部门人数，平均工资时会用到；

partition by会对结果集按照指定字段分层排列，结果集不会缩减，如将公司所有人按照部门进行分区，会发现结果集中同一部门的人会连续排列。

1、rank()/dense_rank over(partition by ... order by ...)

说明：over()在什么条件之上; 

　　partition by 按哪个字段划分组；

　　order by 按哪个字段排序；

注意：

　　（1）使用rank()/dense_rank() 时，必须要带order by否则非法

　　（2）rank()/dense_rank()分级的区别：

　　　　rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。

　　　　dense_rank(): 连续排序，如果有两个第一级时，接下来仍然是第二级。

代码演示：

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO
  FROM (SELECT E.ENAME,
               E.JOB,
               E.SAL,
               E.DEPTNO,
               RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) RANK  --在按部门划分的基础上，工资从高到低分级，级别RANK从1开始依次递增
          FROM EMP E) E
WHERE E.RANK = 1 ;
 

 

2、min()/max() over(partition by ...)

注：这里没有排序条件，若加上order by 排序条件，

MAX() OVER(PARTITION BY .. ORDER BY .. DESC) 排序规则只能为desc，否则不起作用，将查询到目前为止排序值最高字段的对应值

MIN() OVER(PARTITION BY .. ORDER BY .. ASC ) 排序规则只能为asc，否则不起作用，将查询到目前为止排序值最低的字段的对应值，

3、lead()/lag() over(partition by ... order by ...) 取前面/后面第n行记录

说明：

lead(列名,n,m): 当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。

lag(列名,n,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

 

示例：查询个人工资与比自己高一位、第一位的工资的差额

 

4、FIRST_VALUE/LAST_VALUE() OVER(PARTITION BY ...) 取首尾记录

代码演示：

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO,E.ROW_NUM
FROM (SELECT E.ENAME,
E.JOB,
E.SAL,
E.DEPTNO,
ROW_NUMBER() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) ROW_NUM
FROM EMP E) E
WHERE E.ROW_NUM > 3;

 

5、ROW_NUMBER() OVER(PARTITION BY.. ORDER BY ..) 排序

代码演示：

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO,E.ROW_NUM
FROM (SELECT E.ENAME,
E.JOB,
E.SAL,
E.DEPTNO,
ROW_NUMBER() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) ROW_NUM
FROM EMP E) E
WHERE E.ROW_NUM > 3;

　　

6、sum/avg/count() over(partition by ..)

代码演示：

SELECT E.ENAME,
E.JOB,
E.SAL,
E.DEPTNO,
SUM(E.SAL) OVER(PARTITION BY E.DEPTNO) SUM_SAL, --统计某组中的总计值
AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG_SAL, --统计某组中的平均值
COUNT(E.SAL) OVER(PARTITION BY E.DEPTNO) COUNT_SAL --按某列分组，并统计该组中记录数量
FROM EMP E;
SELECT A.DEPT_ID,
A.SALE_DATE,
A.GOODS_TYPE,
A.SALE_CNT,
SUM(A.SALE_CNT) OVER(PARTITION BY A.DEPT_ID) DEPT_TOTAL, --部门销售总和
SUM(A.SALE_CNT) OVER() CMP_TOTAL, --公司销售总额
AVG(A.SALE_CNT) OVER(PARTITION BY A.DEPT_ID) DEPT_AVG, --部门销售均值
AVG(A.SALE_CNT) OVER() CMP_AVG --公司销售均值
FROM LEARN_FUN_KEEP A;
 

 

 7、 rows/range between … preceding and … following 上下范围内求值

 说明：unbounded：不受控制的，无限的

　　　preceding：在...之前

　　　following：在...之后

 rows between … preceding and … following

代码演示：

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意ROWS BETWEEN unbounded preceding AND current row 是指第一行至当前行的汇总
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
FROM SCOTT.EMP;
 

 

8、NULLS FIRST/LAST 将空值字段记录放到最前或最后显示

代码演示：

SELECT E.ENAME,
       E.JOB,
       E.SAL,
       E.DEPTNO,
       RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL NULLS LAST)
  FROM EMP E;

 

9、NTILE(n) 获取部分数据

代码演示：

SELECT E.ENAME,
       E.JOB,
       E.SAL,
       E.DEPTNO,
       NTILE(3) OVER(ORDER BY E.SAL DESC NULLS LAST) ALL_CMP, --若只取前三分之一，all_cmp=1即可，若只取中间三分之一，all_cmp=2即可
       NTILE(3) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC NULLS LAST) ALL_DEPT  --每个部门的分成三部分
  FROM EMP E

 

 备注：这是我自己对Oracle函数的整理笔记，有些笔记为了偷懒，就直接复制别人的代码，请代码原主人不要介意，谢谢