Oracle 函数大全[通俗易懂]

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

Oracle SQL 提供了用于执行特定操作的专用函数。主要使用单行函数和聚合函数两种类型的函数：单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果：字符函数、数字函数、转换函数以及日期函数字符函数：对字符串操作，接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。ASCII(X)：返回字符X的ASCII码代码语言：javascript复制SELECT ASCII('a') FROM dual;

--结果：97CONCAT(X,Y)：连接字符串X和Y代码语言：javascript复制SELECT CONCAT('1','2') FROM dual;

--结果：12INSTR(X,STR[,START][,N)：从X中查找str，可以指定从start开始，也可以指定从n开始代码语言：javascript复制SELECT INSTR('abcdefgh','de') FROM dual;

--结果：4LENGTH(X)：返回X的长度代码语言：javascript复制SELECT LENGTH('abcdefgh') FROM dual;

--结果：8LOWER(X)：X转换成小写代码语言：javascript复制SELECT LOWER('ABcdefgh') FROM dual;

--结果：abcdefghUPPER(X)：X转换成大写代码语言：javascript复制SELECT UPPER('ABcdefgh') FROM dual;

--结果：ABCDEFGHLTRIM(X[,TRIM_STR])：把X的左边截去trim_str字符串，缺省截去空格代码语言：javascript复制SELECT LTRIM('=ABcdefgh=','=') FROM dual;

--结果：ABcdefgh=RTRIM(X[,TRIM_STR])：把X的右边截去trim_str字符串，缺省截去空格代码语言：javascript复制SELECT RTRIM('=ABcdefgh=','=') FROM dual;

--结果：=ABcdefghTRIM([TRIM_STR FROM]X)：把X的两边截去trim_str字符串，缺省截去空格代码语言：javascript复制SELECT TRIM('='FROM'=ABcdefgh=') FROM dual;

--结果：ABcdefghREPLACE(X,old,new)：在X中查找old，并替换成new代码语言：javascript复制SELECT REPLACE('ABCDE','CD','AAA') FROM dual;

--结果：ABAAAESUBSTR(X,start[,length])：返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾代码语言：javascript复制SELECT SUBSTR('ABCDE','2','3') FROM dual;

--结果：BCDINITCAP：返回字符单词首字母大写，其余小写，单词用空格和非字母字符分隔代码语言：javascript复制select initcap('hEllo') name from dual;

--结果：HelloLPAD/RPAD（string1,x[,string2]）：在string1字符左边或右边粘贴数个string2字符，直到字符总字节数达到x字节。string2默认为空格。代码语言：javascript复制select lpad(rpad('111111',10,'*'),17,'*') NAME from dual;

--结果：*******111111****REGEXP_REPLACE(str1,pattem[,str2[,pos[,occ[,par]]]])：用于按照特定正则表达式的规则替换字符串。其中参数str1指定源字符表达式，pattem指定正则表达式，str2指定替换字符串，pos指定起始搜索位置，occ指定替换出现的第几个字符串，par指定默认匹配操作的文本串。代码语言：javascript复制--将某个字段值设置替换成空

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指定默认匹配操作的文本串。代码语言：javascript复制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.代码语言：javascript复制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的绝对值代码语言：javascript复制SELECT ABS('-10') FROM dual;

--结果：10ACOS(X)：X的反余弦代码语言：javascript复制SELECT ACOS(1) FROM dual;

--结果：0COS(X)：余弦代码语言：javascript复制SELECT COS(1) FROM dual;

--结果：0.54030230586814CEIL(X)：大于或等于X的最小值代码语言：javascript复制SELECT CEIL(4.9) FROM dual;

--结果：5FLOOR(X)：小于或等于X的最大值代码语言：javascript复制SELECT FLOOR(4.9) FROM dual;

--结果：4LOG(X,Y)：X为底Y的对数代码语言：javascript复制SELECT LOG(2，8) FROM dual;

--结果：3MOD(X,Y)：X除以Y的余数代码语言：javascript复制SELECT MOD(8，3) FROM dual;

--结果：2POWER(X,Y)：X的Y次幂代码语言：javascript复制SELECT POWER(2，3) FROM dual;

--结果：8ROUND(X[,Y])：X在第Y位四舍五入代码语言：javascript复制SELECT ROUND(5.45，1) FROM dual;

--结果：5.5SQRT(X)：X的平方根代码语言：javascript复制SELECT SQRT(4) FROM dual;

--结果：2TRUNC(X[,Y])：X在第Y位截断代码语言：javascript复制SELECT TRUNC(6.476，2) FROM dual;

--结果：6.47日期函数：对日期和时间进行处理。ADD_MONTHS(d,n)：在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。d 表示日期，n 表示要加的月数。

代码语言：javascript复制SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

--结果：2021/5/12 3:53:21 2021/10/12 3:53:21LAST_DAY(d)：返回指定日期当月的最后一天。代码语言：javascript复制SELECT SYSDATE,last_day(SYSDATE) FROM dual;

--结果：2021/5/12 3:56:15 2021/5/31 3:56:15ROUND(d[,fmt])：返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天。

代码语言：javascript复制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/1EXTRACT(fmt FROM d)：提取日期中的特定部分。代码语言：javascript复制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.248128CURRENT_DATE：返回当前会话时区所对应的日期时间。代码语言：javascript复制select CURRENT_DATE from dual;

--结果：2021/6/10 14:32:49CURRENT_TIMESTAMP：返回当前会话时区所对应的日期时间。代码语言：javascript复制select CURRENT_TIMESTAMP from dual;

--结果：10-6月 -21 02.34.20.845299 下午 +08:00LOCALTIMESTAMP：返回当前会话时区的日期时间。代码语言：javascript复制Select LOCALTIMESTAMP from dual;

--结果：10-6月 -21 02.36.17.989733 下午MONTHS_BETWEEN(date1,date2)：计算date1和date2之间相差的月数.如果date1

--结果：9ROUND(d[,format])：将日期d按照由format指定的格式进行四舍五入处理处理.如果没有给format则使用缺省设置DD.代码语言：javascript复制Select round(sysdate,'MONTH') from dual;

--结果：2021/6/1SYS_EXTRACT_UTC(date)：返回特定时区时间所对应的格林威治时间。代码语言：javascript复制select SYS_EXTRACT_UTC(systimestamp) from dual;

--结果：10-6月 -21 06.41.59.738669 上午SYSDATE：取得当前的日期和时间,类型是DATE.它没有参数.但在分布式SQL语句中使用时,SYSDATE返回本地数据库的日期和时间.代码语言：javascript复制select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

--结果：2021-06-10 02:42:48SYSTIMESTAMP：返回当前系统的日期时间及时区。代码语言：javascript复制SELECT systimestamp from dual;

--结果：10-6月 -21 02.44.06.551610 上午 -04:00TO_TIMESTAMP(char[fmt[,’nls_param’]])：将符合特定日期和时间格式的字符串转变为TIMESTAMP类型。代码语言：javascript复制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类型。代码语言：javascript复制select TO_TIMESTAMP_TZ('20130101','yyyymmdd') from dual;

--结果：01-1月 -13 12.00.00.000000000 上午 +08:00TRUNC(d,format)：截断日期时间数据，计算截尾到由format指定单位的日期d.缺省参数同ROUNG.代码语言：javascript复制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是格式化字符串代码语言：javascript复制SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date" FROM DUAL;

--结果：2021年05月12日 04:01:11TO_DATE(X,[,fmt])：把一个字符串以fmt格式转换成一个日期类型代码语言：javascript复制select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;

--结果：2005/1/1 13:14:20TO_NUMBER(X,[,fmt])：把一个字符串以fmt格式转换为一个数字代码语言：javascript复制SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;

--结果：-12345.67TO_CLOB (char)：将字符串转变为CLOB类型。代码语言：javascript复制Select TO_CLOB（n'test'） from dual;分组函数 分组函数也被称为多行函数，它会根据输入的多行数据返回一个结果。主要用于执行数据统计或汇总操作，并且分组函数只能出现在select语句选择列表、order by子句和having子句中。注意分组函数不能直接在plsql中引用，只能在内嵌select语句中使用。

AVG([DISTINCT|ALL]col)：返回一列数据的平均值,缺省使用是ALL修饰符，all表示对所有的值求平均值,distinct排重后再求平均值代码语言：javascript复制select avg(distinct origin_code) from t_origin;

--结果：1517406.00456621

select avg(ALL origin_code) from t_origin;

--结果：1517406.00456621CORR([expr1,expr2)：返回成对数值的相关系数，其数值使用表达式”covar_pop(expr1,expr2)/(stddev_pop(expr1)*stddev_pop(expr2))”代码语言：javascript复制 select corr(origin_code,leaf) from t_origin;

--结果：0.787679048632485COUNT(*|[DISTINCT|ALL] col)：得到查询中行的数代码语言：javascript复制SELECT count(distinct origin_code) from t_origin;

--结果：219COVAR_POP(expr1,expr2)：返回成对数字的协方差，其数值使用表达式”(sum(expr1expr2)-sum(expr1)sum(expr2)/n)/n”代码语言：javascript复制Select COVAR_POP(origin_code,ID) from t_origin;

--结果：985452046305418COVAR_SAMP(expr1,expr2)：返回成对数字的协方差，其数值使用表达式”(sum(expr1expr2)-sum(expr1)sum(expr2)/n)/n-1”代码语言：javascript复制Select COVAR_SAMP(origin_code,ID) from t_origin;

--结果：989972468536177CUME_DIST(expr1,expr2…) within group (order by expr1,expr2…)：返回特定数值在一组行数据中的累积分布比例。代码语言：javascript复制Select CUME_DIST(4) within group (order by origin_code) from t_origin;

--结果：0.0909090909090909DENSE_RANK(expr1,expr2…) within group (order by expr1,expr2…)：返回特定数据在一组行数据中的等级。代码语言：javascript复制Select DENSE_RANK (4) within group (order by origin_code) from t_origin;

--结果：20GROUPING(expr)：用于确定统计结果是否使用了特定的表达式，返回0则用到了表达式，1则未用。代码语言：javascript复制SELECT SID,

CID,

COUNT(1),

GROUPING(SID),

GROUPING(CID)

FROM SC

GROUP BY ROLLUP(SID, CID);GROUPING_ID(expr1[,expr2]…)：返回对应于特定行的grouping位向量的值。代码语言：javascript复制SELECT SID, CID, SUM(SCORE), GROUPING_ID(SID, CID)

FROM SC

GROUP BY ROLLUP(SID, CID)MAX([DISTINCT|ALL]col)：获得选择列表或表达式的最大值，ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次代码语言：javascript复制 select max(distinct score) from SC;

--结果：99MIN([DISTINCT|ALL]col)：获得选择列表或表达式的最小值，ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次代码语言：javascript复制select min(all score) from SC;

--结果：20PERCENT_RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)：返回特定数值在统计级别中所占的比例。代码语言：javascript复制select percent_rank(3000) within group(order by score) from sc;

--结果：1PERCENTILE_CONT(percent_expr)WITHIN GROUP (ORDER BY expr)：返回在统计级别中处于某个百分点的特定数值（按照连续分布模型确定）。代码语言：javascript复制select percentile_cont(.6) within group(order by score) from sc;

--结果：80PERCENTILE_DISC(percent_expr)WITHIN GROUP (ORDER BY expr)：返回在统计级别中处于某个百分点的特定数值（按照离散分布模型确定）。代码语言：javascript复制select PERCENTILE_DISC(.6) within group(order by score) from sc;

--结果:80RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)：返回特定数值中所占据的等级。代码语言：javascript复制select rank(50) within group(order by score) from sc;

