Oracle常用函数

目录

一、数学函数

1.ABS(X)取绝对值

2.ROUND(X,Y) 四舍五入

3.TRUNC(X,Y)截断

4.FLOOR向下取整(<)

5.CEIL向上取整(>)

6.MOD取余数与MOD(X,Y) x除以y的余数

7.TRUNC取商(相当于取整)

8.POWER取幂

二、日期函数

1.日期 + 整数 得到的是 日期

2.日期 - 日期 得到的是 数字 单位是 天

3.TRUNC 也可以对日期做截断

4.使用TO_CHAR判断日期  

5.月份加减ADD_MONTHS(时间,数字)

6.LAST_DAY(时间)，返回指定日期 当月 的最后一天

7.MONTHS_BETWEEN(大的日期,小的日期)

三、字符串函数

1.两两拼接CONCAT(参数1,参数2)

2.管道拼接符  ||

3.wm_concat(x)+GROUP BY

4.字符串长度LENGTH(参数)

5.字节长度LENGTHB(参数)  

6.转换为大写UPPER()

7.转换为小写LOWER()

8.ASCII(x)返回字符x的ASCII码

9.CHR(ASCII编码)返回的是第一个字母对应的  ASCII 编码

10.左填充  LPAD   left pad

11.右填充  RPAD  right pad

12.字符串去除空格

13.匹配截取LTRIM(字符串,字符集)

14.查找字符串位置INSTR

15.截取字符串SUBSTR

16.替换REPLACE(字符串,旧的值,新的值)

---

一、数学函数

1.ABS(X)取绝对值

SELECT ABS(-1) a
FROM dual;

2.ROUND(X,Y) 四舍五入

--保留y位小数

SELECT trunc(31.1456, -2) a1, ROUND(3.124) a2
FROM dual;

3.TRUNC(X,Y)截断

--在 X 的第 Y 位截断

--截断的意思就是：从截断的那个位置开始，后面的所有数据都变成 0

--如果第二个参数是正数，那么就是从小数点之后截断，如果是负数，那么就是从小数点之前截断

SELECT TRUNC(3567.1263, 2) a1, TRUNC(3567.1243, -2) a2, TRUNC(3567.1243) a3
FROM dual;

4.FLOOR向下取整(<)

SELECT FLOOR(56.34)  a1 --56, FLOOR(-56.34) a2 -- -57
FROM dual;

5.CEIL向上取整(>)

SELECT CEIL(56.34) a1, -- 57CEIL(-56.34) a2 -- -56
FROM dual;

6.MOD取余数与MOD(X,Y) x除以y的余数

SELECT MOD(4, 2) a1, -- 0MOD(5, 3) a2 -- 2
FROM dual;

7.TRUNC取商(相当于取整)

SELECT TRUNC(5 / 3) a1 FROM dual; -- 1

8.POWER取幂

SELECT POWER(2, 1) a1, POWER(2, -1) a2, POWER(2, 1 / 2) a3
FROM dual;

二、日期函数

SELECT TO_DATE('20220610133050', 'YYYYMMDDHH24MISS') a,TO_DATE('20220610123050', 'YYYYMMDDHHMISS') b
FROM DUAL;

1.日期 + 整数 得到的是 日期

示例：获取明天的日期

SELECT SYSDATE+1 FROM dual;

示例：当前时间往前推1小时

SELECT SYSDATE-1/24 FROM dual;

日期相减计算：

SELECT TO_DATE('20220610', 'YYYYMMDD') - TO_DATE('20220710', 'YYYYMMDD')
FROM DUAL; -- 30

2.日期 - 日期 得到的是 数字 单位是 天

示例：获取2024年天数

SELECT to_date('20241231', 'yyyymmdd') -to_date('20240101', 'yyyymmdd') + 1 a
FROM dual;

3.TRUNC 也可以对日期做截断

示例：截取当年第一天

SELECT TRUNC(SYSDATE, 'yyyy') FROM dual; -- 2025-01-01

示例：截取当月第一天

