PLSQL常用内置函数(自己整理)

1．转换函数:

1.1 TO_DATE：

功能：将字符串变量根据模式格式串转换为日期变量

格式：TO_DATE(string[,format]):

年份：

用Y表示:如四位年份表示为YYYY,三位为YYY，两位为YY

YYYY

四位的年份

YYY

三位年份

YY

两位年份

 

月份：

MM

编号为01-12的月份编号

MONTH

9个字符表示的月份

MON

三个字符表示的月份的缩写

 

日：

D

表示星期几，取值为1-7

DD

月份中的某天，取值为1-31

 

小时：

HH，或HH12

一天中的小时，取值为1-12

HH24

一天中的小事，取值为0-23

 

分钟：

MM

小时中的分钟数，0-59

 

秒：

SS

一份中的秒数，0-59

 

如：

select to_date('03/31/2008','MM/DD/YYYY') from dual

select to_date('2008-03-31 04:30:00','YYYY-MM-DDHH24:MI:SS') from dual

 

 

1.2TO_CHAR:

功能：将日期变量或数值变量转换为字符串变量

格式：TO_CHAR(value,format)，其中value可以是数值，也能是日期变量。

其中数值型的格式中用9表示的整数部分则高位的0不显示，0表示则显示：

select to_char(32.5,'000.99') from dual/*显示结果为：032.50*/

/*其中小数点也可以用D表示*/

select to_char(32.5,’000d99’) from dual

select to_char(32.5,'999.99') from dual /*显示结果为：32.50*/

select to_char(sysdate,'yyyy-mm-ddhh24:mi') from dual

 

 

 

1.3．TO_NUMBER:

功能：将字符串转换为数值型变量

格式：TO_NUMBER(value,format)

select to_number('456.432','9999D999') from dual

 

1.4．TO_TIMESTAMP:

功能：将字符串转换为时间戳变量，使用方法与TO_DATE相似。

 

1.5 CAST(value AS type):

功能：将一个变量值转换为第二个参数的类型

例如：selectcast('03-4月-2008' asDATE) FROM DUAL;

 

 

2．日期函数：

2.1ADD_MONTHS(a_date DATE,a_number NUMBER):

将当前的日期a_date增加a_number个月份并返回该日期。

 

2.2CURRENT_DATE():

返回会话时区中的当前日期

 

2.3CURRENT_TEMPSTAMP():

返回会话时区中的当前时间戳

 

2.4EXTRACT():

从给定的表达式中返回制定的日期时间字段

例如：

select extract(YEAR from current_timestamp) from dual

select extract(MONTH from current_timestamp) from dual

select extract(DAY from current_timestamp) from dual

select extract(HOUR from current_timestamp) from dual

select extract(MINUTE from current_timestamp) from dual

select extract(SECOND from current_timestamp) from dual

 

2.5LAST_DAY(a_date DATE):

返回a_date所在月份最后一天的日期

select LAST_DAY(TO_DATE('20080305','YYYYMMDD')) FROM DUAL

/*则返回2008-3-31*/

 

2.6MONTHS_BETWEEN(a_date1 DATE,a_date2 DATE):

返回两个日期间相距的月份

如：

select months_between(to_date('20080131','yyyymmdd'),to_date('20080101','yyyymmdd')) from dual

/*返回0.967741935483871，如果第一个日期比第二个日期早则返回负数*/

 

2.7 NEXT_DAY(a_dateDATE,a_weekday VARCHAR2):

返回从当前第一个日期a_date起的下一个周几（第二个参数）的日期。

其中，第二个参数的取值取决于当前的语言环境，如果为american,则第二个参数取值为：

‘SUNDAY’,’MONDAY’,’TUESDAY’,’WENDNSDAY’,’THRUSDAY’,’FRIDAY’,’SATURDAY’

例如：

select next_day(sysdate,'MONDAY') fromdual;

如果当前语言环境为’SIMPLIFIEDCHINESE’，则第二个参数可以的取值为：

‘星期一’， ‘星期二’，‘星期三’， ‘星期四’，‘星期五’， ‘星期六’，‘星期日’

例如：

select next_day(sysdate,'星期一') from dual;

而当前的nls_language可以通过select * from v$NLS_PARAMETERS查询得到。

 

2.8SYSDATE

返回系统当前日期和时间

 

2.9SYSTIMESTAMP

以TIMESTAMP数据类型返回系统当前日期和时间

 

2.10TRUNC

按照制定的精度截取日期值

例如：

select trunc(sysdate,'year') from dual;

则返回01-1月 -08