--结果：5STDDEV([DISTINCT|ALL]col)：获得选择列表的标准差代码语言：javascript复制select stddev(score) from sc;

--结果：24.9593132970729

select stddev(distinct score) from sc;

--结果：27.6194707558846STDDEV_POP(col)：返回统计标准差，其数值是统计方差的平方根.代码语言：javascript复制select stddev_pop(score) from sc;

--结果：24.2560925272674STDDEV_SAMP(col)：返回采样标准差，其数值是采样方差的平方根.代码语言：javascript复制 select stddev_samp(score) from sc;

--结果：24.9593132970729SUM([DISTINCT|ALL]col)：返回选择的数值和总和代码语言：javascript复制Select sum(score) from sc;

--结果：1234VAR_POP([DISTINCT|ALL]col)：返回统计方差.使用公式为(sum(exprexpr)-sum(expr)sum(expr)/count(expr))/(count(expr)代码语言：javascript复制select VAR_POP (score) from sc;

--结果：588.358024691358VAR_SAMP([col)：返回采样方差.使用公式为(sum(exprexpr)-sum(expr)sum(expr)/count(expr))/(count(expr-1)代码语言：javascript复制select variance (score) from sc;

--结果：622.967320261438VARIANCE([DISTINCT|ALL]col)：返回选择列或表达式的采样方差.使用公式为(sum(exprexpr)-sum(expr)sum(expr)/count(expr))/(count(expr-1)代码语言：javascript复制select variance(score) from sc;

--结果：622.967320261438其他函数COALESCE(exp1,exp2,exp3,…)：依次查找各参数，遇到非NULL则返回，各参数或表达式数据类型必须一致，如果都为null则返回null。代码语言：javascript复制Select COALESCE(SID,cid) from sc;DECODE(base_expr,comparel,valuel,Compare2,value2,…default)：把base_expr与后面的每个compare(n)进行比较,如果匹配返回相应的value (n).如果没有发生匹配,则返回default，每个valuel数据类型必须一致，如果没有default则返回null。代码语言：javascript复制Select decode(a,'金',1,'银',2,0) from table_name;NULLIF (expr1, expr2)：比较表达式expr1和expr2，相等返回null，否则返回expr1.代码语言：javascript复制Select nullif(expr1, expr2) from table_name;NVL (expr1, expr2)：将NULL转变为实际值，如果expr1是NULL,那么返回expr2,否则返回expr1，expr1、expr2两者必须为同类型或expr2可以隐式转换为expr1，否则会报错。代码语言：javascript复制Select nvl(column_name,0) from tbale_name;NVL2 (expr1, expr2, expr3)：expr1不为NULL，返回expr2；expr1为NULL，返回expr3。expr1可以是任意数据类型；expr2与expr3可以是除LONG外的任意数据类型，但需要类型一致或expr3可以隐式转换为expr2。代码语言：javascript复制SELECT NVL2(to_date('01-jun-2016'),sysdate - to_date('01-jun-2016'),sysdate) FROM dual;发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/154754.html原文链接：https://javaforall.cn