下面两种方式都可以

SELECT TRUNC(SYSDATE, 'MM') FROM dual;SELECT TO_DATE('202407', 'YYYYMM') FROM DUAL;

示例：获取当前的年月日

下面两种方式都可以

SELECT TRUNC(SYSDATE, 'DD') FROM dual;SELECT TRUNC(SYSDATE) FROM dual;

示例：截取当月第一天

SELECT TRUNC(SYSDATE, 'MM') FROM dual;

示例：截断到小时

SELECT TRUNC(SYSDATE, 'hh') FROM dual;

示例：截断到分

SELECT TRUNC(SYSDATE, 'mi') FROM dual;

示例：当前所在星期的第一天

SELECT TRUNC(SYSDATE, 'd') FROM dual;

示例：返回当前时间所在季度的第一天  -- QUARTER

SELECT TRUNC(SYSDATE, 'Q') FROM dual;

4.使用TO_CHAR判断日期  

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')   a1, TO_CHAR(SYSDATE, 'YYYY-MM-DD') a2, TO_CHAR(SYSDATE, 'YYYY')       第几年, TO_CHAR(SYSDATE, 'MM')         本年第几月, TO_CHAR(SYSDATE, 'DD')         本月第几天, TO_CHAR(SYSDATE, 'Q')          第几个季度, TO_CHAR(SYSDATE, 'W')          本月第几周 --MONTH OF WEEK, TO_CHAR(SYSDATE, 'D')          本周第几天 -- DAY OF WEEK, TO_CHAR(SYSDATE, 'WW')         本年第几周 --YEAR OF WEEK
FROM DUAL;

5.月份加减ADD_MONTHS(时间,数字)

示例：返回上个月的当天

select trunc(add_months(sysdate, -1), 'DD') from dual;

示例：往前推10年

select add_months(trunc(sysdate), -120) from dual;

示例：获取今年5月最后一天

select trunc(add_months(sysdate, 1), 'MM') - 1 from dual;SELECT LAST_DAY(TO_DATE('2025-05', 'YYYY-MM')) AS last_day
FROM DUAL;-- 提取当前年份 EXTRACT(YEAR FROM SYSDATE)
SELECT LAST_DAY(TO_DATE(EXTRACT(YEAR FROM SYSDATE) || '-05', 'YYYY-MM')) AS last_day_of_may
FROM DUAL;

6.LAST_DAY(时间)，返回指定日期 当月 的最后一天

示例：获取当月最后一天

select last_Day(trunc(sysdate)) from dual;

示例：获取下个月第一天

select trunc(add_months(sysdate, 1), 'MM') from dual;select last_day(trunc(sysdate, 'MM')) + 1 from dual;SELECT last_day(TRUNC(SYSDATE)) + 1 a FROM dual;SELECT TRUNC(SYSDATE, 'mm') a1, add_months(TRUNC(SYSDATE, 'mm'), 1) a2
FROM dual;

7.MONTHS_BETWEEN(大的日期,小的日期)

得到的结果是个数字, 可以是负数

示例：计算今年过去几个月，取整数

select months_between(trunc(sysdate), trunc(sysdate, 'yyyy')) a,ceil(months_between(trunc(sysdate), trunc(sysdate, 'yyyy'))) b, -- 向上取整trunc(months_between(trunc(sysdate), trunc(sysdate, 'yyyy'))) c -- 截取整数from dual;

1.查询每个员工的工号，入职日期，入职日期是入职年份的第几季度，入职日期所在季度的第一天是哪一天

select empno,hiredate,to_char(hiredate, 'Q') 入职季度,trunc(hiredate, 'Q') 入职季度第一天from emp;

2.查出 2022年3月2号所在季度的第一天

select trunc(to_date('20220302', 'YYYYMMDD'), 'Q') a from dual;

3.查出 2022年3月2号所在月的最后一天

select last_Day(to_date('20220302', 'YYYYMMDD')) a from dual;select add_months(trunc(to_date('20220302', 'YYYYMMDD'),'MM'), 1) - 1 a from dual;

