ORACLE常用函数功能演示
ORACLE内置函数,相当于C语言中的库函数,提供了常规数据库编程中所需的绝大多数基本功能,并且通过函数的组合或嵌套使用,可以发挥其强大的功能。由于这些内置函数都是由ORACLE公司的编程大家所写,而且其执行代码是内嵌在ORACLE数据库系统中的,因此其执行效率极高。
在数据库编程中能广泛使用ORACLE所提供的内置函数,将大大提高程序代码的执行效率。“站在高人的肩膀上,将使你变得更高”。
一、 单行函数
1. 数值函数
(1) 三角函数
① SIN、ASIN、SINH(正弦、反正弦、双曲正弦)
② COS、ACOS、COSH(余弦、反余弦、双曲余弦)
③ TAN、ATAN、TANH(正切、反正切,双曲正切)
(2) 幂、方根及对数运算函数
① POWER(m, n)(取m的n次幂)
② SQRT(n)(取n的平方根)
③ EXP(n)(取自然对数底e的n次方根)
④ LN(n)(取n的自然对数)
⑤ LOG(m, n)(取以m为底n的对数)
以上函数功能演示如下:
SQL> select power(2,3),power(2,-3) from dual;
8 0.125
SQL> select SQRT(9) from dual;
3
SQL> select EXP(1),LN(1),LOG(2,4)FROM DUAL;
2.71828182845905 0 2
(3) 数值处理函数
① ABS(n)(取绝对值)
② SIGN(n)(符号函数)
③ CEIL(n)(取不小于n的最小整数)
④ FLOOR(n)(取不大于n的最大整数)
⑤ ROUND(n, [m])(按m精度对n进行4舍5入)
⑥ TRUNC(n, [m])(按m精度对n进行截取)
⑦ MOD(m, n)(取m除以n的余数)
以上函数功能演示如下:
SQL> select ABS(3-5),ABS(5-3),ABS(0)FROM DUAL;
ABS(3-5) ABS(5-3) ABS(0)
---------- ---------- ----------
2 2 0
SQL> select SIGN(-5),SIGN(0),SIGN(5/3)FROM DUAL;
SIGN(-5) SIGN(0) SIGN(5/3)
---------- ---------- ----------
-1 0 1
SQL> select ceil(9.6),ceil(-9.6) FROM DUAL;
CEIL(9.6) CEIL(-9.6)
---------- ----------
10 -9
SQL> select floor(9.6),floor(-9.6) FROM DUAL;
FLOOR(9.6) FLOOR(-9.6)
---------- -----------
9 -10
SQL> select round(355/113,4),round(355/113,2),round(355/113,0) FROM DUAL;
ROUND(355/113,4) ROUND(355/113,2) ROUND(355/113,0)
---------------- ---------------- ----------------
3.1416 3.14 3
SQL> select ceil(9.6),ceil(-9.6) FROM DUAL
CEIL(9.6) CEIL(-9.6)
---------- ----------
10 -9
SQL> select floor(9.6),floor(-9.6) FROM DUAL;
FLOOR(9.6) FLOOR(-9.6)
---------- -----------
9 -10
SQL> select round(355/113,4),round(355/113,2),round(355/113,0) FROM DUAL;
ROUND(355/113,4) ROUND(355/113,2) ROUND(355/113,0)
---------------- ---------------- ----------------
3.1416 3.14 3
SQL> select ceil(9.6),ceil(-9.6) FROM DUAL;
CEIL(9.6) CEIL(-9.6)
---------- ----------
10 -9
SQL> select floor(9.6),floor(-9.6) FROM DUAL;
FLOOR(9.6) FLOOR(-9.6)
---------- -----------
9 -10
SQL> select round(355/113,4),round(355/113,2),round(355/113,0) FROM DUAL;
ROUND(355/113,4) ROUND(355/113,2) ROUND(355/113,0)
---------------- ---------------- ----------------
3.1416 3.14 3
SQL> select ceil(9.6),ceil(-9.6) FROM DUAL;
CEIL(9.6) CEIL(-9.6)
---------- ----------
10 -9
SQL> select floor(9.6),floor(-9.6) FROM DUAL;
FLOOR(9.6) FLOOR(-9.6)
---------- -----------
9 -10
SQL> select round(355/113,4),round(355/113,2),round(355/113,0) FROM DUAL;
ROUND(355/113,4) ROUND(355/113,2) ROUND(355/113,0)
---------------- ---------------- ----------------
3.1416 3.14 3
SQL> select ceil(9.6),ceil(-9.6) FROM DUAL;
CEIL(9.6) CEIL(-9.6)
---------- ----------
10 -9
SQL> select floor(9.6),floor(-9.6) FROM DUAL;
FLOOR(9.6) FLOOR(-9.6)
---------- -----------
9 -10
SQL> select round(355/113,4),round(355/113,2),round(355/113,0) FROM DUAL;
ROUND(355/113,4) ROUND(355/113,2) ROUND(355/113,0)
---------------- ---------------- ----------------
3.1416 3.14 3
SQL> select mod(355,133) from dual;
89
2. 字符处理函数
(1) 对字符串的大小写处理的函数
① INITCAP(功能:将字符串中每个单词的首字母,变换为大写。)
② UPPER(功能:将字符串中的所有字母,转换为大写。)
③ LOWER(功能:将字符串中的所有字母,转换为小写。)
以上函数功能演示如下
SQL> select INITCAP('hello world!') from DUAL;
Hello World!
SQL> select UPPER('hello world!') from DUAL;
HELLO WORLD!
SQL> select LOWER('hello world!') from DUAL;
hello world!
(2) 对字符串进行处理的函数
① CONCAT(字符串连接函数,也可用 || 替代)
功能演示如下:
SQL> select CONCAT('HELLO ', 'WORLD') from DUAL;
HELLO WORLD
SQL> select 'HELLO ' || 'WORLD' from DUAL;
HELLO WORLD
② ASCII(取字符的编码)
③ CHR(将编码转换为对应的字符)
以上函数功能演示如下:
SQL> select ascii(12),ascii('a'),ascii('&'),ascii('人') from dual;
49 97 38 51403
SQL> select chr(49),chr(97),chr(38),chr(51403) from dual;
1 a & 人
④ LTRIM(去除左侧空格/指定字符)
⑤ RTRIM(去除右侧空格/指定字符)
⑥ TRIM(去除两侧的空格/去除指定字符)
以上函数去除空格功能演示如下:
SQL> select '"'||LTRIM(' I am a girl. ')||'"' from dual;
"I am a girl. "
SQL> select '"'||RTRIM(' I am a girl. ')||'"' from dual;
" I am a girl."
SQL> select '"'||TRIM(' I am a girl. ')||'"' from dual;
"I am a girl."
以上函数去除指定字符功能演示如下:
SQL> select '"'||LTRIM('-I am a girl-','-')||'"' from dual;
"I am a girl-"
SQL> select '"'||RTRIM('-I am a girl-','-')||'"' from dual;
"-I am a girl"
SQL> select '"'||TRIM('-'from'-I am a girl-')||'"' from dual;
"I am a girl"
请注意:
1.在用TRIM去除字符串中指定字符(非空格)时的特殊用法。
2.ltrim(x,y) 函数是按照y中的字符一个一个截掉x中的字符,并且是从左边开始执行的,只要遇到y中有的字符, x中的字符都会被截掉, 直到在x的字符中遇到y中没有的字符为止函数命令才结束 .
⑦ LPAD(左侧添充空格/指定字符)
⑧ RPAD(右侧添充空格/指定字符)
以上函数功能演示如下:
SQL> select '"' ||LPAD('Hello',10)||'"' from dual;
" Hello"
SQL> select '"' ||RPAD('Hello',10)||'"' from dual;
"Hello "
SQL> select '"' ||LPAD('Hello',10,'*')||'"' from dual;
"*****Hello"
SQL> select '"' ||RPAD('Hello',10,'*')||'"' from dual;
"Hello*****"
⑨ LENGTH(取字符串的长度)
⑩ SUBSTR(截取子字符串)
用法格式:SUBSTR(string, start [,length])
功能说明:从字符串string的start位置开始向后截取length长度的子串
以上函数功能演示如下:
SQL> select length('HelloWorld') from dual;
LENGTH('HELLOWORLD')
--------------------
10
SQL> select substr('HelloWorld',1,5) from dual;
SUBSTR('HELLOWORLD',1,5)
------------------------
Hello
⑾ INSTR(查找子字符串的位置)
用法格式:INSTR(string, search_string [,n [,m]])
功能说明:从字符串string的第n个字符开始查找search_string第m次出现的位置,如果按条件没有找到字符串,则返回0
(注意:此函数返回的位置是以原字符串首字符所在位置为起始点的,首字符位置为1)
函数功能演示如下:
--查字符a第一次出现的位置
SQL> select INSTR('I am a student,and I like study.','a') from dual;
------------------------------
3
--查字符a第二次出现的位置
SQL> select INSTR('I am a student,and I like study.','a',1,2) from dual;
------------------------------
6
--从字符串第5个字符位置开始查找第一次出现a的位置
SQL> select INSTR('I am a student,and I like study.','a',5) from dual;
------------------------------
6
--从字符串第5个字符位置开始查找第二次出现a的位置
SQL> select INSTR('I am a student, and I like study.','a',5,2) from dual;
------------------------------
17
--从后向前查第一次出现字符a的位置(即:最后一次出现字符a的位置)
SQL> select INSTR('I am a student, and I like study.','a',-1) from dual;
------------------------------
17
从后向前查第二次出现字符a的位置
SQL> select INSTR('I am a student, and I like study.','a',-1,2) from dual;
------------------------------
6
--查找字符串是否存在字符w
SQL> select INSTR('I am a student, and I like study.','w') from dual;
------------------------------
0
⑿ REPLACE(子串替换)
用法格式:REPLACE(string, search_string [,replace_string])
功能说明:将字符串string中的所有search_string都替换为replace_string,如果省略replace_string,则将字符串string中的所有search_string都去掉。
⒀ TRANSLATE(字符转换)
用法格式:TRANSLATE(string, search_set, replace_set)
功能说明:将string中出现在search_set中的字符转换为replace_set相应位置的字符。
以上函数功能演示如下:
SQL> select replace('I am a student, and I like study.','student','girl') from dual;
REPLACE('IAMASTUDENT,ANDILIKES
------------------------------
I am a girl, and I like study.
SQL> select replace('I am a student, and I like study.','and') from dual;
REPLACE('IAMASTUDENT,ANDILIKES
------------------------------
I am a student, I like study.
SQL> select translate('eeegg','eg','bc') from dual;
TRANSLATE('EEEGG','EG','BC')
----------------------------
bbbcc
3. 日期函数
① SYSDATE(取当前的数据库系统时间)
② ADD_MONTHS(加减指定的月份)
③ MONTHS_BETWEEN(取两个日期之间相隔的月数)
④ LAST_DAY(取指定日期所在月的最后一天)
以上函数功能演示如下:
--直接调用sysdate函数,得到当前数据库系统的时间
SQL> select sysdate from dual;
SYSDATE
-----------
2021/6/3 17
--由于这种环境的默认时间显示格式,不符合我们的习惯,如下方法:
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2021-06-03 17:03:44
--也可以采用更改当前会话(session)的方式,来统一设置当前会话的日期显示格式
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered
SQL> select SYSDATE from dual;
SYSDATE
-----------
2021/6/3 17
--显示后一个月的时间
SQL> select ADD_MONTHS(SYSDATE,1) from dual;
ADD_MONTHS(SYSDATE,1)
---------------------
2021/7/3 17:09:15
--显示前一个月的时间
SQL> select ADD_MONTHS(SYSDATE,-1) from dual;
ADD_MONTHS(SYSDATE,-1)
----------------------
2021/5/3 17:10:02
--显示后一个月和前一个月之间的月数
SQL> select MONTHS_BETWEEN(ADD_MONTHS(SYSDATE,1),ADD_MONTHS(SYSDATE,-1)) from dual;
MONTHS_BETWEEN(ADD_MONTHS(SYSD
------------------------------
2
--显示本月的最后一天的时间
SQL> select LAST_DAY(SYSDATE) from dual;
LAST_DAY(SYSDATE)
-----------------
2021/6/30 17:14:5
⑤ ROUND(date, ‘format_string’)
功能:按日期掩码format_string的格式对给定的日期date进行舍/入。
⑥ TRUNC(date, ‘format_string’)
功能:按日期掩码format_string的格式对给定的日期date进行截取。
以上函数功能演示如下:
SQL> select sysdate from dual;
SYSDATE
-----------
2021/7/1 14
SQL> select round(sysdate,'YYYY'),round(sysdate,'MM'),round(sysdate,'DD')from dual;
ROUND(SYSDATE,'YYYY') ROUND(SYSDATE,'MM') ROUND(SYSDATE,'DD')
--------------------- ------------------- -------------------
2022/1/1 2021/7/1 2021/7/2
SQL> select trunc(sysdate,'YYYY'),trunc(sysdate,'MM'),trunc(sysdate,'DD')from dual;
TRUNC(SYSDATE,'YYYY') TRUNC(SYSDATE,'MM') TRUNC(SYSDATE,'DD')
--------------------- ------------------- -------------------
2021/1/1 2021/7/1 2021/7/1
4. 类型转换函数
功能:在数值和字符,以及字符和日期之间进行数据类型的转换
说明:在ORACLE数据服务器,可以对数值形式的字符串隐式地转换为数值类型的数据,也可以将数值类型的数据隐式地转换为数值形式的字符串。但也可以进行如下的显式转换。
① TO_CHAR(n)(将数值n转换为字符类型)
② TO_NUMBER(‘string’)(将字符串string转换为数值类型)
知识扩展:常用进制的转换```
以上函数功能演示如下:
--将十进制转换为十六进制
SQL> select to_char(65535,'xxxx'),to_char(65535,'xxxx') from dual;
TO_CHAR(65535,'XXXX') TO_CHAR(65535,'XXXX')
--------------------- ---------------------
ffff ffff
--将十六进制转化为十进制
SQL> select to_number('f','x'),to_number('ffff','xxxx') from dual;
TO_NUMBER('F','X') TO_NUMBER('FFFF','XXXX')
------------------ ------------------------
15 65535
--将二进制转化为十进制
SQL> select BIN_TO_NUM(1,0),BIN_TO_NUM(1,0,1,0) from dual;
BIN_TO_NUM(1,0) BIN_TO_NUM(1,0,1,0)
--------------- -------------------
2 10
③ TO_CHAR(date, ‘format_string’)(按照时间掩码format_string的格式要求,将日期型数据date转换为字符型数据)
④ TO_DATE(‘string’, ‘format_string’)(将字符型数据string按照时间掩码format_string的格式要求,转换为相应的日期型数据)
常用的时间格式掩码如下:
掩码元素 含义
YYYY 四位数年份 (如:2005)
YY 二位数年份(如 05)
Q 季度(1-4)
MM 月份(01-12)
WW 年的星期数(1-53),其中第一星期为年的第一天至第七天
W 月的星期数(1-5),其中第一星期为月的第一天至第七天
DDD 年的日(1-366)
DD 月的日(1-31)
D 周的日(1-7),其中周日为1,周六为7
HH24 24小时制(0-23)
MI 分钟(0-59)
SS 秒(0-59)
SSSSS 自午夜之后的秒(0-86399)
以上函数功能演示如下:
--查看当前日期
SQL> select sysdate from dual;
SYSDATE
-----------
2021/7/1 15
--查看当前日期的年份,月份,日期,小时,分钟
SQL> select To_char(sysdate,'YYYY')year,To_char(sysdate,'MM')month,To_char(sysdate,'DD')day,To_char(sysdate,'HH')Hour,To_char(sysdate,'MI')Minute,To_char(sysdate,'SS')seconds from dual;
YEAR MONTH DAY HOUR MINUTE SECONDS
---- ----- --- ---- ------ -------
2021 07 01 03 47 38
--查看当前日期是在本年的第几个季度
SQL> select to_char(sysdate,'Q')from dual;
TO_CHAR(SYSDATE,'Q')
--------------------
3
--查看当前日期是在本年的第几个星期,在本月的第几周
SQL> select to_char(sysdate,'WW'),to_char(sysdate,'W') from dual;
TO_CHAR(SYSDATE,'WW') TO_CHAR(SYSDATE,'W')
--------------------- --------------------
26 1
--查看当前日期是在本年的第几天,在本周的第几天(周日为1,周六为7)
SQL> select to_char(sysdate,'DDD'),to_char(sysdate,'D') from dual;
TO_CHAR(SYSDATE,'DDD') TO_CHAR(SYSDATE,'D')
---------------------- --------------------
182 5
5. 空值转换函数
① NVL(expr1, expr2)
功能:如果 expr1 的计算结果为 null 值,则返回 expr2 的值。如果 expr1 的计算结果不是 null 值,则返回 expr1 的值
② NVL2(expr1, expr2, expr3)
功能:如果expr1非空,则返回expr2的值;如果expr1为空,则返回expr3的值。
③ NULLIF(expr1, expr2)
功能:比较两个表达式,如果相等返回空;如果不相等,返回第一个表达式的值。
④ COALESCE(expr1, expr2, ..., exprn)
功能:返回表达式列表中的第一个非空表达式的值。
6. 查看环境参数函数
(1) USER函数 (返回当前用户名)
(2) USERENV函数
① USERENV('ISDBA') (查看当前用户是否是DBA)
② USERENV('LANGUAGE') (查看环境所用的字符集)
③ USERENV('TERMINAL') (查看当前用户终端的标识)
以上函数用法演示如下:
select USERENV('ISDBA'), USERENV('LANGUAGE'), USERENV('TERMINAL') from dual;
(3) SYS_CONTEXT函数
说明:SYS_CONTEXT函数是在ORACLE 8i中新增的,在功能上比USERENV函数更加全面。
用法格式:SYS_CONTEXT('USERENV', '<parameter>')
根据参数parameter来返回相应的信息
① 登录验证类型
select SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') from dual;
② 当前登录数据库的用户名
select SYS_CONTEXT('USERENV', 'CURRENT_USER') from dual;
③ 当前登录客户端的操作系统用户名
select SYS_CONTEXT('USERENV', 'OS_USER') from dual;
④ 当前所在客户机名(包括所在工作组名)
select SYS_CONTEXT('USERENV', 'HOST') from dual;
⑤ 当前所在客户机/终端名
select SYS_CONTEXT('USERENV', 'TERMINAL') from dual;
⑥ 当前所在客户端的IP
select SYS_CONTEXT('USERENV', 'IP_ADDRESS') from dual;
⑦ 当前是否为DBA帐户
select SYS_CONTEXT('USERENV', 'ISDBA') from dual;
⑧ 当前的字符集
select SYS_CONTEXT('USERENV', 'LANGUAGE') from dual;
⑨ 当前所使用的网络协议
select SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') from dual;
⑩ 当前所使用的日历
select SYS_CONTEXT('USERENV', 'NLS_CALENDAR') from dual;
⑾ 当前的货币单位
select SYS_CONTEXT('USERENV', 'NLS_CURRENCY') from dual;
⑿ 当前的日期格式
select SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') from dual;
⒀ 当前的日期语言
select SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') from dual;
⒁ 当前所在的地域
select SYS_CONTEXT('USERENV', 'NLS_TERRITORY') from dual;
⒂ 当前代理用户
select SYS_CONTEXT('USERENV', 'PROXY_USER') from dual;
7. DECODE函数
DECODE函数用法格式与说明:
格式:
DECODE(col|expression,
search1, result1
[, search2, result2 ]
[, search3, result3 ]
[, ......, ...... ]
[, default]
)
说明:
第一个参数可以是表的列,也可以是一个表达式。
如果第一参数的值与search1相等,则函数返回result1的值;
如果第一参数的值与search2相等,则函数返回result2的值;以此类推。
如果第一参数的值与所有的search都不相等,则函数返回default的值。
此函数用法示例如下:
--对员工的月薪按每1000元为一个梯度进行分档
SQL> select ename,sal,
2 decode(Trunc(sal/1000,0),
3 0,1,
4 1,2,
5 2,3,
6 3,4,
7 4,5,
8 5,6,
9 6,7,
10 8 )grade
11 from emp order by grade;
ENAME SAL GRADE
---------- --------- ----------
SMITH 800.00 1
JAMES 950.00 1
ADAMS 1100.00 2
TURNER 1500.00 2
WARD 1250.00 2
MILLER 1300.00 2
MARTIN 1250.00 2
ALLEN 1600.00 2
CLARK 2450.00 3
BLAKE 2850.00 3
JONES 2975.00 3
FORD 3000.00 4
SCOTT 3000.00 4
YIF 3000.00 4
ARVIN 3000.00 4
AMEN 3000.00 4
JACK 3000.00 4
KING 5000.00 6
18 rows selected
二、 聚组函数
功能:对一组数据进行运算。
函数 功能描述
MAX 最大值
MIN 最小值
AVG 平均值
COUNT 数量值
SUM 总计值
SQL> select MAX(sal),MIN(sal),AVG(sal),Count(*),SUM(sal) from emp;
MAX(SAL) MIN(SAL) AVG(SAL) COUNT(*) SUM(SAL)
---------- ---------- ---------- ---------- ----------
5000 800 2279.16666 18 41025
以上函数功能演示如下:
三、 分析函数
分析函数简介:ORACLE从8.1.6版开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚组函数的不同之处是对于每个组返回多行值,而聚组函数对于每个组只返回一行值。
(ORACLE有一整套分析函数,功能极其强大,用法也非常灵活,关于这一块的功能演示,请待续……)