内置函数
1常用函数汇总
定义,封装好一段代码,会返回指定的结果
数值型函数
| 序号 | 数值型函数 | 描述 |
|---|---|---|
| 1 | abs(x) | 绝对值 |
| 2 | sign(x) | 正负值 |
| 3 | ceil(x) | 返回大于等于x的最小整数值 |
| 4 | floor(x) | 返回小于等于x的最大整数值 |
| 5 | power(x,y) | 返回x的y次幂 |
| 6 | log(x,y) | 返回以x为底的y的对数,即log(底,数值) |
| 7 | mod(x,y) | 取余 |
| 8 | round(x[y]) | 返回按精度y四舍五入后的值 |
| 9 | trunc(x[y]) | 返回按精度y截取后的值 |
| 10 | sqrt(x) | 返回x的平方根 |
| 11 | greatest | 获取集合或者表中多行中的最大值 |
| 12 | least | 获取集合或者表中多行中的最小值 |
| 字符型函数 |
| 序号 | 字符型函数 | 描述 |
|---|---|---|
| 1 | ascii(c) | 返回ascii码值 |
| 2 | chr(c) | 返回ascii码值对应的字符 |
| 3 | || | 拼接字符串 |
| 4 | concat(c1,c2) | 拼接字符串 |
| 5 | distinct | 去重 |
| 6 | initcap(c1) | 首字母大写 |
| 7 | lower(c1) | 全部转为小写 |
| 8 | upper(c1) | 全部转为大写 |
| 9 | instr(c1,c2,[ i [ j ]]) | 返回搜索字符串的指定位置 |
| 10 | length(c1) | 返回字符串的长度 |
| 11 | lpad(c1,n[,c2]) | 在字符串的左边填充字符串 |
| 12 | rpad(c1,n[,c2]) | 在字符串的右边填充字符串 |
| 13 | ltrim(c1[,c2]) | 删除左边字符串 |
| 14 | rtrim(c1[,c2]) | 删除右边字符串 |
| 15 | trim(c1 from c2) | 删除左边和右边出现的字符串 |
| 16 | substr(c1,n1[,n2]) | 截取字符串 |
| 17 | replace(c1,c2,c3) | 替换函数 |
| 18 | translate(c1,c2,c3) | 替换字符串 |
| 19 | nvl(c1,c2) | 若第一个参数为空,则返回第二个参数 |
| 20 | nvl2(c1,c2,c3) | 若第一个参数为空,则返回第三个参数,否则返回参数2 |
| 21 | decode(c1,k1,v1,k2,v2,...,v3) | c1等于k1返回v1,以此类推,否则返回v3 |
| 22 | regexp_substr | 字符串截取函数 |
日期函数
| 序号 | 日期函数 | 描述 |
|---|---|---|
| 1 | sysdate | 返回当前系统日期 |
| 2 | add_months(d1,n1) | 返回在原有日期上加n个月后的日期 |
| 3 | last_day(d1) | 返回d1所在月份最后一天的日期 |
| 4 | months_between(d1,d2) | 返回日期d1到日期d2之间的月数 |
| 5 | round(d1[,c1]) | 返回d1按c1四舍五入后期间的第一天日期 |
| 6 | trunc(d1[,c1]) | 返回d1在c1期间的第一天日期 |
| 7 | next_day(d1[,c1]) | 从特定日期得到未来第一个星期几的日期 |
| 8 | extract(c1 from d1) | 提取时间日期中的数据 |
| 9 | dbtimezone | 返回数据库时区 |
| 10 | sessiontimezone | 返回当前会话时区 |
| 11 | interval c1 set1 | 变动日期时间数值 |
转换函数
| 序号 | 转换函数 | 描述 |
|---|---|---|
| 1 | to_date | 将字符串转化为时间 |
| 2 | to_char | 将时间或者数字转化为字符串 |
| 3 | to_number | 将字符串转化为数字 |
数值型函数
1abs(x)绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
---------- ----------
100 100
2sign(x)正负值
SQL> select sign(100),sign(-100),sign(0) from dual;
SIGN(100) SIGN(-100) SIGN(0)
---------- ---------- ----------
1 -1 0
3ceil(x)返回大于等于x的最小整数值
SQL> select ceil(3.6),ceil(2.8+1.3),ceil(0),ceil(-3.6)
2 from dual;
CEIL(3.6) CEIL(2.8+1.3) CEIL(0) CEIL(-3.6)
---------- ------------- ---------- ----------
4 5 0 -3
4floor(x)返回小于等于x的最大整数值
SQL> select floor(3.6),floor(2.8+1.3),floor(0),floor(-3.6)
2 from dual;
FLOOR(3.6) FLOOR(2.8+1.3) FLOOR(0) FLOOR(-3.6)
---------- -------------- ---------- -----------
3 4 0 -4
5power(x,y)返回x的y次幂
SQL> select power(2.5,2),power(2.5,-2),power(2.5,0),power(10,2)
2 from dual;
POWER(2.5,2) POWER(2.5,-2) POWER(2.5,0) POWER(10,2)
------------ ------------- ------------ -----------
6.25 .16 1 100
6log(x,y)返回以x为底的y的对数,即log(底,数值)
SQL> select log(2.5,6.25),log(2.5,0.16),log(2.5,1),log(10,100)
2 from dual;
LOG(2.5,6.25) LOG(2.5,0.16) LOG(2.5,1) LOG(10,100)
------------- ------------- ---------- -----------
2 -2 0 2
7mod(x,y)取余
SQL> select mod(18,5),mod(25,4) from dual;
MOD(18,5) MOD(25,4)
---------- ----------
3 1
8round(x[y])返回按精度y四舍五入后的值
SQL> select round(5555.6666,2.6),
2 round(5555.6666,-2.6),
3 round(5555.6666,3),
4 round(5555.6666)
5 from dual;
ROUND(5555.6666,2.6) ROUND(5555.6666,-2.6) ROUND(5555.6666,3) ROUND(5555.6666)
------------- ----------- ------------- ---------
5555.67 5600 5555.667 5556
9trunc(x[y])返回按精度y截取后的值
SQL> select trunc(5555.6666,2.6),
2 trunc(5555.6666,-2.6),
3 trunc(5555.6666,3),
4 trunc(5555.6666)
5 from dual;
TRUNC(5555.6666,2.6) TRUNC(5555.6666,-2.6) TRUNC(5555.6666,3) TRUNC(5555.6666)
-------------------- --------------------- ------------------ ----------------
5555.66 5500 5555.666 5555
10sqrt(x)返回x的平方根
SQL> select sqrt(10),sqrt(25),sqrt(9)
2 from dual;
SQRT(10) SQRT(25) SQRT(9)
---------- ---------- ----------
3.16227766 5 3
11greatest 获取集合或者表中多行中的最大值
SQL> select greatest(9,10,5) from dual;
GREATEST(9,10,5)
----------------
10
SQL> select * from scs;
ID NAME C M E
----------- ----------- ---- ---- ----
1 张三 80 90 73
2 李四 82 60 93
3 王五 87 94 98
4 赵六 34 30 60
SQL> select name,c,m,e,greatest(c,m,e) from scs;
NAME C M E GREATEST(C,M,E)
------- ---- ---- ---- ---------------
张三 80 90 73 90
李四 82 60 93 93
王五 87 94 98 98
赵六 34 30 60 60
12least 获取集合或者表中多行中的最小值
SQL> select least(9,10,5) from dual;
LEAST(9,10,5)
-------------
5
SQL> select name,c,m,e,least(c,m,e) from scs;
NAME C M E LEAST(C,M,E)
------------- ---- ---- ---- ------------
张三 80 90 73 73
李四 82 60 93 60
王五 87 94 98 87
赵六 34 30 60 30
字符型函数
1ascii(c)返回ascii码值
SQL> select ascii('A'),ascii('a'),ascii('1')
2 from dual;
ASCII('A') ASCII('A') ASCII('1')
---------- ---------- ----------
65 97 49
2chr(c)返回ascii码值对应的字符
SQL> select chr(65),chr(97),chr(49)
2 from dual;
CH CH CH
-- -- --
A a 1
3||拼接字符串
SQL> select empno||ename
2 from emp;
EMPNO||ENAME
---------------
7777S_HH%GGH
7369SMITH
7499ALLEN
7521WARD
4concat(c1,c2)拼接字符串
SQL> select concat(empno,ename) from emp;
CONCAT(EMPNO,ENAME)
--------------------------------------------------------------------------------
7777S_HH%GGH
7369SMITH
7499ALLEN
7521WARD
7566JONES
5distinct去重
SQL> select distinct deptno from emp;
DEPTNO
----------
30
20
10
6initcap(c1)首字母大写
SQL> select initcap(ename) from emp;
INITCAP(ENAME)
--------------------
S_Hh%Ggh
Smith
Allen
Ward
Jones
7lower(c1)全部转为小写
SQL> select lower(ename) from emp;
LOWER(ENAME)
--------------------
s_hh%ggh
smith
allen
ward
jones
8upper(c1)全部转为大写
SQL> select upper(ename) from emp;
UPPER(ENAME)
--------------------
S_HH%GGH
SMITH
ALLEN
WARD
JONES
9instr(c1,c2,[ i [ j ]])返回搜索字符串的指定位置
- I , 搜索的开始位置,默认为1
- J , 第J次出现的位置,默认为1
SQL> select instr('oracle rraning','ra') instr from dual;
INSTR
----------
2
SQL> select instr('oracle rraning','ra',1,2) instr from dual;
INSTR
----------
9
10length(c1)返回字符串的长度
SQL> select ename,length(ename)
2 from emp;
ENAME LENGTH(ENAME)
-------------------- -------------
S_HH%GGH 8
SMITH 5
ALLEN 5
WARD 4
JONES 5
11lpad(c1,n[,c2])在字符串的左边填充字符串
- c1 字符串
- n 追加后字符总长度
- c2 追加字符串,默认为空格
----- - 如果c1长度大于n,则返回c1左边n个字符
- 如果c1长度小于n,且c2和c1连接后大于n,则返回连接后边n个字符
SQL> select lpad(ename,10,'*?') one,
2 lpad(ename,5,'*?') two,
3 lpad(ename,10,'!@#$%^&*()_+') three
4 from emp;
ONE TWO THREE
*?S_HH%GGH S_HH% !@S_HH%GGH
*?*?*SMITH SMITH !@#$%SMITH
*?*?*ALLEN ALLEN !@#$%ALLEN
*?*?*?WARD *WARD !@#$%^WARD
*?*?*JONES JONES !@#$%JONES
12rpad(c1,n[,c2])在字符串的右边填充字符串
- 如果c1长度大于n,则返回c2左边n个字符
- 如果c1长度小于n,c1和c2连接后大于n ,则返回连接后的左边n个字符
- 如果c1长度小于n,c1和c2连接后大于n,则返回c1与多个重复的c2连接(总长度>=n)后的左边n个字符
在字符串'gao'的左边和右边分别加7个6
SQL> select lpad(rpad('gao',10,'6666666'),17,'6666666') text
2 from dual;
TEXT
----------------------------------
6666666gao6666666
SQL> select rpad(lpad('gao',10,'6666666'),17,'6666666') text
2 from dual;
TEXT
----------------------------------
6666666gao6666666
13ltrim(c1[,c2])删除左边字符串
SQL> select ltrim(' qing dao shi'),
2 length(ltrim(' qing dao shi')),
3 ltrim('qing dao shi','qing dao '),
4 length(ltrim('qing dao shi','qing dao '))
5 from dual;
------ -------------------------------------
qing dao shi 12 shi 3
14rtrim(c1[,c2])删除右边字符串
SQL> select rtrim('qing dao shi '),
2 length(rtrim('qing dao shi '))
3 from dual;
RTRIM('QINGDAOSHI') LENGTH(RTRIM('QINGDAOSHI'))
------------------------ ---------------------------
qing dao shi 12
15trim(c1 from c2)删除左边和右边出现的字符串
SQL> select trim(' qing dao shi '),
2 length(trim(' qing dao shi '))
3 from dual;
TRIM('QINGDAOSHI') LENGTH(TRIM('QINGDAOSHI'))
------------------------ --------------------------
qing dao shi 12
16substr(c1,n1[,n2])截取字符串
在字符表达式c1里,从n1开始截取n2个字符;若不指定n2则截取从第y个字符指导结束的字符
SQL> select substr('20191029',1,4),
2 substr('20191029',5)
3 from dual;
SUBSTR(' SUBSTR('
-------- --------
2019 1029
17replace(c1,c2,c3)替换函数
SQL> select replace('i love you','i','he'),
2 replace('i love you','i ')
3 from dual;
REPLACE('ILOVEYOU','I' REPLACE('ILOVEYO
---------------------- ----------------
he love you love you
18translate(c1,c2,c3)替换字符串
- 如果c3长度大于c2,则c3后面长处的字符无效
- 如果c3长度小于c2,则c2后面长处的字符均替换为空(删除)
- 如果c3长度为0,则返回空字符串
- 如果c2里字符重复,按首次位置为替换依据
SQL> select translate('he love you','he','i'),
2 translate('he love you','o','i')
3 from dual;
TRANSLATE('HELOVEY TRANSLATE('HELOVEYOU',
------------------ ----------------------
i lov you he live yiu
19nvl(c1,c2)若第一个参数为空,则返回第二个参数
- 2个参数,若第一个参数为空,则返回第二个参数
SQL> select empno,
2 comm,
3 nvl(a.comm,1)
4 from emp a where a.empno in(7499,7369);
EMPNO COMM NVL(A.COMM,1)
---------- ---------- -------------
7369 1
7499 300 300
20nvl2(c1,c2,c3)若第一个参数为空,则返回第三个参数,否则返回参数2
- 3个参数,若第一个参数为空,则返回参数3,否则返回参数2
SQL> select empno,
2 comm,
3 nvl2(a.comm,1,0)
4 from emp a where a.empno in(7499,7369);
EMPNO COMM NVL2(A.COMM,1,0)
---------- ---------- ----------------
7369 0
7499 300 1
21decode(c1,k1,v1,k2,v2,...,v3)c1等于k1返回v1,以此类推,否则返回v3
- 多个参数,成对出现.若参数1的值等于参数2，则返回参数3,若参数1的值等于参数4,则返回参数5,以此类推
SQL> select sal, decode(a.sal, 800, 1, 950, 2, 1100, 3, 1250, 4, 1300, 5, 6)
2 from emp a
3 where a.sal > 0
4 order by a.sal asc;
SAL DECODE(A.SAL,800,1,950,2,1100,3,1250,4,1300,5,6)
---------- ------------------------------------------------
800 1
950 2
1100 3
1250 4
1250 4
1300 5
1500 6
1600 6
22regexp_substr()：字符串截取函数
语法
regexp_substr(字符串,正则表达式,起始位置,第几个)
select regexp_substr('HELL,OQEE,EQ,QEQM','[^,]+',1,1) from dual;
输出
HELL
select regexp_substr('HELL,OQEE,EQ,QEQM','[^,]+',1,3) from dual;
输出
EQ
日期函数
1sysdate返回当前系统日期
SQL> select sysdate time
2 from dual;
TIME
--------------
11-11月-19
2add_months(d1,n1)返回在原有日期上加n个月后的日期
SQL> select sysdate,
2 add_months(sysdate,2),
3 add_months(sysdate,-2)
4 from dual;
SYSDATE ADD_MONTHS(SYS ADD_MONTHS(SYS
-------------- -------------- --------------
11-11月-19 11-1月 -20 11-9月 -19
3last_day(d1)返回d1所在月份最后一天的日期
SQL> select sysdate,
2 last_day(sysdate)
3 from dual;
SYSDATE LAST_DAY(SYSDA
-------------- --------------
11-11月-19 30-11月-19
4months_between(d1,d2)返回日期d1到日期d2之间的月数
SQL> select sysdate,
2 months_between(sysdate,to_date('2019-01-01','YYYY-MM-DD')) d2,
3 months_between(sysdate,to_date('2020-01-01','YYYY-MM-DD')) d3
4 from dual;
SYSDATE D2 D3
-------------- ---------- ----------
11-11月-19 10.349319 -1.650681
5round(d1[,c1])返回d1按c1四舍五入后期间的第一天日期
c1对应的参数表
最近0点日期 参数c1或j
最近的星期日 day或者dy或者d
最近月初日期 month或者mon或者mm或者rm
最近季日期 q
最近年初日期 syear或者year或者yyyy或者yyy或者yy或者y(多个y表示精度)
最近世纪初日期 cc或者scc
SQL> select sysdate 当前日期,
2 round(sysdate,'syear') 最近年初日期,
3 round(sysdate,'q') 最近季度日期,
4 round(sysdate,'month') 最近月初,
5 round(sysdate) 最近0点
6 from dual;
当前日期 最近年初日期 最近季度日期 最近月初 最近0点
-------------- -------------- -------------- -------------- --------------
11-11月-19 01-1月 -20 01-10月-19 01-11月-19 12-11月-19
6trunc(d1[,c1])返回d1在c1期间的第一天日期
SQL> select sysdate 当前日期,
2 trunc(sysdate) 今天日期,
3 trunc(sysdate,'day') 本周日,
4 trunc(sysdate,'month') 本月初,
5 trunc(sysdate,'q') 本季初,
6 trunc(sysdate,'year') 本年初日期
7 from dual;
当前日期 今天日期 本周日 本月初 本季初 本年初日期
-------------- -------------- -------------- -------------- -------------- --------------
11-11月-19 11-11月-19 10-11月-19 01-11月-19 01-10月-19 01-1月 -19
7next_day(d1[,c1])从特定日期得到未来第一个星期几的日期
SQL> select sysdate 当前日期,
2 next_day(sysdate,'星期一') 未来第一个星期一,
3 next_day(sysdate,'星期二') 未来第一个星期二,
4 next_day(sysdate,'星期三') 未来第一个星期三,
5 next_day(sysdate,'星期四') 未来第一个星期四,
6 next_day(sysdate,'星期五') 未来第一个星期五,
7 next_day(sysdate,'星期六') 未来第一个星期六,
8 next_day(sysdate,'星期日') 未来第一个星期日
9 from dual;
当前日期 未来第一个星期 未来第一个星期 未来第一个星期
-------------- -------------- -------------- --------------
11-11月-19 18-11月-19 12-11月-19 13-11月-19
未来第一个星期 未来第一个星期 未来第一个星期 未来第一个星期
-------------- -------------- -------------- --------------
14-11月-19 15-11月-19 16-11月-19 17-11月-19
8extract(c1 from d1)提取时间日期中的数据
SQL> select
2 extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,
3 extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,
4 extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,
5 extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,
6 extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,
7 extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年
8 from dual;
小时 分钟 秒 日 月 年
---------- ---------- ---------- ---------- ---------- ----------
2 38 40 16 2 2001
SQL> select sysdate 当前日期,
2 extract(DAY from sysdate ) 日,
3 extract(MONTH from sysdate ) 月,
4 extract(YEAR from sysdate ) 年
5 from dual;
当前日期 日 月 年
-------------- ---------- ---------- ----------
11-11月-19 11 11 2019
9dbtimezone返回数据库时区
SQL> select dbtimezone
2 from dual;
DBTIMEZONE
------------
+00:00
10sessiontimezone返回当前会话时区
SQL> select dbtimezone,
2 sessiontimezone
3 from dual;
DBTIMEZONE SESSIONTIMEZONE
------------ ------------
+00:00 +08:00
11interval c1 set1变动日期时间数值
SQL> select trunc(sysdate)+(interval '1' second) 加1秒,
2 trunc(sysdate)+(interval '1' minute) 加1分钟,
3 trunc(sysdate)+(interval '1' hour) 加1小时,
4 trunc(sysdate)+(INTERVAL '1' DAY) 加1天,
5 trunc(sysdate)+(INTERVAL '1' MONTH) 加1月,
6 trunc(sysdate)+(INTERVAL '1' YEAR) 加1年,
7 trunc(sysdate)+(interval '01:02:03' hour to second) 加指定小时到秒,
8 trunc(sysdate)+(interval '01:02' minute to second) 加指定分钟到秒,
9 trunc(sysdate)+(interval '01:02' hour to minute) 加指定小时到分钟,
10 trunc(sysdate)+(interval '2 01:02' day to minute) 加指定天数到分钟
11 from dual;
加1秒 加1分钟 加1小时 加1天 加1月 加1年
11-11月-19 11-11月-19 11-11月-19 12-11月-19 11-12月-19 11-11月-20
加指定小时到秒 加指定分钟到秒 加指定小时到分 加指定天数到分
11-11月-19 11-11月-19 11-11月-19 13-11月-19
转换函数
to_date将字符串转化为时间
SQL> select to_date('20191110','YYYYMMDD') from dual;
TO_DATE('20191
--------------
10-11月-19
to_char将时间或者数字转化为字符串
SQL> select to_char(sysdate,'YYYYMMDD') from dual;
TO_CHAR(SYSDATE,
----------------
20191111
to_number将字符串转化为数字
SQL> select to_number('123') from dual;
TO_NUMBER('123')
----------------
123
