Oracle 函数大全[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。

Oracle SQL 提供了用于执行特定操作的专用函数。主要使用单行函数和聚合函数两种类型的函数：

单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果：字符函数、数字函数、转换函数以及日期函数

字符函数：对字符串操作，接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。

ASCII(X)：返回字符X的ASCII码

SELECT ASCII('a') FROM dual;
--结果：97

CONCAT(X,Y)：连接字符串X和Y

SELECT CONCAT('1','2') FROM dual;
--结果：12

INSTR(X,STR[,START][,N)：从X中查找str，可以指定从start开始，也可以指定从n开始

SELECT INSTR('abcdefgh','de') FROM dual;
--结果：4

LENGTH(X)：返回X的长度

SELECT LENGTH('abcdefgh') FROM dual;
--结果：8

LOWER(X)：X转换成小写

SELECT LOWER('ABcdefgh') FROM dual;
--结果：abcdefgh

UPPER(X)：X转换成大写

SELECT UPPER('ABcdefgh') FROM dual;
--结果：ABCDEFGH

LTRIM(X[,TRIM_STR])：把X的左边截去trim_str字符串，缺省截去空格

SELECT LTRIM('=ABcdefgh=','=') FROM dual;
--结果：ABcdefgh=

RTRIM(X[,TRIM_STR])：把X的右边截去trim_str字符串，缺省截去空格

SELECT RTRIM('=ABcdefgh=','=') FROM dual;
--结果：=ABcdefgh

TRIM([TRIM_STR FROM]X)：把X的两边截去trim_str字符串，缺省截去空格

SELECT TRIM('='FROM'=ABcdefgh=') FROM dual;
--结果：ABcdefgh

REPLACE(X,old,new)：在X中查找old，并替换成new

SELECT REPLACE('ABCDE','CD','AAA') FROM dual;
--结果：ABAAAE

SUBSTR(X,start[,length])：返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾

SELECT SUBSTR('ABCDE','2','3') FROM dual;
--结果：BCD

INITCAP：返回字符单词首字母大写，其余小写，单词用空格和非字母字符分隔

select initcap('hEllo') name from dual;
--结果：Hello

LPAD/RPAD（string1,x[,string2]）：在string1字符左边或右边粘贴数个string2字符，直到字符总字节数达到x字节。string2默认为空格。

select lpad(rpad('111111',10,'*'),17,'*') NAME from dual;
--结果：*******111111****

REGEXP_REPLACE(str1,pattem[,str2[,pos[,occ[,par]]]])：用于按照特定正则表达式的规则替换字符串。其中参数str1指定源字符表达式，pattem指定正则表达式，str2指定替换字符串，pos指定起始搜索位置，occ指定替换出现的第几个字符串，par指定默认匹配操作的文本串。

--将某个字段值设置替换成空
select REGEXP_REPLACE (t.acceptordatajson,'"bankId":\d+','"bankId":""') from t_accept_log t where t.acceptordatajson like '%其它银行%'

REGEXP_SUBSTR(str1,pattem [,pos[,occ[,par]]])：用于按照特定表达式的规则返回字符串的子串。其中参数str1指定源字符表达式，pattem指定规则表达式， pos指定起始搜索位置，occ指定替换出现的第几个字符串，par指定默认匹配操作的文本串。

SELECT REGEXP_SUBSTR('http://www.baidu.com','http://([[:alnum:]]+\.?)') a from dual;
--结果：http://www.

TRANSLATE(string,from_str,to_str)：将字符string按照from_str与to_str的对应规则进行处理，返回将所出现的from_str中的每个字符替换为to_str中的相应字符以后的string. TRANSLATE是REPLACE所提供的功能的一个超集.如果from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string中被删除,因为它们没有相应的替换字符. to_str不能为空.Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是NULL.

SELECT TRANSLATE('2abc2234','01234abcde','99999XXXXX') tra from dual
--结果：9XXX9999

--查找字符串',01234,2342,2,'中逗号出现次数
select length(translate(',01234,2342,2,', 'a0123456789', ' ')) from dual;
--结果：4

数字函数：对数字进行计算，返回一个数字，接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。

ABS(X)：X的绝对值

SELECT ABS('-10') FROM dual;
--结果：10

ACOS(X)：X的反余弦

SELECT ACOS(1) FROM dual;
--结果：0

COS(X)：余弦

SELECT COS(1) FROM dual;
--结果：0.54030230586814

CEIL(X)：大于或等于X的最小值

SELECT CEIL(4.9) FROM dual;
--结果：5

FLOOR(X)：小于或等于X的最大值

SELECT FLOOR(4.9) FROM dual;
--结果：4

LOG(X,Y)：X为底Y的对数

SELECT LOG(2，8) FROM dual;
--结果：3

MOD(X,Y)：X除以Y的余数

SELECT MOD(8，3) FROM dual;
--结果：2

POWER(X,Y)：X的Y次幂

SELECT POWER(2，3) FROM dual;
--结果：8

ROUND(X[,Y])：X在第Y位四舍五入

SELECT ROUND(5.45，1) FROM dual;
--结果：5.5

SQRT(X)：X的平方根

SELECT SQRT(4) FROM dual;
--结果：2

TRUNC(X[,Y])：X在第Y位截断

SELECT TRUNC(6.476，2) FROM dual;
--结果：6.47

日期函数：对日期和时间进行处理。

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

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

SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;
--结果：2021/5/12 3:53:21 2021/10/12 3:53:21

LAST_DAY(d)：返回指定日期当月的最后一天。

SELECT SYSDATE,last_day(SYSDATE) FROM dual;
--结果：2021/5/12 3:56:15 2021/5/31 3:56:15

ROUND(d[,fmt])：返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式

模型。默认 fmt 为 DDD，即月中的某一天。

SELECT SYSDATE,
       ROUND(SYSDATE),
       ROUND(SYSDATE, 'day'),
       ROUND(SYSDATE, 'month'),
       ROUND(SYSDATE, 'year')
  FROM DUAL;
--结果：
	2021/5/12 3:57:11
	2021/5/12 
	2021/5/9 
	2021/5/1 
	2021/1/1

EXTRACT(fmt FROM d)：提取日期中的特定部分。

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;

--结果：
	2021/5/12 3:59:32
	2021
	5
	12
	7
	59
	32.248128

CURRENT_DATE：返回当前会话时区所对应的日期时间。

select CURRENT_DATE from dual;
--结果：2021/6/10 14:32:49

CURRENT_TIMESTAMP：返回当前会话时区所对应的日期时间。

select CURRENT_TIMESTAMP from dual;
--结果：10-6月 -21 02.34.20.845299 下午 +08:00

LOCALTIMESTAMP：返回当前会话时区的日期时间。

Select LOCALTIMESTAMP from dual;
--结果：10-6月 -21 02.36.17.989733 下午

MONTHS_BETWEEN(date1,date2)：计算date1和date2之间相差的月数.如果date1<date2，则返回负数；如果date1,date2这两个日期中日分量信息是相同的,或者这两个日期都分别是所在月的最后一天,那么返回的结果是一个整数,否则包括一个小数,小数为富余天数除以31，Oracle以每月31天为准计算结果。

select months_between('19-12月-2021','19-3月-2021') mon_between from dual;
--结果：9

ROUND(d[,format])：将日期d按照由format指定的格式进行四舍五入处理处理.如果没有给format则使用缺省设置DD.

Select round(sysdate,'MONTH') from dual;
--结果：2021/6/1

SYS_EXTRACT_UTC(date)：返回特定时区时间所对应的格林威治时间。

select SYS_EXTRACT_UTC(systimestamp) from dual;
--结果：10-6月 -21 06.41.59.738669 上午

SYSDATE：取得当前的日期和时间,类型是DATE.它没有参数.但在分布式SQL语句中使用时,SYSDATE返回本地数据库的日期和时间.

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--结果：2021-06-10 02:42:48

SYSTIMESTAMP：返回当前系统的日期时间及时区。

SELECT systimestamp from dual;
--结果：10-6月 -21 02.44.06.551610 上午 -04:00

TO_TIMESTAMP(char[fmt[,’nls_param’]])：将符合特定日期和时间格式的字符串转变为TIMESTAMP类型。

select systimestamp from dual;
--结果：10-6月 -21 02.47.59.481088 上午 -04:00

--字符型转成timestamp
select TO_TIMESTAMP('01-1月-03') from dual;
--结果：01-1月 -03 12.00.00.000000000 上午
select to_timestamp('01-10月-08 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') FROM dual;
--结果：01-10月-08 07.46.41.000000000 上午

--timestamp转成date型
select cast(TO_TIMESTAMP('2015-10-01 21:11:11.328', 'yyyy-mm-dd hh24:mi:ss.ff') as date)FROM dual;
--结果：2015/10/1 21:11:11

--date型转成timestamp
select cast(sysdate as timestamp) date_to_timestamp FROM dual;
--结果：10-6月 -21 02.50.07.000000 上午

TO_TIMESTAMP_TZ(char[fmt[,’nls_param’]])：将符合特定日期和时间格式的字符串转变为TIMESTAMP WITH TIME ZONE类型。

select TO_TIMESTAMP_TZ('20130101','yyyymmdd') from dual;
--结果：01-1月 -13 12.00.00.000000000 上午 +08:00

TRUNC(d,format)：截断日期时间数据，计算截尾到由format指定单位的日期d.缺省参数同ROUNG.

select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') FIRST, to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') second from dual;
--结果：2021.06.10 02:00:00，2021.06.10 02:52:00

转换函数：可以将一种数据类型转换为另外一种数据类型。

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

SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date" FROM DUAL;
--结果：2021年05月12日 04:01:11

TO_DATE(X,[,fmt])：把一个字符串以fmt格式转换成一个日期类型

select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
--结果：2005/1/1 13:14:20

TO_NUMBER(X,[,fmt])：把一个字符串以fmt格式转换为一个数字

SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;
--结果：-12345.67

TO_CLOB (char)：将字符串转变为CLOB类型。

Select TO_CLOB（n'test'） from dual;

分组函数

分组函数也被称为多行函数，它会根据输入的多行数据返回一个结果。主要用于执行数据统计或汇总操作，并且分组函数只能出现在select语句选择列表、order by子句和having子句中。注意分组函数不能直接在plsql中引用，只能在内嵌select语句中使用。

AVG([DISTINCT|ALL]col)：返回一列数据的平均值,缺省使用是ALL修饰符，all表示对所有的值求平均值,distinct排重后再求平均值

select avg(distinct origin_code) from t_origin;
--结果：1517406.00456621

select avg(ALL origin_code) from t_origin;
--结果：1517406.00456621

CORR([expr1,expr2)：返回成对数值的相关系数，其数值使用表达式”covar_pop(expr1,expr2)/(stddev_pop(expr1)*stddev_pop(expr2))”

 select corr(origin_code,leaf) from t_origin;
 --结果：0.787679048632485

COUNT(*|[DISTINCT|ALL] col)：得到查询中行的数

SELECT count(distinct origin_code) from t_origin;
--结果：219

COVAR_POP(expr1,expr2)：返回成对数字的协方差，其数值使用表达式”(sum(expr1expr2)-sum(expr1)sum(expr2)/n)/n”

Select COVAR_POP(origin_code,ID) from t_origin;
--结果：985452046305418

COVAR_SAMP(expr1,expr2)：返回成对数字的协方差，其数值使用表达式”(sum(expr1expr2)-sum(expr1)sum(expr2)/n)/n-1”

Select COVAR_SAMP(origin_code,ID) from t_origin;
--结果：989972468536177

CUME_DIST(expr1,expr2…) within group (order by expr1,expr2…)：返回特定数值在一组行数据中的累积分布比例。

Select CUME_DIST(4) within group (order by origin_code) from t_origin;
--结果：0.0909090909090909

DENSE_RANK(expr1,expr2…) within group (order by expr1,expr2…)：返回特定数据在一组行数据中的等级。

Select DENSE_RANK (4) within group (order by origin_code) from t_origin;
--结果：20

GROUPING(expr)：用于确定统计结果是否使用了特定的表达式，返回0则用到了表达式，1则未用。

SELECT SID,
       CID,
       COUNT(1),
       GROUPING(SID),
       GROUPING(CID)
  FROM SC
 GROUP BY ROLLUP(SID, CID);

GROUPING_ID(expr1[,expr2]…)：返回对应于特定行的grouping位向量的值。

SELECT SID, CID, SUM(SCORE), GROUPING_ID(SID, CID)
  FROM SC
 GROUP BY ROLLUP(SID, CID)

MAX([DISTINCT|ALL]col)：获得选择列表或表达式的最大值，ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次

 select max(distinct score) from SC;
 --结果：99

MIN([DISTINCT|ALL]col)：获得选择列表或表达式的最小值，ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次

select min(all score) from SC;
--结果：20

PERCENT_RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)：返回特定数值在统计级别中所占的比例。

select percent_rank(3000) within group(order by score) from sc;
--结果：1

PERCENTILE_CONT(percent_expr)WITHIN GROUP (ORDER BY expr)：返回在统计级别中处于某个百分点的特定数值（按照连续分布模型确定）。

select percentile_cont(.6) within group(order by score) from sc;
--结果：80

PERCENTILE_DISC(percent_expr)WITHIN GROUP (ORDER BY expr)：返回在统计级别中处于某个百分点的特定数值（按照离散分布模型确定）。

select PERCENTILE_DISC(.6) within group(order by score) from sc;
--结果:80

RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)：返回特定数值中所占据的等级。

