oracle函数大全

<style type="text/css"> .tt { font-weight: bold; font-size: 16px; color: #F00; } </style> <style type="text/css"> <!-- .t2 { color: #00F; font-weight: bold; font-size: 24px; } --> </style>

数字函数

1.1、ABS(n)函数
描述：返回数值n的绝对值。

Examples：select abs(-15) “test” from dual;

1.2、ACOS(n)函数
描述：返回数值n的反余弦值。输入数值范围在-1~1之间，返回值为弧度。

Examples：select acos(0.6) “test”,acos(-.6) “test1” from dual;

1.3、COS(n)函数
描述: 返回数值n的余弦值。返回值为弧度。

Examples：select cos(1.6) “test”,cos(-6) “test1” from dual;

1.4、SIN(n)函数
描述: 返回数值n的正弦值。

Examples：select sin(1.6) “test”,sin(-6) “test1” from dual;

1.5、ASIN(n)函数
描述: 返回数值n的反正弦值。输入数值范围在-1~1之间，返回值为弧度。

Examples：select asin(0.6) “test”,asin(-0.6) “test1” from dual;

1.6、TAN(n)函数
描述: 返回数值n的正切值。

Examples：select tan(6) “test”,tan(-0.6) “test1” from dual;

1.7、ATAN(n)函数
描述: 返回数值n的反正切值。输入数值任意，返回值为弧度。

Examples：select atan(6) “test”,atan(-0.6) “test1” from dual;

1.8、ATAN2(n,m)函数
描述: 返回数值n/m的反正切值。输入数值任意，返回值为弧度。

Examples：select atan2(19,3) “test”,atan2(-9,-0.9) “test1” from dual;

1.9、SINH(n)函数
描述: 返回数值n的双曲正弦值。输入数值任意。

Examples：select sinh(6) “test”,sinh(-0.6) “test1” from dual;

1.10、TANH(n)函数
描述: 返回数值n的双曲正切值。输入数值任意。

Examples：select tanh(6) “test”,tanh(-0.6) “test1” from dual;

1.11、CEIL(n)函数
描述: 返回大于等于数值n的最小整数。

Examples：select ceil(6) “test”,ceil(6.6) “test1” from dual;

1.12、COSH(n)函数
描述: 返回数值n的双曲余弦值。

Examples：select cosh(6) “test”,cosh(6.6) “test1” from dual;

1.13、EXP(n)函数
描述: 返回e的n次冥。(e=2.71828183…)

Examples：select exp(6) “test” from dual;

1.14、FLOOR(n)函数
描述: 返回小于等于数值n的最大整数。

Examples：select floor(6) “test”,floor(9.3) “test1” from dual;

1.15、LN(n)函数
描述: 返回数值n的自然对数。(n必须大于0)

Examples：select ln(6) “test” from dual;

1.16、LOG(m,n)函数
描述: 返回以m为底的数值n的对数。(m>1,n>0)

Examples：select log(6,3) “test” from dual;

1.17、MOD(m,n)函数
描述: 返回m/n后的余数，若n=0，则返回m（求模运算）

Examples：select mod(6,3) “test” from dual;

1.18、POWER(m,n)函数
描述: 返回m的n次冥

Examples：select power(6,3) “test” from dual;

1.19、ROUND(n,[m])函数
描述: 执行四舍五入运算，m可以省略，当省略m时，四舍五入到整数位；当m为正数时，四舍五入到小数点后m位；当m为负数时，四舍五入到小数点前m位。

Examples：select round(6.698,2) “test” from dual;

1.20、SIGN(n)函数
描述: 检测数值的正负，当n<0则返回-1；当n>0则返回1，当n=0返回0。

Examples：select sign(6.698) “test”,sign(-9) “test1”,sign(0) “test2” from dual;

1.21、SQRT(n)函数
描述: 返回数值n的平方根。(n>=0)

Examples：select sqrt(6.698) “test” from dual;

1.22、TRUNC(n,[m])函数
描述: 截取数值n,m可以省略，当省略m时则截取n的小数部分；当m为正数时则将n截取到小数点后m位；当m为负数时则将n截取到小数点前m位

Examples：select trunc(6.698,2) “test”,trunk(696.3,-2) “test1” from dual;

字符函数

说明：字符函数输入值为字符类型，返回值为字符类型或数字类型，可以在sql语句中直接使用，也可以在pl/sql块中使用。

2.1、ASCII(n)函数
描述: 返回字符串的ascii码(当输入为字符串时返回第一个字符的ascii码)

Examples：select ascii(‘A’) “test”,ascii(‘我们’) “test1” from dual;

2.2、CHR(n)函数
描述: 返回对应的ascii码的字符(n必须为数字类型)

Examples：select ascii(54992) “test” from dual;

2.3、CONCAT(n,m)函数
描述: 连接n和m，n和m可以是字符，也可以是字符串。作用和”||”一样。

Examples：select concat(‘中国’,’人民’) “test” from dual;

2.4、INITCAP(n)函数
描述: 将字符串n中每个单词首字母大写，其余小写(区分单词的规则是按空格或非字母字符；可以输入中文字符，但没有任何作用)

Examples：select initcap(‘中 国 人 民’) “test”,initcap(‘my word’) “test1”,initcap(‘my中国word’) “test2” from dual;

2.5、INSTR(chr1,chr2,[n,[m]])函数
描述: 获取字符串chr2在字符串chr1中出现的位置。n和m可选,省略是默认为1；n代表开始查找的起始位置，当n为负数从尾部开始搜索；m代表字串出现的次数。

Examples：select instr('pplkoopijk','k',-1,1) “test” from dual;

备注：当n为负数从尾部搜索，但返回值仍然是按正向排列得出的位置。

2.6、LENGTH(n)函数
描述: 返回字符或字符串长度。(当n为null时，返回nll；返回的长度包括后面的空格)

Examples：select length('ppl ') “test”,length(null) “test1” from dual;

2.7、LOWER(n)函数
描述: 将n转换为小写。

Examples：select lower('KKKD') “test” from dual;

2.8、LPAD(chr1,n,[chr2])函数
描述: 在chr1左边填充字符chr2，使得字符总长度为n。chr2可选，默认为空格；当chr1字符串长度大于n时，则从左边截取chr1的n个字符显示。

Examples：select lpad('kkk',5) “test”,lpad(‘kkkkk’,4) “test1”,lpad(‘kkk’,6,’lll’) “test2” from dual;

2.9、LTRIM(chr,[n])函数
描述: 去掉字符串chr左边包含的n字符串中的任何字符，直到出现一个不包含在n中的字符为止。

Examples：select ltrim('abcde',’a’) “test”,ltrim(‘abcde’,’b’) “test1”,ltrim(‘abcdefg’,’cba’) “test2” from dual;

2.10、NLS_INITCAP(chr,[’nls_param’])函数
描述: 将chr首字母大写。Nls_param可选，指定排序的方式。（有SCHINESE_RADICAL_M（部首、笔画），SCHINESE_STROKE_M（笔画、部首），SCHINESE_PINYIN_M（拼音））

Examples：select nls_initcap('ab cde') “test”,nls_initcap(‘a b c d e’,’nls_sort= SCHINESE_PINYIN_M’) “test1” from dual;

2.11、NLS_LOWER(chr,[‘nls_param’])函数
描述: 将字符串转换为小写。Nls_param可选，指定排序的方式。（有SCHINESE_RADICAL_M（部首、笔画），SCHINESE_STROKE_M（笔画、部首），SCHINESE_PINYIN_M（拼音））

Examples：select nls_lower('ABC') “test”,nls_lower(‘ABC’,’nls_sort= SCHINESE_PINYIN_M’) “test1” from dual;

2.12、NLSSORT(col,[’nls_param’])函数
描述: 根据nls_param指定的方式对col字段进行排序。

Examples：SELECT part_number FROM cux_om_part_all ORDER BY nlssort(part_number,'nls_sort=SCHINESE_RADICAL_M')

2.13、NLS_UPPER(chr,[‘nls_param’])函数
描述: 将chr转换为大写。Nls_param可选，用于指定排序规则

Examples：SELECT nls_upper('ddddd','nls_sort=xdanish') FROM dual

2.14、REGEXP_REPLACE(source_string,pattern,replace_string,position,occurtence,match_parameter)函数(10g新函数)
描述:字符串替换函数。相当于增强的replace函数。Source_string指定源字符表达式；pattern指定规则表达式；replace_string指定用于替换的字符串；position指定起始搜索位置；occurtence指定替换出现的第n个字符串；match_parameter指定默认匹配操作的文本串。

其中replace_string,position,occurtence,match_parameter参数都是可选的。

2.15、REGEXP_SUBSTR(source_string, pattern[,position [, occurrence[, match_parameter]]])函数(10g新函数)
描述：返回匹配模式的子字符串。相当于增强的substr函数。Source_string指定源字符表达式；pattern指定规则表达式；position指定起始搜索位置；occurtence指定替换出现的第n个字符串；match_parameter指定默认匹配操作的文本串。

其中position,occurtence,match_parameter参数都是可选的

Examples:select regexp_substr(‘http://www.oracle.com/products’,’http://([[:alnum:]]+\.?) {3,4} / ?’) “regexp_substr” from dual

2.16、REGEXP_LIKE(source_string, pattern
[, match_parameter])函数(10g新函数)
描述：返回满足匹配模式的字符串。相当于增强的like函数。Source_string指定源字符表达式；pattern指定规则表达式；match_parameter指定默认匹配操作的文本串。

其中position,occurtence,match_parameter参数都是可选的

Examples:

2.17、REGEXP_INSTR(source_string, pattern
[, start_position
[, occurrence
[, return_option
[, match_parameter]]]])函数(10g新函数)
描述: 该函数查找 pattern ，并返回该模式的第一个位置。您可以随意指定您想要开始搜索的 start_position。 occurrence 参数默认为 1，除非您指定您要查找接下来出现的一个模式。return_option 的默认值为 0，它返回该模式的起始位置；值为 1 则返回符合匹配条件的下一个字符的起始位置

Examples:

 

附注：上面红色标题的四个函数是oracle 10g才有的函数，使用正则表达式可以实现很强大的功能。鉴于变化太多，可以参考oracle的官方文档：SQL Reference(第七章)
2.18、REPLACE(chr,search_string,[,replacement_string])函数
描述:将chr中满足search_string条件的替换为replacement_string指定的字符串，当search_string为null时，返回chr；当replacement_string为null时，返回chr中截取掉search_string部分的字符串。

Examples: SELECT REPLACE('abcdeef','e','oo') "test",REPLACE('abcdeef','ee','oo') "test1",REPLACE('abcdeef',NULL,'oo') "test2",REPLACE('abcdeef','ee',NULL) "test3" FROM dual

2.19、RPAD(chr1,n,chr2)函数
描述：在chr1右边填充chr2，使返回字符串长度为n..当chr1长度大于n时，返回左端n个字符。参考LPAD()函数。

2.20、RTRIM(chr,[set])函数
描述：去掉chr右边包含的set中的任何字符，直到出现一个不是set中的字符结束。参考LTRIM()函数。

2.21、SOUNDEX(chr)函数
描述：返回字符串的语音表示，可以用来比较字符串的发音是否相同。

Examples:select soundex(‘ship’) “test”,soundex(‘sleep’) “test1” from dual;

2.22、SUBSTR(chr,m[,n])函数
描述：取chr的子串。M代表开始位置，n是要取的长度。当m为0时从首字符开始，当m为负时从字符串尾部开始截取。

Examples:select substr(‘abcdef’,0,3) “test”,substr(‘abcdef’,1,3) “test1”,substr(‘abcdef’,-3,3) “test2”,substr(‘abcdef’,-1,3) “test3” from dual

注意：m取0或1时，开始位置是一样的，都是从第一位开始，m为负的时候，仍然是按从左到右的顺序取，所以如果m为-1，n的长度再大，也只能取到最后一个字符，因为chr右边已经没有字符了。

2.23、TRANSLATE(chr,from_str,to_str)函数
描述：另一种替换函数的用法。

Examples: SELECT translate('abcdeabc','abc','fgh') "test",translate('abcdeabc','abc','hf') "test1",translate('abcdeabc','ab','hfgh') "test2",translate('abcdeabc','abc',' ') "test3" FROM dual

注意：匹配的规则是from_str和to_str每个字符按顺序相对应，如果from_str字符少于to_str中的字符，则只替换能对应的字符，to_str后面不能和from_str对应的字符则不管，如果from_str字符多于to_str字符，则from_str中找不到对应字符按照null来处理。

2.24、TRIM(chr)函数
TRIM函数将字符串的前缀（或尾随）字符删除。

其具体的语法格式如下：

TRIM（[LEADING|TRAILING|BOTH][trimchar FROM] string）

其中：

LEADING 指明仅仅将字符串的前缀字符删除

TRAILING 指明仅仅将字符串的尾随字符删除

BOTH 指明既删除前缀字符，也删除尾随字符。这也是默认方式

string 任意一待处理字符串

trimchar 可选项。指明试图删除什么字符，默认被删除的字符是空格

下面是该函数的使用情况：

TRIM（’ Ashley ’）=‘Ashley’

TRIM（LEADING ’*’ FROM’***Ashley***’）=‘Ashley***’

2.25、UPPER(chr)函数
UPPER函数间返回字符串的大写形式。

其具体的语法格式如下：

UPPER（string）

其中：

string 任意VARCHAR2或CHAR型字符串

下面是该函数的使用情况：

UPPER（’THIS IS a Test’）=‘THIS IS A TEST’

日期函数

3.1、add_months(d,n)
说明：用于从一个日期值增加或减少一些月份,d代表一个日期,n为正数则代表在d日期 上增加n月份，n为负数则代表在d日期上减少n月
例:select add_months(sysdate,12) "Next Year" from dual;
3.2、current_date()

说明:返回当前会话时区中的当前日期时间
alter session set time_zone=’-11:00’(更改当前会话时区命令)
例:select sessiontimezone,current_date from dual;
3.3、dbtimezone()

说明:返回数据库实例时区
select dbtimezone from dual;
3.4、extract()

说明:显示指定格式的日期值。
select extract(month from sysdate) "This Month" from dual;
select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;
3.5、last_day()

说明:返回包含了日期参数的月份的最后一天的日期
select last_day(sysdate) "last" from dual;
3.6、months_between(d1,d2)

说明:返回d1和d2两个月份之间相差的月数，若d1<d2,返回负数；d1>d2，返回正数；若d1和d2都是月底或者天数相同，则返回整数，否则以每月31天为基准数返回小数。
select months_between(to_date('2007-01-31','yyyy-mm-dd'),to_date('2006-11-30','yyyy-mm-dd')) from dual;
3.7、next_day(d,varchar2)

说明:返回日期d指定的在日期d之后的第一个工作日；

SELECT next_day(SYSDATE,'星期四') FROM dual;

Select next_day(sysdate,’monday’) from dual; 错误，不能使用英文单词

返回当前日期后的第一个星期四的日期。若当前日期已经是星期四或过了星期四，则返回下周的星期四对应的日期，否则返回本周星期四的日期。

注意：varchar2指定工作日的时候和当前数据库实例的参数设置有关，字符串需要用中文写，如果中文不行，就使用英文星期代表。

3.8、round(d,fmt)
说明：返回日期时间的四舍五入结果。如果fmt指定年，则以7月1日为分界；如果指定月，则以16日为分界；关于按天来四舍五入，在测试时无法理解(资料上解释按天的时候是以中午12：00为分界)

Select round(sysdate,’month’) from dual;

3.9、trunc(d,fmc)
说明：按照指定的格式截断日期，如果指定格式为年，则结果为本年1月1日，如果格式指定为月，则结果为本月1日，关于格式指定为天还未理解。

SELECT trunc(SYSDATE,'month') FROM dual;

3.10、sysdate

应用：
1. 日期和字符转换函数用法（to_date,to_char）

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串
select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年
select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月
select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日
select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时
select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分
select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual//

2. select to_char( to_date(222,'J'),'Jsp') from dual

显示Two Hundred Twenty-Two

3.求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
星期一
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
monday
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')

4. 两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;

5. 时间为null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;

注意要用TO_DATE(null)

6.月份差
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以，当时间需要精确的时候，觉得to_char还是必要的

7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
8.
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
not in ( '1', '7' )

查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).

9. 查找月份
select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1
select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1.03225806451613

10. Next_day的用法
Next_day(date, day)

Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D

11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意：第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
create or replace function sys_date return date is
begin
return sysdate;
end;

select to_char(sys_date,'hh:mi:ss') from all_objects;

12.获得小时数
extract()找出日期或间隔值的字段值
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from dual;

SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07

SQL> select sysdate ,to_char(sysdate,'hh24') from dual;

SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19

13.年月日的处理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days

from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,
newer_date,
older_date
from (
select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date
from emp
)
)

