oracle函数

/*四舍五入round（n[,m]）缺省m：0m>0:小数点后m位m<0:小数点前m位*/select round(23.4),round(23.45,1),round(23.45,-1) from dual;/*取整ceil 取最大   上限floor 取最小 下限*/select ceil(23.45),floor(23.45) from dualselect abs(23.45),abs(-23),abs(0) from dual/*mod（m，n）取余 m，n有一个null就返回null*/select mod(23,2) from dualselect power(2,3),power(null,2) from dualselect sqrt(16) from dual/*三角函数sin asin cos acostan atan弧度*/select sin(5) from dual--upper小写转大写、lower大写转小写、initcap首字母大写select upper('adbe'),lower('aDe'),initcap('asd') from dual/*substr获取自字符串函数substr(char,[m[,n]])n可以省略，当n省略时表示从m的位置截取到字符串末尾m为0，表示从字符串的首字母开始截取m为负数时，表示从字符串的尾部开始截取*/select substr('abcde',2,3),substr('abcded',2),substr('abcde',-2,1)from dual--length获取字符串长度select length('acd') from dual/*字符串连接函数concat（char1，char2）与||操作符作用一样*/select concat('ab','cd'), 'ab'||'cd'from dual/*去除字串函数trim(c2 from c1)从字符串c1中去掉字符c2*/select trim('a' from 'abcdea') from dual/*ltrim(c1,[,c2])从左边去掉c2*/select ltrim('aabababa','a') from dual/*rtrim(c1,[,c2])从右边去掉c2*/select rtrim('aabababaa','a') from dual--trim(c1)去掉空格--替换函数replace--replace (char,s_string,[,r_string])省略r_string用空格替换select replace（'abcde','a','A'） from dual--sysdate 系统当前日期select sysdate from dual--add_months系统当前加select add_months(sysdate,2),add_months(sysdate,-2) from dual--next_day(date,char)下一个select next_day(sysdate,'星期一') from dual--last_day(date)select last_day(sysdate) from dual--months_between(date1,date2)日期差select months_between('1-1月-15',sysdate) from dual--extract(date from datetime)select extract(year from sysdate) from dualselect extract(day from sysdate) from dualselect extract(hour from timestamp '2015-10-1 13:00:00') from dualselect extract(second from timestamp '2015-10-1 13:22:22') from dual--转换函数/*日期转换字符函数to_char(data[,fmt[,params]])参数说明data：将转换的日期fmt：转换的格式params：默认DD-MON-RRYY YYYY YEARMM MONTHDD　DAYHH24 HH12MI SS*/select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual/*字符转换日期函数to_date(char[,fmt[,params]])*/select to_date('2015-05-22','YYYY-MM-DD') from dual/*数字转换为字符函数to_char(number[,fmt])9:显示数字忽略前面的00：显示数字，位数不足用0补足.或d：显示小数点，或g：显示千分位$:显示美元符号S：加正负号（前后都可以）*/select to_char('12345.78','$99,999.999')from dual/*字符转数字to_number(char[,fmt])*/select to_number ('$1,000','$9999') from dual