oracle 实用函数整理

任2519

下面整理出一些比较实用的函数，函数功能易懂，在这就不一一说明。
select ascii(' ') space from dual;

select chr(54740) zhao from dual;

select concat('010-','88888999')||'转23' from dual;

select initcap('smith') as "upp" from dual;

select length('dummy') from dual;

select lower('AaBbCcDd') as "lower" from dual;

select upper('AaBbCcDd') as "upper" from dual;

select rpad('gao',10,'*') from dual;

select lpad('gao',10,'*') from dual;

select rtrim(' Hello World ',' ') from dual;

select ltrim(' Hello World',' ') from dual;

select trim(' Hello World ') from dual;

select abs(100),abs(-100) from dual;

select ceil(3.1415926) from dual;

select ceil(-3.1415926) from dual;

select floor(3.1415926) from dual;

select floor(-3.1415926) from dual;

select exp(2),exp(1) from dual;

select log(2,1),log(2,4) from dual;   

select mod(10,3),mod(3,3) from dual;

select power(2,10),power(3,3) from dual;

select round(55.5),round(-55.4),trunc(55.5),trunc(-55.3) from dual;

select trunc(123.444,2),trunc(123.555,-3) from dual; --以小数点为轴，负的向左

select sign(123),sign(-100),sign(0) from dual; --取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

select sqrt(64),sqrt(2) from dual;

select to_char(add_months(to_date('1999-12','yyyy-mm'),2),'yyyy/mm') from dual;

select sysdate from dual;

select last_day(to_date('2011-5-8','yyyy-mm-dd')) from dual;

select months_between('1-3月-1999','8-5月-2011') from dual;

select next_day('17-3月-2009','星期五') from dual;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select to_number('1999') year from dual;

select substr('不是我的错',2,4) from dual; --2表示从第二个字符开始取，4表示取四个字符|

DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value 等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。
select decode(abs(-1),1,'ss','yy') from dual