Oracle函数

字符函数

select ASCII('sa'), ASCII('s') fromdual;                                             --返回单个字符的ASCII码

select CONCAT('ad',123), CONCAT('Dad','asdDF') fromdual;                              --拼接括号内的字符

select INSTR('test','t',3,1), INSTR('test','t',3,2) fromdual;                         --INSTR(x,str[,start][,n]):在x中查找str，可以指定从start开始，也可以指定从n次开始

select LENGTH(13213), LENGTH('sdfsdf') fromdual;                                      --返回括号里的字符长度

select LOWER('sdfSDFSD') , LOWER(4564) fromdual;                                     --将内容转换为小写

select UPPER('sdfSDFSD') , LOWER(4564) fromdual;                                     --将内容转换为大写

select '=='|| LTRIM('=====hello====','='), LTRIM('=====hello====','===2'), LTRIM('  =====hello====') fromdual;     --LTRIM(x[,trim_str])在x的左边截去trim_str，缺省截去空格

select RTRIM('====hello==','=')||'==', RTRIM('====hello==','=12') fromdual;          -- RTRIM(x[,trim_str])在x的右边截去trim_str，缺省截去空格

select '1'|| TRIM('  ==   ')||'2', TRIM('==  ') fromdual;                            --截去左右两边空格

select REPLACE('qweqwe','qw','123'), REPLACE('qweqwe','qW')fromdual;                 --REPLACE(x,old,new)在x中查找old，并替换为new,整体比较

select TRANSLATE('jaclc','jscd',16), TRANSLATE('jaclc','cjsd',1614)fromdual;         --TRANSLATE（char,from,to）按字符匹配,char中的单个字符在from中匹配，返回from匹配字符位置的to位置字符，匹配不到舍去

select SUBSTR('QWEEFRF',2), SUBSTR('WQWEWEE',2,0), SUBSTR('WQWEWEE',-8,10) fromdual;    --SUBSTR(x,start[,length])返回x的字符，从start开始，截取length个字符，缺省length默认到结尾，start是负数的时候从右边开始截去start长度，start超过字符长度和length<=0返回空白

数字函数

select ABS(-85) fromdual;                                                            --取绝对值

select ACOS(1), ACOS(0.3), ACOS(0.6) fromdual;                                       --取反余弦

select COS(10), COS(0.2) fromdual;                                                    --去余弦

select CEIL(5.4), CEIL(5.5), CEIL(5.0) fromdual;                                     --取大于或等于本身的最小整数值

select FLOOR(5.4),FLOOR(5.9),FLOOR(5.0) fromdual;                                    --取小于或等于本身的最大整数值

select LOG(2,2), LOG(3,9), LOG(9,3)fromdual;                                          --LOG(x,y)取x为底 的对数

select MOD(2,3), MOD(5,2), MOD(5,0)from dual;                                         --MOD(x,y)取x除以y的余数,y可以为0

select POWER(2,3), POWER(4,5) fromdual;                                               --POWER(x,y)x的y次幂

select ROUND(1.6356), ROUND(1.6356,3), ROUND(1.6356,6), ROUND(116.6356,-1) fromdual;    --ROUND(x[,y])x在第y位四舍五入，缺省y四舍五入整数，y大于小数点位数全部保留，y可以为负数，当为负数的时候，意味着整数部分四舍五入,并从第y位四舍五入

select SQRT(4), SQRT(3), SQRT(2.5), SQRT(0) fromdual;                                   --返回平方根

select TRUNC(3.12345,2), TRUNC(3.66345,1), TRUNC(3.66345) fromdual;                    --TRUNC(x,y)x在第y位直接截断，并且不会四舍五入，缺省y保留整数

日期函数

select ADD_MONTHS(sysdate,5), ADD_MONTHS(sysdate,20) fromdual;                        --返回在原日期上加上月后的新日期

select sysdate, LAST_DAY(sysdate) fromdual;                                           --返回指定日期的最后一天

select ROUND(sysdate,'day'), ROUND(sysdate,'year'), ROUND(sysdate) fromdual;           --ROUND(d[,fmt])一fmt条件对日期四舍五入，缺省fmt对当天四舍五入

select sysdate, EXTRACT(MONTH from sysdate) fromdual;                                 --EXTRACT（fmt from d）提取日期中fmt部分

select lpad(round(dbms_random.value(1,999999999)),9,0)from dual;
lpad(9,0)是个左补0函数，如果选出的随机数为1，则前边就是8个0补充

转换函数

TO_CHAR(d|n[,fmt])                                                                    

selectTO_CHAR(sysdate,'yyyy-MM'),TO_CHAR(32,'9'),TO_CHAR(32.32,'$') fromdual;

TO_DATE(x[,fmt])

select TO_DATE('2009-8-3','yyyy-MM-dd') from dual;

TO_NUMBER(x[,fmt])

select TO_NUMBER('-$1346.5','$0000.0') from dual;

聚合函数

AVG,SUM,MIN,MAX,COUNT

其他单行函数

1. NVL(x,value):如果x为空，返回value，否则返回x;

2. NVL2(x,value1,value2):如果x为null返回value2，否则返回value1（x==null?value2:value1）;

select t.*,NVL2(comm,'123',456) from scott.emp t;

3. DECODE(x,if1,then1,if2,then2,...,else):如果x的值是if1就返回then1，如果是if2就返回then2...否则返回else的值

select DECODE(sal,800,900,1000,2000,0) fromscott.emp;