14.处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual

16.找出今年的天数
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual

闰年的处理方法
to_char( last_day( to_date('02' | | :year,'mmyyyy') ), 'dd' )
如果是28就不是闰年

17.yyyy与rrrr的区别
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001

18.不同时区的处理
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from dual;

19.5秒钟一个间隔
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
from dual

2002-11-1 9:55:00 35786
SSSSS表示5位秒数

20.一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual

310 2002-11-6 10:03:51

21.计算小时,分,秒,毫秒
select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
)

select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');

//
floor((date2-date1) /365) 作为年
floor((date2-date1, 365) /30) 作为月
d(mod(date2-date1, 365), 30)作为日.

23.next_day函数 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六

---------------------------------------------------------------

select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
日期 返回的是天 然后 转换为ss

24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual

25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
select sysdate S1,
trunc(sysdate) S2, //返回当前日期,无时分秒
trunc(sysdate,'year') YEAR, //返回当前年的1月1日,无时分秒
trunc(sysdate,'month') MONTH , //返回当前月的1日,无时分秒
trunc(sysdate,'day') DAY //返回当前星期的星期天,无时分秒
from dual

26,返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual

27.计算时间差
注:oracle时间差是以天数为单位,所以换算成年月,日

select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual //时间差-年
select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual //时间差-月
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual //时间差-天
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual //时间差-时
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual //时间差-分
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒

