PL/SQL内置函数

截取指定字符位置之前字符串:substr('abc(DEF)',1,instr('abc(DEF)')-1)

内置函数

一、多行函数：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