4.查出2022年3月2号是所在年份的第几个季度

select to_char(to_date('20220302', 'YYYYMMDD'), 'YYYY') 所在年份,to_char(to_date('20220302', 'YYYYMMDD'), 'Q') 所在季度from dual;

5.由2022年3月2号得到2023年的1月1号

-- 第二年所在季度的第一天
select trunc(add_months(to_date('20220302', 'YYYYMMDD'), 12), 'Q') afrom dual;

6.输出 EMP表中 入职时间 的 上个季度 的 第一天的 月末

select hiredate,to_char(hiredate, 'Q') 入职季度,TRUNC(hiredate, 'Q') 入职季度第一天,ADD_MONTHS(TRUNC(hiredate, 'Q'), -3) 上个季度第一天,last_day(ADD_MONTHS(TRUNC(hiredate, 'Q'), -3)) 上个季度的第一天的月末from emp;

7.计算当前日期跟 2020-01-01 相差多少个月

select ceil(months_between(trunc(sysdate),to_date('2020-01-01', 'YYYY-MM-DD'))) 相差月份from dual; -- 65

三、字符串函数

1.两两拼接CONCAT(参数1,参数2)

select ename,empno,concat(concat(ename, job), sal) a,ename || empno || salfrom emp;

2.管道拼接符  ||

SELECT ename || '-' || empno || '-' || job a, 1 || 2 || 3 b FROM emp;

3.wm_concat(x)+GROUP BY

wm_concat(x)跟 GROUP BY 一起使用，将那一组的多行的某个字段的数据拼在一行里

SELECT DEPTNO, wm_concat(job) a FROM emp GROUP BY DEPTNO;

SELECT deptno, wm_concat(DISTINCT job) a FROM emp GROUP BY deptno;

每个部门有多少不同的工作种类

select count(job), count(distinct job) from emp group by deptno;

4.字符串长度LENGTH(参数)

SELECT LENGTH('ABC') AS char_count, -- 返回 3（3个字符）LENGTH('你好') AS char_count,    -- 返回 2（2个字符）  LENGTH('A😊B') AS char_count   -- 返回 3（1个字母 + 1个表情符号 + 1个字母）    
FROM DUAL;

5.字节长度LENGTHB(参数)  

SELECT LENGTHB('ABC') AS byte_count,     -- 返回 3（每个字符占1字节）LENGTHB('你好') AS byte_count,     -- 返回 6（每个中文占3字节）LENGTHB('A😊B') AS byte_count     -- 返回 8（表情符号在UTF-8中占4字节）
FROM DUAL;

6.转换为大写UPPER()

select upper('hello') from dual;

7.转换为小写LOWER()

select lower('HELLO') from dual;

8.ASCII(x)返回字符x的ASCII码

