5fc84738508132963.jpg_fo742.jpg

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:59:59....

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
描述 两个日期间的天数
实例  
备注  

 

  1. 时间为null的用法
函数 To_date
描述 To_date(null)
实例  
备注  

 

6、组合用法

函数 To_date
描述 查找2008-07-28至2008-07-01间除星期一和七的天数
实例  
备注  

 

  1. 7.  months_between
函数 months_between
描述 计算日期
实例  

 

备注  

 

  1. 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; (未成功执行)

备注 ** 使用TO_CHAR(SYSDATE,'D')可以求得当前日期是一周的第几天,得到的结果是星期日开始作为第1天的,那么星期一就是第2天,星期日就是第8天 */

/** SUBSTR代替DECODE函数,  NEXT_DAY 函数可以指定当前日期的下一个星期几的日期 ,比如:今天是11-25日,星期六,那么 NEXT_DAY(sysdate,'星期一') , 得到的日期就是11-27,那么这个星期一的日期就是 11-27 减去 7天, 而星期日的日期就是11-27减去1天 */

 

9、获得时间

函数 To_char
描述 获得小时数,获取年月日与此类似
实例  
备注  

 

  1. 10.  处理月份天数不定的办法
函数 Last_day
描述 处理月份天数不定的办法
实例  
备注  

 

11、找出今年的天数

函数 add_months,trunc
描述 找出今年的天数
实例  
备注 闰年的处理方法

to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )

如果是28就不是闰年

 

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)

实例  
备注  

 

24.next_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
实例  
备注  

 

六、变换函数

  • TRANSLATE
函数 TRANSLATE
描述 逐个字符替换

 

实例  
备注  

 

  • 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
描述 用于对查询到的结果进行排序输出
实例  
备注  

 

发表评论

您的电子邮箱地址不会被公开。

Captcha Code