一、多行函数:COUNT 、AVG 、SUM 、MIN 、MAX
二、单行函数:数值函数、字符函数、转换函数、日期函数、杂项函数
1、数值函数
(1) ABS(number_param) 用途:返回绝对值
举例:SelectAbs(-2)From dual; 结果:2
(2) CEIL(number_param) 用途:返回大于参数的最小整数
举例:SELECTCeil(1.5)FROM dual; 结果:2
(3) FLOOR(number_param) 用途:返回小于参数的最小整数
举例:SELECTfloor(2.7)FROM dual; 结果:2
(4) ROUND(number_param1,number_param2) 用途:按参数2指定小数位数,对参数1进行截取小数,保留参数2指定的小数位数,考虑“四舍五入”;
举例:SelectRound(2.5678,2)From dual; 结果:2.57
(5) TRUNC(number_param1,number_param2) 用途:按参数2指定小数位数,对参数1进行截取小数,保留参数2指定的小数位数,不考虑“四舍五入”;
举例:SELECTTrunc(2.5678,2)FROM dual; 结果:2.56
(6) MOD(number_param1, number_param1) 用途:返回参数1除参数2后的余数
举例:SELECTMod(5,3)FROM dual; 结果:2
(7) SIGN(number_param) 用途:返回如果参数值小于0,则返回-1,如果参数值大于0,则返回1,如果参数值为0,则返回0
举例:SELECTSign(4)FROM dual; 结果:1
SELECTSign(0)FROM dual; 结果:0
SELECTSign(-4)FROM dual; 结果:-1
SELECTSign(4-1)FROM dual; 结果:1
2、字符函数
(1) LENGTH(string_param) 用途:返回字符长度,每个数字、符号、大小写英文字母、汉字长度都是1
举例:SELECTlength('hjksklg12344AA赛意')FROM dual; 结果:16
SELECTlength('12344')FROM dual; 结果:5
SELECTlength('hjksklg')FROM dual; 结果:7
SELECTlength('AA')FROM dual; 结果:2
SELECTlength('赛意')FROM dual; 结果:2
(2) LENGTHB(string_param) 用途:返回字符按字节计算长度,每个数字、符号、大小写英文字母字节长度为1,但汉字字节长度与数据库字符集有关,在GBK和USASCII字符集下每个汉字字节长度是2,在UTF8下每个汉字字节长度是3
举例:SELECTLengthb('hjksklg12344AA赛意')FROM dual; 结果:18
SELECTLengthb('12344')FROM dual; 结果:5
SELECTLengthb('hjksklg')FROM dual; 结果:7
SELECTLengthb('AA')FROM dual; 结果:2
SELECTLengthb('赛意')FROM dual; 结果:4
(3) LOWER(string_param) 用途:将字符转为小写,参数字符串中可包含非字符,如数字,汉字,特殊符号等,执行后该函数后,英文字母全部转为小写
举例:SELECTLower('hjksklg12344AA赛意')FROM dual; 结果:hjksklg12344aa赛意
(4) UPPER(string_param) 用途:将字符转为大写,参数字符串中可包含非字符,如数字,汉字,特殊符号等,执行后该函数后,英文字母全部转为大写
举例:SELECTUPPER('hjksklg12344AA赛意')FROM dual; 结果:HJKSKLG12344AA赛意
(5) REPLACE(string_param1,string_param2,string_param3) 用途:将参数1字符串中的参数2字符串替换为参数3字符串
举例:SELECTReplace('hjjkgkahdlab','hj','HJ')FROM dual; 结果:HJjkgkahdlab
SELECTReplace('hjjkgkahdlab','j','J')FROM dual; 结果:hJJkgkahdlab
SELECTReplace('hjjkgkahdlab','hj')FROM dual; 结果:jkgkahdlab (直接删除hj,无参数3替代)
(6) LTRIM(string_param1[,string_param2]) 用途:从左边开始,将参数1字符串中的参数2字符串包含的每个字符都去除,直至遇到第一个不在参数2字符串中的字符而终止,并返回参数1余下的字符串;若不指定参数2,则默认为空格(单引号之间有一个空格)
举例:SELECTLtrim('1112234fj34nddg4231','f1234')FROM dual;
结果:j34nddg4231
SELECTLtrim(' 1112234 fj34nd dg42 31 ')FROM dual;
结果:1112234 fj34nddg42 31
(7) RTRIM(string_param1[,string_param2]) 用途:从右边开始,将参数1字符串中的参数2字符串包含的每个字符都去除,直至遇到第一个不在参数2字符串中的字符而终止,并返回参数1余下的字符串;若不指定参数2,则默认为空格(单引号之间有一个空格)
举例:SELECTrtrim('1112234fj34nddg4231','f1234')FROM dual;
结果:1112234fj34nddg
SELECTrtrim(' 1112234 fj34nd dg42 31 ')FROM dual;
结果: 1112234 fj34nddg42 31
(8) INSTR(string_param1,string_param2[,pos,nth]) 用途:从pos位置开始从左往右,在string_param1找string_param2,第nth次出现的位置,若为指定pos和nth默认值均为1
举例:SELECTInstr('abcdbcefbcfgadbc','bc')FROM dual;
结果:2
SELECTInstr('abcdbcefbcfgadbc','bc',1,1)FROM dual;
结果:2
SELECTInstr('abcdbcefbcfgadbc','bc',1,2)FROM dual;
结果:5
SELECTInstr('abcdbcefbcfgadbc','bc',7,1)FROM dual;
结果:9
SELECTInstr('abcdbcefbcfgadbc','bc',7,2)FROM dual;
结果:15
(9) SUBSTR(string_param,start_position[,length]) 用途:在字符串string_param中,从start_position位置,从左往右截取length长度的字符,不指明length参数,则截取剩余全部
举例:SELECTSubstr('abcdbcefbcfgadbc',7,2)FROM dual;
结果:ef
SELECTSubstr('abcdbcefbcfgadbc',7)FROM dual;
结果:efbcfgadbc
SELECT Substr('AA,BB,CC,DD',INSTR('AA,BB,CC,DD',',')+4)FROM dual; 结果:CC,DD
(10) LPAD(string_param1,number_param[,string_param2])用途:在string_param中,从左边第1位开始,截取number_param参数长度的子串,如果number_param小于 string_param的长度,则返回截取的子串,如果number_param大于 string_param的长度,在string_param的左边补string_param2中从左往右的字符,直至长度等于number_param
举例:SELECTLpad('adfge',3,'abc')FROM dual; 结果:adf
SELECTLpad('adfge',7,'abc')FROM dual; 结果:abadfge
SELECTLpad('adfge',7,'&&')FROM dual; 结果:&&adfge
(11) RPAD(string_param1,number_param[,string_param2]) 用途:在string_param中,从右边第1位开始,截取number_param参数长度的子串,如果number_param小于 string_param的长度,则返回截取的子串,如果number_param大于 string_param的长度,在string_param的右边补string_param2中从左往右的字符,直至长度等于number_param
举例:SELECTrpad('adfge',3,'abc')FROM dual; 结果:adf
SELECTrpad('adfge',7,'abc')FROM dual; 结果:adfgeab
SELECTrpad('adfge',7,'&&')FROM dual; 结果:adfge&&
3、转换函数
(1)TO_NUMBER(param) 用途:将字符转为数字型
举例:SELECTTo_Number('1998')FROM dual; 结果:1998
TO_CHAR(string_param)
TO_CHAR(date_param[,format,nlsparams])
将数字转为字符
将日期类转为字符型,按一定格式
举例:SELECTTo_Char(2011)FROM dual; 结果:2011
SELECTto_char(sysdate,'yyyy-mm-dd')FROM dual; 结果:2011-03-28
(2) TO_DATE(string_param[format,nls_params]) 用途:将字符类转为日期型,按一定格式
举例:SELECT to_date('2011-03-28','yyyy-mm-dd')FROM dual;
结果:2011/3/28
SELECT to_date('2011-03-28 21:52:25','yyyy-mm-dd hh24:mi:ss')FROM dual; 结果:2011/3/28 21:52:25
4、日期函数
(1) SYSDATE 用途:返回当前系统时间,数据库所在服务器时间
举例:SELECTSysdateFROM dual; 结果:2011/3/2822:00:48
(2) TRUNC(date_param) 用途:返回截取时分秒的日期
举例:SELECTtrunc(Sysdate)FROM dual; 结果:2011/3/28
(3) NEXT_DAY(date_param,’DAY_OF_WEEK’) 用途:返回参数1时间起,第一个参数2“星期几”的日期
举例:SELECT next_day(Sysdate,'星期一')FROM dual;
结果:2011/4/4 22:10:38
(4) ADD_MONTHS(date_param,number_param) 用途:返回参数1时间起增加参数2 个月后的日期,主要处理2月的28号,30天和31天的月份差异
举例:SELECTadd_months(Sysdate,1.5)FROM dual;
结果:2011/4/28 22:20:31
SELECT add_months(Sysdate,1)FROM dual;
结果:2011/4/28 22:20:31
(5) MONTHS_BETWEEN(date_param1, date_param2) 用途:返回参数1时间起,增加参数2的月份差异,参数1比参数2小,返回负数,否则返回整数;同时,如果参数1和参数2都是同一天或者都是月底,返回整数
举例:SELECTmonths_between(to_date('2011-03-28','yyyy-mm-dd'),to_date('2011-01-28','yyyy-mm-dd'))FROMdual;结果:2
SELECT months_between(to_date('2011-03-28','yyy-mm-dd'),to_date('2011-05-28','yyyy-mm-dd'))FROMdual;结果:-2
SELECT months_between(to_date('2011-03-28','yyyy-mm-dd'),to_date('2011-03-28','yyyy-mm-dd'))FROMdual; 结果:0
5、杂项函数
(1) GREATEST(nuber_param1[,number_param2,number_param3,number_param4…])
用途:返回参数列表中的最大值,如果所有参数类型不同,从第2个开始都转为第一个参数的类型,进行比较
举例:SELECTGreatest(2,8.2,7)FROM dual; 结果:8.2
(2) LEAST(nuber_param1[,number_param2,number_param3,number_param4…])
用途:返回参数列表中的最小值,若果所有参数类型不同,从第2个开始都转为第一个参数的类型,进行比较
举例:SELECTLeast(2,8.2,7)FROM dual; 结果:2
(3) NVL(param1,param2) 用途:如果参数1为空,则返回参数2,否则返回参数1;参数1和参数2类型必须一致
举例:SelectNvl('efeabc','none')From dual; 结果:efeabc
SelectNvl(Null,'none')From dual; 结果:none
SelectNvl('','none')From dual; 结果:none
(4) DECODE(expession,value1,result1[,value2,result2…] [,default result])
用途:如果expression值为value1则返回result1,如果expression值为value2则返回result2…都不满足,返回default result
DECODE相当于IF … ELSIF…ELSIF… ELSE
DECODE不能直接给变量赋值,只能在SQL里使用(SELECT INTO 也可以)
举例:SELECTDecode(Sign(15000-10000),1,'A',0,'B',-1,'C')FROM dual; 结果:A
SELECTDecode(Sign(10000-10000),1,'A',0,'B',-1,'C')FROM dual; 结果:B
SELECTDecode(Sign(1000-10000),1,'A',0,'B',-1,'C')FROM dual; 结果:C