oracle+基本内置函数,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;