2024年4月12日发(作者：)
Oracle 函数大全
一、字符函数 ............................................................................................ 2
二、单值函数 ............................................................................................ 8
三、列表函数 .......................................................................................... 12
四、日期函数 .......................................................................................... 12
五、转换函数 .......................................................................................... 21
六、变换函数 .......................................................................................... 21
七、分组函数 .......................................................................................... 22
一、字符函数
1、ASCII(n)函数
函数
描述
实例
ASCII(n)
返回字符串的ascii码(当输入为字符串时返回第一个字符的ascii码)
备注
2、CHR(n)函数
函数
描述
实例
CHR(n)
返回对应的ascii码的字符(n必须为数字类型)
备注
3、CONCAT(n,m)函数
函数
描述
实例
CONCAT(n,m)
连接n和m，n和m可以是字符，也可以是字符串。作用和”||”一样。
备注
4、INITCAP(n)函数
函数
INITCAP(n)
描述
将字符串n中每个单词首字母大写，其余小写(区分单词的规则是按空格
或非字母字符；可以输入中文字符，但没有任何作用)
实例
备注
5、INSTR(chr1,chr2,[n,[m]])函数
函数
INSTR(chr1,chr2,[n,[m]])
描述
获取字符串chr2在字符串chr1中出现的位置。n和m可选,省略是默认
为1；n代表开始查找的起始位置，当n为负数从尾部开始搜索；m代表
字串出现的次数。
实例
备注
当n为负数从尾部搜索，但返回值仍然是按正向排列得出的位置。
6、LENGTH(n)函数
函数
LENGTH(n)
描述
返回字符或字符串长度。(当n为null时，返回null；返回的长度包括后
面的空格)
实例
备注
7、LOWER(n)函数
函数
LOWER(n)
描述
将n转换为小写。
实例
备注
8、LPAD(chr1,n,[chr2])函数（与函数19对应）
函数
LPAD(chr1,n,[chr2])
描述
在chr1左边填充字符chr2，使得字符总长度为n。chr2可选，默认为空
格；当chr1字符串长度大于n时，则从左边截取chr1的n个字符显示。
实例
备注
不够字符则用*来填满
9、LTRIM(chr,[n])函数（与函数20对应）
函数
LTRIM(chr,[n])
描述
去掉字符串chr左边包含的n字符串中的任何字符，直到出现一个不包
含在n中的字符为止。
实例
备注
10、NLS_INITCAP(chr,[’nls_param’])函数
函数
NLS_INITCAP(chr,[’nls_param’])
描述
将chr首字母大写。Nls_param可选，指定排序的方式。（有
SCHINESE_RADICAL_M（部首、笔画），SCHINESE_STROKE_M（笔
画、部首），SCHINESE_PINYIN_M（拼音））
实例
备注
11、NLS_LOWER(chr,[‘nls_param’])函数
函数
NLS_LOWER(chr,[‘nls_param’])
描述
将字符串转换为小写。Nls_param可选，指定排序的方式。（有
SCHINESE_RADICAL_M（部首、笔画），SCHINESE_STROKE_M（笔
画、部首），SCHINESE_PINYIN_M（拼音））
实例
备注
12、NLSSORT(col,[’nls_param’])函数
函数
NLSSORT(col,[’nls_param’])
描述
根据nls_param指定的方式对col字段进行排序
实例
备注
拼音 SELECT * FROM TEAM ORDER BY NLSSORT(队名,'NLS_SORT =
SCHINESE_PINYIN_M')
笔划 SELECT * FROM TEAM ORDER BY NLSSORT(队名,'NLS_SORT =
SCHINESE_STROKE_M')
部首 SELECT * FROM TEAM ORDER BY NLSSORT(队名,'NLS_SORT =
SCHINESE_RADICAL_M')
13、NLS_UPPER(chr,[‘nls_param’])函数
函数
NLS_UPPER(chr,[‘nls_param’])
描述
将chr转换为大写。Nls_param可选，用于指定排序规则
实例
备注
14、REGEXP_REPLACE(source_string,pattern,replace_string,position,occurtence,
match_parameter) 函数(10g新函数)
函数
REGEXP_REPLACE
描述
字符串替换函数。相当于增强的replace函数。Source_string指定源字符
表达式；pattern指定规则表达式；replace_string指定用于替换的字符串；
position指定起始搜索位置；occurtence指定替换出现的第n个字符串；
match_parameter指定默认匹配操作的文本串。
实例
备注
select regexp_replace('abc12hdjj2345jsdsjh456789jdsk','([0-9]+)',lpad('1',10,'0'))
from dual
其中replace_string,position,occurtence,match_parameter参数都是可选的
15、REGEXP_SUBSTR(source_string, pattern[,position [, occurrence[,
match_parameter]]]) 函数(10g新函数)
函数
REGEXP_SUBSTR
描述
返回匹配模式的子字符串。相当于增强的substr函数。Source_string指
定源字符表达式；pattern指定规则表达式；position指定起始搜索位置；
occurtence指定替换出现的第n个字符串；match_parameter指定默认匹
配操作的文本串。
实例
备注
select REGEXP_SUBSTR('2222 333 444','(^| )[^ ]+',1,1) from dual;
其中position,occurtence,match_parameter参数都是可选的
16、REGEXP_LIKE(source_string, pattern[, match_parameter])函数(10g新函数)
函数
REGEXP_LIKE
描述
返回满足匹配模式的字符串。相当于增强的like函数。Source_string指
定源字符表达式；pattern指定规则表达式；match_parameter指定默认匹
配操作的文本串。
实例
备注
其中position,occurtence,match_parameter参数都是可选的
17、REGEXP_INSTR(source_string, pattern[, start_position[, occurrence[,
return_option[, match_parameter]]]])函数(10g新函数)
函数
REGEXP_INSTR
描述
该函数查找 pattern ，并返回该模式的第一个位置。您可以随意指定您
想要开始搜索的 start_position。 occurrence 参数默认为 1，除非您指定
您要查找接下来出现的一个模式。return_option 的默认值为 0，它返回
该模式的起始位置；值为 1 则返回符合匹配条件的下一个字符的起始位
置
实例
备注
18、REPLACE(chr,search_string,[,replacement_string])函数
函数
REPLACE
描述
将chr中满足search_string条件的替换为replacement_string指定的字符
串，当search_string为null时，返回chr；当replacement_string为null
时，返回chr中截取掉search_string部分的字符串。
实例
备注
19、RPAD(chr1,n,chr2)函数
函数
RPAD(chr1,n,chr2)
描述
在chr1右边填充chr2，使返回字符串长度为n..当chr1长度大于n时，
返回左端n个字符。
实例
备注
参考LPAD()函数。
20、RTRIM(chr,[set])函数
函数
RTRIM(chr,[set])
描述
去掉chr右边包含的set中的任何字符，直到出现一个不是set中的字符
结束。
实例
备注
参考LTRIM()函数
21、SOUNDEX(chr)函数
函数
SOUNDEX(chr)
描述
返回字符串的语音表示，可以用来比较字符串的发音是否相同。
实例 select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
备注
22、SUBSTR(chr,m[,n])函数
函数
SUBSTR(chr,m[,n])
描述
取chr的子串。M代表开始位置，n是要取的长度。当m为0时从首字
符开始，当m为负时从字符串尾部开始截取。
实例
备注
注意：m取0或1时，开始位置是一样的，都是从第一位开始，m为负
的时候，仍然是按从左到右的顺序取，所以如果m为-1，n的长度再大，
也只能取到最后一个字符，因为chr右边已经没有字符了。
23、TRANSLATE(chr,from_str,to_str)函数
函数
TRANSLATE(chr,from_str,to_str)
描述
另一种替换函数的用法
实例
备注
匹配的规则是from_str和to_str每个字符按顺序相对应，如果from_str
字符少于to_str中的字符，则只替换能对应的字符，to_str后面不能和
from_str对应的字符则不管，如果from_str字符多于to_str字符，则
from_str中找不到对应字符按照null来处理。
24、TRIM(chr)函数
函数
TRIM(chr)
描述
TRIM函数将字符串的前缀（或尾随）字符删除。
其具体的语法格式如下：
TRIM（[LEADING|TRAILING|BOTH][trimchar FROM] string）
其中：
LEADING 指明仅仅将字符串的前缀字符删除
TRAILING 指明仅仅将字符串的尾随字符删除
BOTH 指明既删除前缀字符，也删除尾随字符。这也是默认方式
string 任意一待处理字符串
trimchar 可选项。指明试图删除什么字符，默认被删除的字符是空格
下面是该函数的使用情况：
TRIM（’ Ashley ’）=‘Ashley’
TRIM（LEADING ’*’ FROM’***Ashley***’）=‘Ashley***’
实例
备注
25、UPPER(chr)函数
函数
UPPER(chr)
描述
UPPER函数间返回字符串的大写形式。
实例
备注
二、单值函数
1、ABS(n) 函数
函数
ABS(n)
描述
返回数字的绝对值
实例
备注
2、COS(n) 函数
函数
COS(n)
描述
返回n的余弦值
实例
备注
3、ACOS(n) 函数
函数
ACOS(n)
描述
反余弦函数，n between -1 and 1，返回值between 0 and pi。
实例
备注
4、BITAND(n1,n2) 函数
函数
BITAND(n1,n2)
描述
位与运算
实例
备注
假设3,9做位与运算，3的二进制形式为：0011，9的二进制形式为：1001，
则结果是0001，转换成10进制数为1。
5、CEIL(n)函数
函数
CEIL(n)
描述
返回大于或等于n的最小的整数值
实例
备注
6、FLOOR(n)函数
函数
FLOOR(n)
描述
返回小于或等于n的最小的整数值
实例
备注
7、BIN_TO_NUM(n1,n2,....n)函数
函数
BIN_TO_NUM(n1,n2,....n)
描述
二进制转向十进制
实例
备注
8、SIN(n) 函数
函数
SIN(n)
描述
返回n的正玄值,n为弧度
实例
备注
9、SINH(n)函数
函数
SINH(n)
描述
返回n的双曲正玄值,n为弧度
实例
备注
10、ASIN(n) 函数
函数
描述
实例
ASIN(n)
反正玄函数，n between -1 and 1，返回值between pi/2 and -pi/2。
备注
11、TAN(n) 函数
函数
TAN(n)
描述
返回n的正切值,n为弧度
实例
备注
12、TANH(n) 函数
函数
TANH(n)
描述
返回n的双曲正切值,n为弧度
实例
备注
13、ATAN(n) 函数
函数
ATAN(n)
描述
反正切函数，n表示弧度，返回值between pi/2 and -pi/2。
实例
备注
14、EXP(n) 函数
函数
EXP(n)
描述
返回e的n次幂，e = 2.71828183 ...
实例
备注
15、LN(n) 函数
函数
LN(n)
描述
返回n的自然对数，n>0
实例
备注
16、LOG(n1,n2)函数
函数
LOG(n1,n2)
描述
返回以n1为底n2的对数，n1 >0 and not 1 ，n2>0
实例
备注
17、POWER(n1,n2) 函数
函数
POWER(n1,n2)
描述
返回n1的n2次方。n1,n2可以为任意数值，不过如果m是负数，则n
必须为整数
实例
备注
18、SIGN(n)函数
函数
SIGN(n)
描述
如果n<0返回-1,如果n>0返回1，如果n=0返回0
实例
备注
19、SQRT(n) 函数
函数
SQRT(n)
描述
返回n的平方根,n为弧度。n>=0
实例
备注
20、TRUNC函数
函数
TRUNC
描述
按照指定的精度截取一个数
实例
备注
三、列表函数
1、greatest 函数
函数
greatest
描述
取一个一维数组中的最大值greatest
实例
备注
2、least 函数
函数
least
描述
取一个一维数组中的最小值least
实例
备注
四、日期函数
TO_DATE格式
Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998
24小时格式下时间范围为： 0:00:00 - 23:
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....
1、日期和字符转换函数用法（to_date,to_char）
函数
to_date,to_char
描述
日期和字符转换
实例
备注
2、 求某天是星期几
函数
to_date,to_char
描述
求某天是星期几
实例
备注
3、monday 设置日期语言
函数
描述
实例
monday
显示日期格式
备注
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4、两个日期间的天数
函数
描述
实例
floor
两个日期间的天数
备注
5. 时间为null的用法
函数
描述
实例
To_date
To_date（null）
备注
6、组合用法
函数
描述
实例
To_date
查找2008-07-28至2008-07-01间除星期一和七的天数
备注
7. months_between
函数
描述
实例
months_between
计算日期
备注
8. Next_day的用法 (24同解)
函Next_day(date, day)
数
描准确来说一个礼拜是从星期日开始到星期六结束的，很多的查询条件和统计都需要求得一周
述 的时间段，也就是星期一到星期日的时间段， 我们用to_char和next_day两个函数分别可
以求得第一天和最后一天的日期。
实
例
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') 今天,
'星期'||SUBSTR('日一二三四五六',TO_NUMBER(TO_CHAR(SYSDATE,'D')),1) 星期几,
TO_CHAR(NEXT_DAY(sysdate,'星期一')-7,'YYYY-MM-DD') 星期一,
TO_CHAR(NEXT_DAY(sysdate,'星期一')-1,'YYYY-MM-DD') 星期日
FROM DUAL； （未成功执行）
得到的结果是星期日开始作为第1天
备
** 使用TO_CHAR(SYSDATE,'D')可以求得当前日期是一周的第几天，
注
的，那么星期一就是第2天，星期日就是第8天 */
/** SUBSTR代替DECODE函数， NEXT_DAY 函数可以指定当前日期的下一个星期几的日期 ，比如：今
天是11-25日，星期六，那么 NEXT_DAY(sysdate,'星期一') ， 得到的日期就是11-27，那么这个星期一的日
期就是 11-27 减去 7天， 而星期日的日期就是11-27减去1天 */
9、获得时间
函数
描述
实例
To_char
获得小时数，获取年月日与此类似
备注
10. 处理月份天数不定的办法
函数
描述
Last_day
处理月份天数不定的办法
实例
备注
11、找出今年的天数
函
数
描
述
实
例
备
注
闰年的处理方法
to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )
如果是28就不是闰年
add_months，trunc
找出今年的天数
12、不同时区的处理
函数
描述
实例
NEW_TIME
不同时区的处理
备注
13、 5秒钟一个间隔
函数
描述
实例
FLOOR，TO_CHAR
5秒钟一个间隔
备注 SSSSS表示5位秒数
14、一年的第几天
函数
描述
实例
TO_CHAR
一年的第几天
备注
15、计算小时,分,秒,毫秒
TRUNC
函数
描述
实例
计算小时,分,秒,毫秒
备注
16、add_months()
函数
描述
实例
add_months()
用于从一个日期值增加或减少一些月份
备注
17、current_date()
函数
描述
实例
current_date()
返回当前会放时区中的当前日期，date_value:=current_date
备注
18、current_timestamp()
函数
描述
实例
current_timestamp()
以timestamp with time zone数据类型返回当前会放时区中的当前日期
timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])
备注
19、dbtimezone()
函数
描述
实例
dbtimezone()
返回时区 ，varchar_value:=dbtimezone
备注
20、extract()
函数
描述
实例
extract()
找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
备注
21、last_day()
函数
描述
last_day()
返回包含了日期参数的月份的最后一天的日期，date_value:=last_day(date_value)
实例
备注
22、localtimestamp()
函localtimestamp()
数
描返回会话中的日期和时间，timestamp_value:=localtimestamp
述
实
例
备
注
23、months_between()
函数
描述
实例
months_between()
判断两个日期之间的月份数量
number_value:=months_between(date_value,date_value)
备注
_day函数
函next_day
数
描next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
述
实1.查询某周的第一天
例
备
2.查询某周的最后一天
注
select trunc(decode(ww, 53, to_date(yy || '3112', 'yyyyddmm'), to_date(yy || '-' || to_char(ww * 7),
'yyyy-ddd')), 'd') - 6 first_day
from (select substr('2004-33', 1, 4) yy, to_number(substr('2004-33', 6)) ww
from dual)；
select trunc(to_date(substr('2003-01',1,5)||to_char((to_number(substr('2003-01',6)))*7),
'yyyy-ddd'),'d') last_day from dual；
select max(v_date) from
(select (to_date('200408','yyyymm') + rownum) v_date
from all_tables
where rownum < 370)
where to_char(v_date,'yyyy-iw') = '2004-33'；
3.查询某周的日期
select min_date, to_char(min_date,'day') day from
(select to_date(substr('2004-33',1,4)||'001'+rownum-1,'yyyyddd') min_date
from all_tables
where rownum <= decode(mod(to_number(substr('2004-33',1,4)),4),0,366,365) union
select to_date(substr('2004-33',1,4)-1||
decode(mod(to_number(substr('2004-33',1,4))-1,4),0,359,358)+rownum,'yyyyddd') min_date
from all_tables
where rownum <= 7 union
select to_date(substr('2004-33',1,4)+1||'001'+rownum-1,'yyyyddd') min_date
from all_tables
where rownum <= 7 )
where to_char(min_date,'yyyy-iw') ='2004-33'；
五、转换函数
1、to_char 将DATE或者BUMBER 转换为字符串（参看日期函数）
2、to_date 将nmber、char、vachar2转换为date（参看日期函数）
3、to_number将char、varchar2转换为number
函数
to_number
描述
将char、varchar2转换为number
实例
备注
六、变换函数
1、TRANSLATE
函数
TRANSLATE
描述
逐个字符替换
实例
备注
2、decode
DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表
相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应
结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，
则函数也有默认的返回值。区别于SQL的其它函数，DECODE函数还能识别
和操作空值. 其具体的语法格式如下：
DECODE(input_value,value,result[,value,result…][,default_result]);
其中： input_value 试图处理的数值。DECODE函数将该数值与一系列的序
偶相比较，以决定最后的返回结果，value 是一组成序偶的数值。如果输入数
值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关
键字NULL于之对应 ，result 是一组成序偶的结果值 ，default_result 未能与
任何一序偶匹配成功时，函数返回的默认值
下面的例子说明了，如何读取用户CHECKUP表SEAPARK中的
BLOOD_TEST_FLAG列下的项目，作为DECODE函数的实参支持值。
SELECT checkup_type,
DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)
FROM checkup;
七、分组函数
1、AVG(DISTINCT|ALL)
函数
AVG(DISTINCT|ALL)
描述
all表示对所有的值求平均值,distinct只对不同的值求平均值
实例
备注
2、MAX(DISTINCT|ALL)
函数
MAX(DISTINCT|ALL)
描述
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求
最大值,相同的只取一次
实例
备注
3、MIN(DISTINCT|ALL)
函数
MIN(DISTINCT|ALL)
描述
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求
最小值,相同的只取一次
实例
备注
4、STDDEV(distinct|all)
函数
STDDEV(distinct|all)
描述
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值
求标准差
实例
备注
5、VARIANCE(DISTINCT|ALL)
函数
VARIANCE(DISTINCT|ALL)
描述
求协方差
实例
备注
6、GROUP BY
函数
GROUP BY
描述
主要用来对一组数进行统计
实例
备注
7、HAVING
函数
HAVING
描述
对分组统计再加限制条件
实例
备注
8、ORDER BY
函数
ORDER BY
描述
用于对查询到的结果进行排序输出
实例
备注
发布评论