Oracle_分类函数大全

  sre

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

 

LEAVE A COMMENT

Captcha Code