Hi SANNAHA

A Link is a Promise.

0%

Oracle常用函数

发表于 更新于

Oracle中常用的函数。

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

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

单行函数

单行函数为查询表或视图的每一行返回一个结果行。这些函数可以出现在SELECT列表，WHERE子句，START WITH和CONNECT 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