select ASCII('a') a, -- 97ASCII('Aa') b, -- 65ASCII(',') c, -- 44ASCII('好') d, -- 47811ASCII('''') a, -- 单引号的ASCII码值是39ASCII(6) -- 54from dual;-- 查询单引号
SELECT '''' a FROM dual;

9.CHR(ASCII编码)返回的是第一个字母对应的  ASCII 编码

通过 ASCII 编码返回对应的字符

SELECT CHR(97) a1, CHR(50403) || CHR(47811) a2 FROM dual;

10.左填充  LPAD   left pad

LPAD(要填充的那个字符串,填充之后的总长度,以什么字符填充)

select lpad('1234', 7, 'a') from dual; -- aaa1234
select lpad('1234', 7, 'ab') from dual; -- aba1234
select lpad('01234', 3, 'ab') from dual; -- 012

11.右填充  RPAD  right pad

SELECT RPAD('1234', 7, 'a') a1, -- 1234aaaRPAD('1234', 7, 'ab') a2, -- 1234abaRPAD('01234', 3, 'ab') a3 -- 012FROM dual;

1.将员工的编号、姓名、工作拼接起来

select empno || ename || job from emp;

2.将每一个岗位的所有员工的名字拼接到一起

select job, wm_concat(ename) from emp group by job;

3.查找名字长度是 4个字符的名字

select ename from emp where length(ename) = 4;

12.字符串去除空格

TRIM (字符串)--去除字符串两边空格

LTRIM(字符串)  ---去除字符串左边空格 LEFT

RTRIM (字符串)---去除字符串右边空格 RIGHT

REPLACE也可以去除字符串内部的空格

SELECT TRIM('  12 3  45  ') a,LTRIM('  12 3  45  ') a2,RTRIM('  12 3  45  ') a3,REPLACE('asdfg   dfhdhf', ' ') a4 -- asdfgdfhdhfFROM dual;

13.匹配截取LTRIM(字符串,字符集)

LTRIM(字符串,字符集) ---从字符串的左边开始与字符集相匹配，匹配的上就消掉，

                     ---直到字符不在字符集里面则停止匹配，并截取

找到一部分也会截取

SELECT LTRIM('asdasfgZXZc', 'as') a1,-- dasfgZXZcLTRIM('asdfgZXZc', 'asg') a2 -- dasfgZXZcFROM dual;

14.查找字符串位置INSTR

INSTR(参数1, 参数2 ,参数3,参数4)   返回的是字符在字符串出现的位置

参数1 在哪个字符串中查找

参数2 查找的是哪一个字符

参数3 从哪一个位置开始找

参数4 第几次出现

--返回的位置是从左边第一个位置开始数的

--第三个参数是正数的时候，就是从左往右找

--第三个参数是负数的时候，就是从右往左找

SELECT INSTR('aasdaf23', 'a', 1, 3) a1, -- 5INSTR('aasdf23', '3') a2, -- 7INSTR('aasdf23', 'a', 4, 1) a3, -- 0INSTR('aasdf23', 'a', -1, 2) a4 -- 1FROM dual;

15.截取字符串SUBSTR

SUBSTR(参数1，参数2，参数3)

注意：截取都是从左往右截取

参数1 从哪个字符串中截

参数2 从这个字符串的哪个位置截取，如果参数2为负数，就是从倒数第几个位置开始截取

参数3 截取的长度，如果参数3 为空，就默认截到末尾

SELECT SUBSTR('1234567', 1, 1) a1, -- 1SUBSTR('1234567', 1, 4) a2, -- 1234SUBSTR('1234567', -1, 1) a3, -- 7SUBSTR('12345678', -2, 4) a4, -- 78SUBSTR('1234567', -3, 4) a5, -- 567SUBSTR('1234567', -3) a6 -- 567FROM dual;

16.替换REPLACE(字符串,旧的值,新的值)

SELECT REPLACE('asdfgdh', 'd', 'x') a, -- asxfgxhREPLACE('asdfgdh', 'd', 'x1') a, -- asx1fgx1hREPLACE('asdfgdfhdhf', 'df', 'x') a, -- asxgxhdhfREPLACE('asdfg   dfhdhf', ' ') a -- asdfgdfhdhfFROM dual;

练习：

1.在 EMP中找出名字的首字母是 A 的员工的名字

select ENAME from emp where substr(ename, 1, 1) = 'A';
SELECT ename FROM EMP WHERE INSTR(ename, 'A') = 1;

2.在 EMP 表中找出名字的最后一个字符是 S 的员工的名字

select ENAME from emp where substr(ename, -1, 1) = 'S';
SELECT ename FROM EMP WHERE SUBSTR(ename, LENGTH(ename), 1) = 'S';

3.保证EMP表的员工姓名是6位长度，如果不够左边填充X

select lpad(ename, 6, 'X') from emp;

4.将 '2020-1-3'转换成'2020-01-03'

select to_date('2020-1-3', 'YYYY-MM-DD') A,TO_CHAR(to_date('2020-1-3', 'YYYY-MM-DD'), 'YYYY-MM-DD') Bfrom dual;
select replace('2020-1-3','-','-0') from dual;