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-DD HH24:MI:SS') from dual
1.2 TO_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,’000d 99’ ) from dual
select to_char(32.5,'999.99') from dual /*显示结果为:32.50*/
select to_char(sysdate,'yyyy-mm-dd hh24: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):
功能:将一个变量值转换为第二个参数的类型
例如:select cast('03-4月-2008' as DATE) FROM DUAL;
2.日期函数:
2.1 ADD_MONTHS(a_date DATE,a_number NUMBER):
将当前的日期a_date增加a_number个月份并返回该日期。
2.2 CURRENT_DATE():
返回会话时区中的当前日期
2.3 CURRENT_TEMPSTAMP():
返回会话时区中的当前时间戳
2.4 EXTRACT():
从给定的表达式中返回制定的日期时间字段
例如:
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.5 LAST_DAY(a_date DATE):
返回a_date所在月份最后一天的日期
select LAST_DAY(TO_DATE('20080305','YYYYMMDD')) FROM DUAL
/*则返回 2008-3-31 */
2.6 MONTHS_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_date DATE,a_weekday VARCHAR2):
返回从当前第一个日期a_date起的下一个周几(第二个参数)的日期。
其中,第二个参数的取值取决于当前的语言环境,如果为american,则第二个参数取值为:
‘SUNDAY’,’MONDAY’,’TUESDAY’,’WENDNSDAY’,’THRUSDAY’,’FRIDAY’,’SATURDAY’
例如:
select next_day(sysdate,'MONDAY') from dual;
如果当前语言环境为’SIMPLIFIED CHINESE’,则第二个参数可以的取值为:
‘星期一’, ‘星期二’, ‘星期三’, ‘星期四’, ‘星期五’, ‘星期六’, ‘星期日’
例如:
select next_day(sysdate,'星期一') from dual;
而当前的nls_language可以通过select * from v$NLS_PARAMETERS查询得到。
2.8 SYSDATE
返回系统当前日期和时间
2.9 SYSTIMESTAMP
以TIMESTAMP数据类型返回系统当前日期和时间
2.10 TRUNC
按照制定的精度截取日期值
例如:
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;/*返回1*/
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;/*返回-4*/
nullif(expr1,expr2):
nullif函数中,如果expr1和expr2相等则返回null,否则返回expr1.
select nullif(1,2) from dual;--返回1
select nullif(1,1) from dual;--返回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_num number):
返回给定整数对应的字符。
select chr(65) from dual;--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(’oracle traning’,’ra’,1,2) instring from dual;
trim(str1):
返回str1字符串去处左右两边空格后的字符串。
Length(str1):
返回字符串str1的长度。
select length(trim(' i love you ')) from dual; --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) from dual;--40
select round(44.35,1) from dual;--44.4
sign(num):
如果num小于0则返回-1,大于0返回1,等于0返回0。
Trunk(num1,num2):
按照指定的精度num2截取数num1。与round相似,但是直接截取。
select trunc(44.567,1) from dual;--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