0%

Oracle常用函数

Oracle中常用的函数。


Oracle SQL 提供了用于执行特定操作的专用函数。这些函数大大增强了 SQL 语言的功能。函数可以接受零个或者多个输入参数,并返回一个输出结果。 Oracle 数据库中主要使用两种类型的函数:

  1. 单行函数:对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果,如 MOD(x,y)。常用的单行函数有:
  • 字符函数:对字符串操作。
  • 数字函数:对数字进行计算,返回一个数字。
  • 转换函数:可以将一种数据类型转换为另外一种数据类型。
  • 日期函数:对日期和时间进行处理。
  1. 聚合函数:聚合函数同时可以对多行数据进行操作,并返回一个结果,如 SUM(x)

单行函数

单行函数为查询表或视图的每一行返回一个结果行。这些函数可以出现在SELECT列表,WHERE子句,START WITHCONNECT BY子句以及HAVING子句中。

数值函数

数值函数(Numeric Functions)接受数值参数并返回数值。

函数 说明 示例
ABS(x) x的绝对值 ABS(-3)=3
SIN(x)/ASIN(x)/SINH(x)/… x的正弦、反正弦、双曲正弦值、…… SIN(0)=0
CEIL(x) 大于或等于x的最小整数 CEIL(2.33)=3
FLOOR(x) 小于或等于x的最大整数 FLOOR(2.51)=2
MOD(x,y) x除以y的余数。如果y为0,返回x MOD(8,3)=2
LOG(x,y) 以x为底,y的对数 LOG(2,4)=2
POWER(x,y) x的y次幂。如果x为负数,y需要为整数 POWER(2,3)=8
SQRT(x) x的平方根 SQRT(4)=2
ROUND(x[,y]) x四舍五入到y位小数*[注1]* ROUND(3.1415,3)=3.142
TRUNC(x[,y]) x截断到y位小数*[注2]* TRUNC(3.1415,3)=3.141

说明:

  1. ROUND(x[,y])

在缺省 y 时,默认 y=0。ROUND(77.7)=78。

y 是正整数,四舍五入到小数点右边 y 位。ROUND(3.1415,3)=3.142。

y 是负整数,四舍五入到小数点左边|y|位。ROUND(77.7,-1)=80,ROUND(77.7,-2)=100,ROUND(77.7,-3)=0。

  1. TRUNC(x[,y])

在缺省 y 时,默认 y=0。TRUNC (77.7)=77。

Y是正整数,截断到小数点后 y 位。TRUNC (3.1415,3)=3.141。

y 是负整数,截断到小数点左边|y|位。TRUNC (77.7,-1)=70,TRUNC (77.7,-2)=0。

字符函数

字符函数(Character Functions)接收字符参数并返回字符或数值。

返回字符的字符函数

函数 说明
ASCII(char) 返回字符char对应的ASCII码
CONCAT(str1,str2) 拼接两个字符串
INSTR(STR,str[,start[,n]]) 从字符串STR中查找str出现的位置,可以指定从第start位开始匹配,可以指定返回第n个匹配的结果
LENGTH(str) 返回字符串的长度(字符个数)
LENGTHB(str) 返回字符串的字节数(参数一律视为字符串)
VSIZE(expression) 返回表达式的字节数(参数保持原来的类型)
LOWER(STR) 将字符串STR转换成小写
UPPER(str) 将字符串str转换成大写
LTRIM(STR[,trim_str]) 截去字符串STR左边的trim_str,默认截去空格
RTRIM(STR[,trim_str]) 截去字符串STR右边的trim_str,默认截去空格
TRIM([trim_char FROM]STR) 截去字符串STR两边的字符trim_char,默认截去空格
REPLACE(STR,oldstr,newstr) STR中查找字符串oldstr并替换为newstr
SUBSTR(STR,start[,len]) 返回STR的子串,从第start位开始,截取len个字符,默认截取到结尾

上面各函数的例子:

示例 示例结果
SELECT ASCII(‘a’) FROM dual; 97
SELECT CONCAT(‘Hello’,’world’) FROM dual; Helloworld
SELECT INSTR(‘Hello world’,’or’) FROM dual; 8
SELECT LENGTH(12.3) FROM dual; 4
SELECT LENGTHB(12.3) FROM dual; 4
SELECT VSIZE(12.3) FROM dual; 3
SELECT LENGTH(‘hi你好’) FROM dual; 4
SELECT LENGTHB(‘hi你好’) FROM dual; 6
SELECT VSIZE(‘hi你好’) FROM dual; 6
SELECT LOWER(‘SANNAHA’) FROM dual; sannaha
SELECT UPPER(‘sannaha’) FROM dual; SANNAHA
SELECT LTRIM(‘hiSANNAHA’,’hi’) FROM dual; SANNAHA
SELECT RTRIM(‘SANNAHAhi’,’hi’) FROM dual; SANNAHA
SELECT TRIM(‘=’ FROM ‘=SANNAHA=’) FROM dual; SANNAHA
SELECT REPLACE(‘ABCDE’,’CD’,’AAA’)FROM dual; ABAAAE
SELECT SUBSTR(‘ABCDE’,2,3) FROM dual; BCD

返回数值的字符函数

日期函数

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

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

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

1
SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

1571801221274

  1. LAST_DAY(d):返回指定日期当月的最后一天。
1
SELECT SYSDATE,last_day(SYSDATE) FROM dual;

1571801198908

  1. ROUND(d[,fmt]):返回一个以 fmt 为格式的四舍五入日期值, d 是日期, fmt 是格式模型。默认 fmt 为 DDD,即月中的某一天。
  • 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日,即前半年舍去,后半年作为下一年。

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

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

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

1
2
SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),
ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

1571801160195

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

  1. 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;

1571801278333

转换函数

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

  1. TO_CHAR(d|n[,fmt]):把日期和数字转换为制定格式的字符串。Fmt是格式化字符串

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

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

1571802015592

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

参数 示例 说明
9 999 指定位置处显示数字
. 9.9 指定位置返回小数点
, 99,99 指定位置返回一个逗号
$ $999 数字开头返回一个美元符号
EEEE 9.99EEEE 科学计数法表示
L L999 数字前加一个本地货币符号
PR 999PR 如果数字是负数则用尖括号进行表示
1
SELECT TO_CHAR(123456.789,'$999,999.9PR')"positive",TO_CHAR(-123456.789,'L9.9EEEEPR')"negative" FROM dual;

1571809341112

  1. TO_DATE(X,[,fmt]):把一个字符串以fmt格式转换成一个日期类型
1
SELECT TO_DATE('2019/10/23 星期三 11:11:11','YYYY/MM/DD DAY HH24:MI:SS')"date" FROM dual;

1571809570460

  1. TO_NUMBER(X,[,fmt]):把一个字符串以fmt格式转换为一个数字
1
SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;

1571802534086

其它单行函数

  1. NVL(X,VALUE):如果X为空,返回value,否则返回X。

例:对工资是2000元以下的员工,如果没发奖金,每人奖金100元

1
SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;
  1. NVL2(x,value1,value2):如果x非空,返回value1,否则返回value2。

例:对EMP表中工资为2000元以下的员工,如果没有奖金,则奖金为200元,如果有奖金,则在原来的奖金基础上加100元

1
2
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(表达式)
1
2
3
4
5
--求本月所有员工的基本工资总和
SELECT SUM(SAL) FROM emp;

--求不同部门的平均工资(AVG函数下的分组查询)
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;  

分析函数

RATIO_TO_REPORT

1
2
RATIO_TO_REPORT(expr)
OVER ([ query_partition_clause ])

它计算一个值一组值之和的比率。如果expr为空,则返回值也为空。

一组值query_partition_clause决定,如果省略该子句,则与所有返回行。

例子:查询员工信息,包括某员工工资占所在部门总工资的百分比,以及占全体员工总工资的百分比。

1
2
3
4
5
6
7
select deptno,ename,sal,
sum(sal) over (partition by deptno order by sal,ename) cum_sal,
--根据deptno分区,计算与分区sal之和的比率
round(100*ratio_to_report(sal) over (partition by deptno),1) pct_dept,
--不分区,计算与全部sal之和的比率
round(100*ratio_to_report(sal) over (),1) pct_overall
from emp order by deptno,sal;

1575428961163


参考资料

Oracle常用函数详解
SQL Functions