/*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 languageSELECT SUBSTR('structured query language',12) AS VALUE FROM dual; --2.左起第12位开始截取，长度为5:querySELECT SUBSTR('structured query language',12,5) AS VALUE FROM dual; --3.右起第8位开始截取到最后:languageSELECT SUBSTR('structured query language',-8) AS VALUE FROM dual; --3.右起第8位开始截取,长度为5:languageSELECT 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*salWHEN 'MANAGER' THEN 1.30*salWHEN 'ANALYST' THEN 1.40*salELSE sal END "Salary"FROM empORDER 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 empORDER 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;