select rank(50) within group(order by score) from sc;
--结果：5

STDDEV([DISTINCT|ALL]col)：获得选择列表的标准差

select stddev(score) from sc;
--结果：24.9593132970729

select stddev(distinct  score) from sc;
--结果：27.6194707558846

STDDEV_POP(col)：返回统计标准差，其数值是统计方差的平方根.

select stddev_pop(score) from sc;
--结果：24.2560925272674

STDDEV_SAMP(col)：返回采样标准差，其数值是采样方差的平方根.

 select stddev_samp(score) from sc;
 --结果：24.9593132970729

SUM([DISTINCT|ALL]col)：返回选择的数值和总和

Select sum(score) from sc;
--结果：1234

VAR_POP([DISTINCT|ALL]col)：返回统计方差.使用公式为(sum(exprexpr)-sum(expr)sum(expr)/count(expr))/(count(expr)

select VAR_POP (score) from sc;
--结果：588.358024691358

VAR_SAMP([col)：返回采样方差.使用公式为(sum(exprexpr)-sum(expr)sum(expr)/count(expr))/(count(expr-1)

select variance (score) from sc;
--结果：622.967320261438

VARIANCE([DISTINCT|ALL]col)：返回选择列或表达式的采样方差.使用公式为(sum(exprexpr)-sum(expr)sum(expr)/count(expr))/(count(expr-1)

select variance(score) from sc;
--结果：622.967320261438

其他函数

COALESCE(exp1,exp2,exp3,…)：依次查找各参数，遇到非NULL则返回，各参数或表达式数据类型必须一致，如果都为null则返回null。

Select COALESCE(SID,cid) from sc;

DECODE(base_expr,comparel,valuel,Compare2,value2,…default)：把base_expr与后面的每个compare(n)进行比较,如果匹配返回相应的value (n).如果没有发生匹配,则返回default，每个valuel数据类型必须一致，如果没有default则返回null。

Select decode(a,'金',1,'银',2,0) from table_name;

NULLIF (expr1, expr2)：比较表达式expr1和expr2，相等返回null，否则返回expr1.

Select nullif(expr1, expr2) from table_name;

NVL (expr1, expr2)：将NULL转变为实际值，如果expr1是NULL,那么返回expr2,否则返回expr1，expr1、expr2两者必须为同类型或expr2可以隐式转换为expr1，否则会报错。

Select nvl(column_name,0) from tbale_name;

NVL2 (expr1, expr2, expr3)：expr1不为NULL，返回expr2；expr1为NULL，返回expr3。expr1可以是任意数据类型；expr2与expr3可以是除LONG外的任意数据类型，但需要类型一致或expr3可以隐式转换为expr2。

SELECT NVL2(to_date('01-jun-2016'),sysdate - to_date('01-jun-2016'),sysdate) FROM dual;