28.更新时间
注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-年
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual //改变时间-月
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-时
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-分
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-秒

29.查找月的第一天,最后一天
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual;

转换函数

4.1、asciistr(str)
说明：将任意字符集的字符串转换为当前数据库实例对应的ascii字符串。

SELECT asciistr('中华民族') FROM dual;

备注：和该函数相似的有ascii,它是取得字符串第一个字符的ascii码，后面的字符不管；chr是将ascii码转换为对应的字符。

4.2、cast()
说明：将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型。

SELECT CAST(SYSDATE AS VARCHAR2(100)) FROM dual;

4.3、chartorowid(str)
说明：将字符串转变为rowid数据类型，但字符串必须符合rowid格式。

4.4、convert(str,char_set,source_char_set)
说明：将字符串从一个字符集转变为另一个字符集。Str为要转变的字符串，char_set为转变后的字符集，source_char_set为该字符串原始的字符集。

SELECT convert('中国','US7ASCII','ZHS16GBK') FROM dual;

4.4、rowidtochar(rowid)
说明：将rowid转换为对应的varchar2数据类型值。

4.5、to_char()
select to_char(n’中国’) from dual; 转变为本地字符集类型

select to_char(10000,’L99G999D99MI’) from dual; 转换成货币格式

4.6、to_date()
转变为日期格式。

