Oracle常用的函数.docxVIP

常用函数：sysdate为系统日期 dual为虚表日期函数[重点掌握前四个日期函数]1.add_months[返回日期加(减)指定月份后(前)的日期]select sysdate S1,add_months(sysdate,10) S2,add_months(sysdate,5) S3 from dual;2.last_day [返回该月最后一天的日期] select last_day(sysdate) from dual; 3.months_between[返回日期之间的月份数]select sysdate S1, months_between(1-4月-04,sysdate) S2,months_between(1-4月-04,1-2月-04) S3 from dual4.next_day(d,day): 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日 select sysdate S1,next_day(sysdate,1) S2,next_day(sysdate,星期日) S3 FROM DUAL 5.round[舍入到最接近的日期](day:舍入到最接近的星期日)select sysdate S1,round(sysdate) S2 ,round(sysdate,year) YEAR,round(sysdate,month) MONTH ,round(sysdate,day) DAY from dual6.trunc[截断到最接近的日期] select sysdate S1,trunc(sysdate) S2,trunc(sysdate,year) YEAR,trunc(sysdate,month) MONTH ,trunc(sysdate,day) DAY from dual7.返回日期列表中最晚日期select greatest(01-1月-04,04-1月-04,10-2月-04) from dual 字符函数(可用于字面字符或数据库列)1.字符串截取select substr(abcdef,1,3) from dual2.查找子串位置select instr(abcfdgfdhd,fd) from dual3.字符串连接select HELLO||hello world from dual;4. 1)去掉字符串中的空格select ltrim( abc) s1,rtrim(zhang ) s2,trim( zhang ) s3 from dual2)去掉前导和后缀select trim(leading 9 from 9998767999) s1,trim(trailing 9 from 9998767999) s2,trim(9 from 9998767999) s3 from dual;5.返回字符串首字母的Ascii值select ascii(a) from dual6.返回ascii值对应的字母select chr(97) from dual7.计算字符串长度 select length(abcdef) from dual8.initcap（首字母变大写） ,lower(变小写),upper(变大写)select lower(ABC) s1,upper(def) s2, initcap(efg) s3 from dual; 9.Replaceselect replace(abc,b,xy) from dual;10.translateselect translate(abc,b,xx) from dual; -- x是1位11.lpad [左添充] rpad [右填充](用于控制输出格式)select lpad(func,15,=) s1, rpad(func,15,-) s2 from dual;select lpad(dname,14,=) from dept;12.decode[实现if ..then 逻辑]select deptno,decode(deptno,10,1,20,2,30,3,其他) from dept; 数字函数1.取整函数（ceil 向上取整,floor 向下取整）select ceil(66.6) N1,floor(66.6) N2 from dual;2. 取幂(power) 和 求平方根(sqrt)select power(3,2) N1,sqrt(9) N2 from dual;3.求余select mod(9,5) from dual;4.返回固定小数位数 (round:四舍五入，trunc:直接截断)select