oracle 常用函数
参考： oracle常用函数详解（详细）
两种类型函数
oracle 数据库中主要使用两种类型的函数：
单行函数
单行函数：操作一行数据，返回一个结果
- 字符串函数：对字符串操作。
- 数字函数：对数字进行计算，返回一个数字。
- 日期函数：对日期和时间进行处理。
- 转换函数：可以将一种数据类型转换为另外一种数据类型。
聚合函数
聚合函数(多行函数、分组函数、组函数)：操作多行数据，并返回一个结果。比如 SUM
日期函数
日期函数对日期进行运算。常用的日期函数有：
ADD_MONTHS(d,n)
ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。
-- ADD_MONTHS(d,n) - 当前日期加2个月
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2) FROM DUAL;LAST_DAY(d)
LAST_DAY(d)，返回指定日期当月的最后一天。
-- LAST_DAY(d) - 返回当月最后一天
SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL;ROUND(d[,fmt])
与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作， TRUNC 与 ROUND 非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。
ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式
- 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。
- 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。
- 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
- 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
-- ROUND(d,[fmt]) - 四舍五入
SELECT SYSDATE, ROUND(SYSDATE), ROUND(SYSDATE, 'day'),
ROUND(SYSDATE, 'month'), ROUND(SYSDATE, 'year') FROM DUAL;TRUNC(date[,fmt])
TRUNC函数为指定元素而截去的日期值。其中：date 一个日期值，fmt 日期格式，该日期将由指定的元素格式所截去。
-- TRUNC(date[,fmt]) - 截取日期值
SELECT TRUNC(SYSDATE, 'yyyy'), TRUNC(SYSDATE, 'mm'), TRUNC(SYSDATE, 'd'), TRUNC(SYSDATE, 'dd') FROM DUAL;说明
- trunc（sysdate,’yyyy’） –返回当年第一天。
- trunc（sysdate,’mm’） –返回当月第一天。
- trunc（sysdate,’d’） –返回当前星期的第一天。
- trunc（sysdate,’dd’）–返回当前年月日
EXTRACT(fmt FROM d)
HOUR 匹配的结果中加上 8 小时，因此在中国时间一致。
EXTRACT(fmt FROM d)，提取日期中的特定部分。
fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。
-- EXTRACT(fmt FROM d) - 提取日期中的特定部分
SELECT SYSDATE "date",
EXTRACT(YEAR FROM SYSDATE) "year",
EXTRACT(MONTH FROM SYSDATE) "month",
EXTRACT(DAY FROM SYSDATE) "day",
EXTRACT(HOUR FROM SYSTIMESTAMP)+8 "hour",
EXTRACT(MINUTE FROM SYSTIMESTAMP) "minute",
EXTRACT(SECOND FROM SYSTIMESTAMP) "second"
FROM DUAL;转换函数
转换函数将值从一种数据类型转换为另外一种数据类型。常见的转换函数有：
TO_CHAR(d|n[,fmt])
把日期和数字转换为制定格式的字符串。Fmt是格式化字符串
-- TO_CHAR(d|n[,fmt]) - 转换为字符串
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date" FROM DUAL;代码解析：
在格式化字符串中，使用双引号对非格式化字符进行引用，针对数字的格式化，请自行百度。
TO_DATE(X,[,fmt])
把一个字符串以fmt格式转换成一个日期类型
-- TO_DATE(X,[,fmt]) - 转换为日期类型
SELECT TO_DATE('2020-2-18 15:37:41', 'YYYY-MM-DD HH24:MI:SS') "date" FROM DUAL;TO_NUMBER(X,[,fmt])
把一个字符串以fmt格式转换为一个数字
-- TO_NUMBER(X,[,fmt]) - 转换为数字
SELECT TO_NUMBER('-$12,345.67','$99,999.99') "num" FROM DUAL;CAST(X as type)
将X转换为指定的兼容的数据库类型
-- CAST(X as type) - 转换兼容类型
SELECT CAST(12345.67 AS VARCHAR2(10)),
CAST('18-2月-20' AS DATE),
CAST(12345.678 AS NUMBER(10, 2))
FROM DUAL;其它单行函数
NVL(X,VALUE)
如果X为空，返回value，否则返回X
例：对工资是2000元以下的员工，如果没发奖金，每人奖金100元
SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;NVL2(x,value1,value2)
如果x非空，返回value1，否则返回value2
例：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元
SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm" FROM EMP WHERE SAL<2000;聚合函数
聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。
常用函数
名称 | 作用 | 语法 |
|---|---|---|
AVG | 平均值 | AVG(表达式) |
SUM | 求和 | SUM(表达式) |
MIN、MAX | 最小值、最大值 | MIN(表达式)、MAX(表达式) |
COUNT | 数据统计 | COUNT(表达式) |
对分组行使用聚集函数
对分组后的行使用聚集函数，聚集函数会统计每组中的值，对于每组分别统计后返回一个值。
--按照职位分组，求出每个职位的最高和最低工资
select job ,max(sal),min(sal) from emp
group by job
order by job;注意： 一、 分组时select子句后边的列名必须与group by子句后的列名一致，除非是聚合函数
--错误，因为deptno不是聚集函数，也不是group by后面跟的列名
select deptno,avg(sal) from EMP;二、 不能使用聚集函数作为WHERE子句的筛选条件
--错误，聚合函数不能作为筛选条件
select deptno from emp where avg(sal)>1000;三、 分组后，需要使用条件进行筛选，则使用having过滤分组后的行，不能使用where，where只能放在group by前面。
select deptno, avg(sal) from emp where deptno<>10
group by deptno
having avg(sal) > 900;集合操作符
Oracle 支持如下几个集合操作符。
- UNION 用来求两个集合的并集，并去掉重复值
- UNION ALL 用来求两个集合的并集
- INTERSECT 用来求两个集合的交集，并去掉重复值
- MINUS 用来求第一个集合中存在，而第二个集合中不存在的记录，并去掉重复值
数据准备
CREATE TABLE TEST_LEFT
(
COL CHAR
);
INSERT INTO TEST_LEFT VALUES ('A');
INSERT INTO TEST_LEFT VALUES ('A');
INSERT INTO TEST_LEFT VALUES ('B');
INSERT INTO TEST_LEFT VALUES ('B');
INSERT INTO TEST_LEFT VALUES ('C');
CREATE TABLE TEST_RIGHT
(
COL CHAR
);
INSERT INTO TEST_RIGHT VALUES ('A');
INSERT INTO TEST_RIGHT VALUES ('B');
INSERT INTO TEST_RIGHT VALUES ('B');
INSERT INTO TEST_RIGHT VALUES ('D');
INSERT INTO TEST_RIGHT VALUES ('E');UNION
UNION 用来求两个集合的并集，并去掉重复值
-- UNION
SELECT * FROM TEST_LEFT
UNION
SELECT * FROM TEST_RIGHT;
---结果
A
B
C
D
EUNION ALL
UNION ALL 用来求两个集合的并集
-- UNION ALL
SELECT * FROM TEST_LEFT
UNION ALL
SELECT * FROM TEST_RIGHT;
---结果
A
A
B
B
C
A
B
B
D
EINTERSECT
INTERSECT 用来求两个集合的交集，并去掉重复值
-- INTERSECT
SELECT * FROM TEST_LEFT
INTERSECT
SELECT * FROM TEST_RIGHT;
---结果
A
BMINUS
MINUS 用来求第一个集合中存在，而第二个集合中不存在的记录，并去掉重复值
-- MINUS
SELECT * FROM TEST_LEFT
MINUS
SELECT * FROM TEST_RIGHT;
---结果
C总结
大家对比一下它们之间的结果就可以看出它们之间的区别，不过有个问题需要注意:
UNION 和 INTERSECT 两个集合顺序是可以互换的，但是 MINUS 互换将有不同的结果，如下：
-- 互换集合-1
SELECT * FROM TEST_LEFT
MINUS
SELECT * FROM TEST_RIGHT;
---结果
C
-- 互换集合-2
SELECT * FROM TEST_RIGHT
MINUS
SELECT * FROM TEST_LEFT;
---结果
D
E其他
GREATEST(expr_1, expr_2, …expr_n)
求多列的最大值，oracle中的 greatest 函数；
求多列的最小值，oracle中的 least 函数。
SELECT ID, A, B, C,
GREATEST(A,B,C) max,
LEAST(A,B,C) min
FROM TEST_INDEXexpr_2, … expr_n等中找出最大的数返回。在比较时，OracIe会自动按表达式的数据类型进行比较，以expr_1的数据类型为准。
四舍五入
Oracle 提供了以下四个函数用来做四舍五入。
- CEIL 向上四舍五入
- FLOOR 向下四舍五入
- ROUND 四舍五入
- TRUNC 去掉小数
-- 四舍五入
SELECT 5.5 NUM, CEIL(5.5) CEIL, FLOOR(5.5) FLOOR, ROUND(5.5) ROUND, TRUNC(5.5, 0) TRUNC FROM DUAL
UNION ALL
SELECT 2.5 NUM, CEIL(2.5) CEIL, FLOOR(2.5) FLOOR, ROUND(2.5) ROUND, TRUNC(2.5, 0) TRUNC FROM DUAL
UNION ALL
SELECT 1.6 NUM, CEIL(1.6) CEIL, FLOOR(1.6) FLOOR, ROUND(1.6) ROUND, TRUNC(1.6, 0) TRUNC FROM DUAL
UNION ALL
SELECT 1.1 NUM, CEIL(1.1) CEIL, FLOOR(1.1) FLOOR, ROUND(1.1) ROUND, TRUNC(1.1, 0) TRUNC FROM DUAL
UNION ALL
SELECT 1 NUM, CEIL(1) CEIL, FLOOR(1) FLOOR, ROUND(1) ROUND, TRUNC(1, 0) TRUNC FROM DUAL
UNION ALL
SELECT -1 NUM, CEIL(-1) CEIL, FLOOR(-1) FLOOR, ROUND(-1) ROUND, TRUNC(-1, 0) TRUNC FROM DUAL
UNION ALL
SELECT -1.1 NUM, CEIL(-1.1) CEIL, FLOOR(-1.1) FLOOR, ROUND(-1.1) ROUND, TRUNC(-1.1, 0) TRUNC FROM DUAL
UNION ALL
SELECT -1.6 NUM, CEIL(-1.6) CEIL, FLOOR(-1.6) FLOOR, ROUND(-1.6) ROUND, TRUNC(-1.6, 0) TRUNC FROM DUAL
UNION ALL
SELECT -2.5 NUM, CEIL(-2.5) CEIL, FLOOR(-2.5) FLOOR, ROUND(-2.5) ROUND, TRUNC(-2.5, 0) TRUNC FROM DUAL
UNION ALL
SELECT -5.5 NUM, CEIL(-5.5) CEIL, FLOOR(-5.5) FLOOR, ROUND(-5.5) ROUND, TRUNC(-5.5, 0) TRUNC FROM DUAL;