4.7、to_number()
将字符串转变为数字值，但字符串必须是数字(0~9)。

单行函数

decode(exer.search1,result1[search2,result2,...][,defalut])
用于匹配特定表达式的结果，如果search1匹配于exer，则返回result1，如果search2匹配于exer则返回result2，
依次类推，如果没有任何匹配关系则返回defalut

分组函数

6.1AVG 取平均值
Select AVG(SAL) From EMP

6.2max 取最大值
select max(sal) from emp

6.3min 取最小值
select min(sal) from emp

6.4count 取记录数
SELECT COUNT(*) FROM EMP
也可以
select count(empno) from emp
注意:
要么count(*)
要么count(没有null值的列---主键)

 

6.5sum 求和
Select SUM(SAL*12) From EMP

应用：

1.分组函数和distinct关键词的搭配
Select COUNT(Distinct SAL) From EMP

 

2.组函数与空值
select count(comm) from emp
空值不参与运算,直接被过滤掉

 

3.分组数据
group by子句
SELECT COUNT(*) FROM EMP GROUP BY DEPTNO

4.使用group by规则
A.group by后面的字段不必显示在select列表中
B.反之则不行
也就是说:select后面的字段必须在group by子句中出现
例外的是:
在组函数(count,max,min等)中出现的字段除外

5.关于group by条件分组的问题
A.group by可以和where来搭配
where只能在group by的前面
Select JOB From EMP Where SAL>2000 Group By JOB
group by后面不能有where

B.where子句中不能包括组函数
条件的表达只能使用having来表示
Select DEPTNO,Max(SAL),Min(SAL) From EMP Group By DEPTNO Having Max(SAL)>2000

对象函数