select trunc(sysdate,'month') from dual;

select trunc(sysdate,'DAY') from dual;

 

3．杂项单行函数：

decode(expr,search1,result1[,search2,result2,……][default]):

decode函数有些类似于case操作符，逐一扫描表达式expr，如果能匹配第一个查询条件（search1）,返回给定的值(result1).如果表达式不能匹配所有所有的查询条件，此时如果提供了默认值，则返回默认值，否则返回null。

Select decode(1,1,1,2,,2,0) from dual;/*返回１*/

Select decode(3,1,1,2,,2,0) from dual;/*返回0*/

 

Least(expr1,expr2……):

Least函数返回表达式列表中的最小值

select least(1,2,3,4,-4,5,6) from dual;/*返回－４*/

 

nullif(expr1,expr2):

nullif函数中，如果expr1和expr2相等则返回null,否则返回expr1.

select nullif(1,2) from dual;--返回1
select nullif(1,1) fromdual;--返回null

 

nvl(expr,value):

如果expr不为null则返回expr；如果expr为null，则返回value。

 

Nvl2(expr,value1,value2):

如果expr不为空则返回value1,否则返回value2

select nvl2(3,1,2) from dual;--1
select nvl2(null,1,2) from dual;--2

 

4．字符串函数：

Chr(a_numnumber):

返回给定整数对应的字符。

select chr(65) fromdual;--A

 

concat(str1,str2):

连接两个给定的字符串。

select concat('abc','def') from dual;--abcdef

 

initcap(str):

返回字符串，并且字符串的每个单词的首字母为大写。

select initcap('i love you') from dual;--I Love You

 

lower(str):

返回字符串的小写形式。

select lower('I LOVE YOU') from dual;--i love you

upper函数与此相反，返回字符串

 

lpad(expr1,n[,expr2]):

lpad函数将输入串expr1左边填充以达到长度n。如果可选项expr2给出，则expr1左边填充内容为expr2；若expr2为给出，则使用默认值空白字符。如果expr1的长度大于n，则将expr1长度截取左边的n个字符并返回。

select lpad('i love you',13,'abcd') from dual;--abci love you
select lpad('i love you',6,'abcd') from dual ;--i love
select lpad('i love you',13) from dual --   i love you

 

 

ltrim(expr1[,set]):

将expr1字符串左边在集合set中出现的字符去掉，如果set省略则删除expr1左边的空格。

select ltrim('+=+=abcdiakjsdlkf','+=') from dual;--abcdiakjsdlkf

select ltrim('   abcdiakjsdlkf') from dual;--abcdiakjsdlkf

 

rpad,rtrim与lpad,ltrim相似：

 

substr(str1,pos,len):

对字符串str1，从第pos个位置起返回长度为len的子串。对于pos小于0时，则从字符串的右边计算起始位置。

select substr('i love you',3,2) from dual;-- lo
select substr('i love you',-3,2) from dual; --yo

 

instr(C1,C2[,I,J])

在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 出现的位置,默认为1

SQL> select instr(’oracletraning’,’ra’,1,2) instring from dual;

 

trim(str1):

返回str1字符串去处左右两边空格后的字符串。

 

Length(str1):

返回字符串str1的长度。

select length(trim('    i love you    ')) fromdual; --10

 

replace(str1,str2[,str3]):

在str1中用str3替换str2子串。如果str3缺省，则直接删除str1中的所有子串str2。

select replace ('aaabbbcccdddeeefff','ccc','XXX') from dual;--aaabbbXXXdddeeefff
select replace ('aaabbbcccdddeeefff','ccc') from dual;--aaabbbdddeeefff

 

5．数值型函数：

Abs(num):

返回num的绝对值。

 

Mod(num1,num2):

返回num1整除num2的余数。

 

Nanvl(m,n):

如果m不是数字则返回n。

Power(m,n):

返回m的n次方。

 

 

Round(m,n):

M是一个实数，而n是整数，表示对小数点后面的n位进行四舍五入操作。如果n为负数，则对小数点右边的n位进行四舍五入操作。

select round(44.35,-1) fromdual;--40
select round(44.35,1) fromdual;--44.4

 

sign(num):

如果num小于0则返回-1，大于0返回1，等于0返回0。

 

Trunk(num1,num2):

按照指定的精度num2截取数num1。与round相似，但是直接截取。

select trunc(44.567,1) fromdual;--44.5
select trunc(44.567,-1) from dual;--40

 

ceil(num):

天花板函数。

Floor(num):

地板函数。

select floor(44.5) from dual;--44
select ceil(44.5) from dual;--45