ORACLE 内置基本函数

/*

ORACLE 内置基本函数

*/



--【把字符串转换成小写】
SELECT LOWER('Structured Query Language') AS VALUE FROM dual; 

--【把字符串转换成大写】
SELECT UPPER('Structured Query Language') AS VALUE FROM dual; 

--【把字符串的首字母大写】
SELECT INITCAP('structured  query language') AS VALUE FROM dual; 

--【连接两个字符串】
SELECT CONCAT ('Structured Query Language','is easy to learn!') AS VALUE FROM dual; 

--【字符串截取】
--1.左起第12位开始截取到最后:query language
SELECT SUBSTR('structured query language',12) AS VALUE FROM dual; 
--2.左起第12位开始截取，长度为5:query
SELECT SUBSTR('structured query language',12,5) AS VALUE FROM dual; 
--3.右起第8位开始截取到最后:language
SELECT SUBSTR('structured query language',-8) AS VALUE FROM dual; 
--3.右起第8位开始截取,长度为5:language
SELECT SUBSTR('structured query language',-8,8) AS VALUE FROM dual; 

--【计算字符串中的字符个数】
SELECT LENGTH('structured query language') AS VALUE FROM dual; 

--【第一个字符串中，出现第二个字符串的都替换为第三个字符串】
SELECT replace ('Structured Query Language','Structured Query','sql') AS VALUE FROM dual;  

--【小数取整】
SELECT FLOOR(12345.6789) AS VALUE FROM dual; 

--【保留N位小数并四舍五入，默认取整】
SELECT round(12345.6789,3) AS VALUE FROM dual; 

--【保留N位小数不四舍五入】
SELECT trunc (12345.6789,3) AS VALUE FROM dual; 

--【求余数】
SELECT mod(100,40) AS VALUE FROM dual; 

--【如果第一个为空值则设为0】
SELECT  empno,ename,sal,comm,sal+NVL(comm,0) AS VALUE FROM emp; 

--【如果第一个不为空值，取第二个，为空则取三个】
SELECT  empno,ename,sal,comm,nvl2(comm,sal+comm,sal) AS VALUE FROM emp; 

--【如果二者相等则返回空值,否则返回第一个的值】
SELECT  empno,ename,sal,comm,NULLIF(sal,sal) AS VALUE,NULLIF(sal,comm) AS VALUE FROM emp; 

--【回该函数中第一个不为空的值】
SELECT  COALESCE(comm,sal+comm,sal) AS VALUE FROM emp; 
SELECT  COALESCE(null,'','KK') AS VALUE FROM emp; 

--【CASE 表达式使用】
SELECT ename,job,sal,
  CASE job WHEN 'SALESMAN' THEN 1.20*sal
    WHEN 'MANAGER' THEN 1.30*sal
    WHEN 'ANALYST' THEN 1.40*sal
  ELSE sal END "Salary"
  FROM emp
  ORDER BY job; 

--【DECODE 表达式使用,与上面的case 结果一样】
SELECT ename,job,sal,DECODE(job,'SALESMAN',1.20*sal,'MANAGER',1.30*sal,'ANALYST',1.40*sal,sal) "Salary"
FROM emp
ORDER BY job; 


--【返回指定的字符的十进制数】
select ascii('A') A,ascii('a'),ascii('黄') a from dual;

--【返回ascii对应的字符】
select chr(65) A,chr(97) a,chr(48070) huang from dual;

--【求n次方】
select power(2,10) AS VALUE from dual;


--【日期字符串转化为某种日期格式】
select to_date('2013-09-08','yyyy-mm-dd') AS VALUE from dual;

--【字符类型转换为数字类型】
select to_number('20130908') AS VALUE from dual;

--【返回一组表达式中的最小值】
select least('吃','吧','阿','腌') from dual;

--【返回系统的当前日期】可加减天数
SELECT sysdate,sysdate+7,sysdate-7,sysdate + 20/24 FROM dual; 

--【增减月份】
select to_char(add_months(to_date('2013-09-08','yyyy-mm-dd'),2),'yyyy-mm-dd') AS VALUE from dual;

--【当前日期最后一天】
select last_day(sysdate) AS VALUE from dual;

--【第一个日期月第二个日期相差的月数】
select months_between(to_date('2013-09-08','yyyy-mm-dd'),to_date('2012-09-08','yyyy-mm-dd')) AS VALUE from dual;

--【给出日期后计算下一个星期几的日期】
select next_day('08-9月-2013','星期日') AS VALUE from dual;

--【将ROWID类型转换为字符类型】
select rowid,rowidtochar(rowid),ename from emp;