2 Oracle常用函数及使用技巧.doc

Oracle常用函数及使用技巧 1. 日期函数 ADD_MONTHS：增加或减去月份l SQL select to_char(add_months(to_date(199912,yyyymm),2),yyyymm) from dual;输出：200002l SQL select to_char(add_months(to_date(199912,yyyymm),-2),yyyymm) from dual;输出：199910 EXTRACT:从一个日期或时间型的字段内抽取年、月、日、时、分、秒数据l SQLselect extract(year from sysdate) from dual;输出：当前的年份 l SQLselect extract(month from sysdate) from dual;输出：当前的月份 l SQLselect extract(day from sysdate) from dual;输出：当前是几号 l SQLselect extract(hour from sysdate) from dual;输出：当前是几时l SQLselect extract(minute from sysdate) from dual;输出：当前是几分l SQLselect extract(second from sysdate) from dual;输出：当前是几秒 LAST_DAY：返回指定日期所在月份的最后一天l SQL select to_char(sysdate,yyyy.mm.dd),to_char(sysdate)+1,yyyy.mm.dd) from dual; （假设现在是2009年5月9日）输出：2009.05.09 和 2009.05.10l SQL select last_day(sysdate) from dual; （假设现在是2009年5月）输出：31-5月 -09 MONTHS_BETWEEN(date2,date1):给出date2-date1的月份l SQL select months_between(19-12月-1999,19-3月-1999) from dual;输出：9l SQLselectmonths_between(to_date(20000520,yyyymmdd),to_date(20050520,yyyymmdd) from dual;输出：-60 NEXT_DAY(date,day)：给出日期date和星期x之后计算下一个星期的日期l SQL select next_day(18-5月-2001,星期五) from dual;输出：25-5月 -01 SYSDATE：得到系统的当前日期l SQL select to_char(sysdate,dd-mm-yyyy day) from dual; （假设现在是2009年5月9日）输出：09-05-2009 星期六 TRUNC(date,fmt)：按照给出的要求将日期截断,如果fmt=mi表示保留分,截断秒l SQL select to_char(trunc(sysdate,hh),yyyy.mm.dd hh24:mi:ss) ,to_char(trunc(sysdate,mi),yyyy.mm.dd hh24:mi:ss) from dual; （假设现在是2009年5月9日）输出：2004.05.09 11:00:00 和2004.05.09 11:17:001.1 日期格式参数日期格式参数含义说明D一周中的星期几DAY天的名字，使用空格填充到9个字符DD月中的第几天DDD年中的第几天DY天的简写名IWISO标准的年中的第几周IYYYISO标准的四位年份YYYY四位年份YYY,YY,Y年份的最后三位，两位，一位HH小时，按12小时计HH24小时，按24小时计MI分SS秒MM月Mon月份的简写Month月份的全名W该月的第几个星期WW年中的第几个星期1.2日期时间间隔操作 当前时间减去5分钟的时间l SQLselect sysdate - interval 5 minute from dual; 当前时间减去5小时的时间l SQLselect sysdate - interval 5 hour from dual; 当前时间减去5天的时间l SQLselect sysdate - interval 5 day from dual; 当前时间减去5月的时间l SQLselect sysdate - interval 5 moth from dual; 当前时间减去5年的时间l SQLselect sysdate - interval 5 year from dual; 时间间隔乘以一个数字l SQL select sysdate - 8 *interval 2 hour from dual;2. 字符函数 INITCAP：返回字符串并将字符串的第一个字母变为大写l SQL select initcap(smith) from dual;输出：Smith LOWER：返回字符串,并将所有的字符小写l SQL select lower(AaBbCcDd) from dual;输出：aabbccdd UPPER：返回字符串,并将所有的字符大写l SQL select upper(AaBbCcDd) from dual;输出：AABBCCDD LTRIM和RTRIM：删除左边出现的某字符串和删除右边出现的某字符串l SQL select ltrim(rtrim(gao qian jing , ),g) from dual;输出：ao qian jing REPLACE(string,s1,s2)：字符串替代l String：希望被替换的字符或变量s1：被替换的字符串s2：要替换的字符串大小写敏感l SQL select replace(he love you,he,i) from dual;输出：i love you INSTR(C1,C2,I,J)：在一个字符串中搜索指定的字符,返回发现指定的字符的位置l C1：被搜索的字符串C2：希望搜索的字符串I：搜索的开始位置,默认为1J：出现的位置,默认为1l SQL select instr(oracle traning,ra,1,2) from dual;输出：9 SUBSTR(string,start,count)：取子字符串,从start开始,取count个l 使用时未提供长度count，表示从起始位置开始截取到字符串的末尾l 如果起始位置start是负值，则表示从字符串的右边开始算起，进行截取l SQL select substr3,8) from dual;输出：08888888 CHR：返回给出整数对应的字符l SQL select chr(65) from dual;输出：A ASCII：返回与指定的字符对应的十进制数l SQL select ascii(A) from dual; 输出：65 RPAD和LPAD：在列的右边粘贴字符和在列的左边粘贴字符l SQL select lpad(rpad(gao,10,*),17,*)from dual;输出：*gao* LENGTH：返回字符串的长度l SQL select length(ABC) from dual;输出：3 CONCAT：连接两个字符串;l SQL select concat(010-,88888888) from dual;输出3. 数字函数 ABS：返回指定值的绝对值l SQL select abs(-100) from dual;输出：100 CEIL：返回大于或等于给出数字的最小整数l SQL select ceil(3.1415927) from dual;输出：4 FLOOR：对给定的数字取整数l SQL select floor(2345.67) from dual; 输出：2345 SQRT：返回数字的平方根l SQL select sqrt(64) from dual;输出：8 POWER(n1,n2)：返回n1的n2次方根l SQL select power(3,3) from dual;输出：27 ROUND：按照指定的精度进行四舍五入l SQL select round(55.53,1) from dual; 输出：55.5 TRUNC：按照指定的精度截取一个数l SQL select trunc(124.1666,-2) from dual; 输出：100 EXP：返回数字e的n次方根l SQL select exp(2) from dual;输出：7.3890561 LN：返回一个数字的对数值l SQL select ln(1) from dual; 输出：0 LOG(n1,n2)：返回一个以n1为底n2的对数 l SQL select log(2,4) from dual; 输出：2 MOD(n1,n2)：返回一个n1除以n2的余数l SQL select mod(10,3) from dual; 输出：1 SIGN(n)：取数字n的符号,大于0返回1,小于0返回-1,等于0返回0l SQL select sign(123) from dual; 输出：14. 转换函数 TO_CHAR(date,format)：日期转换为字符格式l SQL select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual; （假设现在是2009年5月9日21时14分41秒）输出：2004/05/09 21:14:41 TO_DATE(string,format)：将字符串转化为日期格式l SQL select to_date(2005/05/09,yyyy/mm/dd) from dual; TO_NUMBER：将给出的字符转换为数字l SQL select to_number(1999) from dual;输出：19995. 分组函数 AVG(DISTINCT|ALL)：求平均值，ALL表示对所有的值求平均值, DISTINCT只对不同的值求平均值l SQL create table table3(xm varchar(8),sal number(7,2);SQL insert into table3 values(gao,1111.11);SQL insert into table3 values(gao,1111.11);SQL insert into table3 values(zhu,5555.55);SQL commit;l SQL select avg(distinct sal) from table3;输出：3333.33l SQL select avg(all sal) from table3; 输出：2592.59 MAX(DISTINCT|ALL)：求最大值，ALL表示对所有的值求最大值，DISTINCT表示对不同的值求最大值，相同的只取一次 MIN(DISTINCT|ALL)：求最小值，ALL表示对所有的值求最小值，DISTINCT表示对不同的值求最小值，相同的只取一次 SUM (DISTINCT|ALL)：求和，ALL表示对所有的值求和, DISTINCT只对不同的值求和 COUNT (DISTINCT|ALL)：求