ELSE EMP.SALARY
END
-----内置函数
----字符函数
----LOWER
SELECT LOWER(EMP.NAME) FROM EMPLOYEE EMP ;
---UPPER
SELECT UPPER(EMP.NAME) FROM EMPLOYEE EMP;
---SUBSTR
SELECT SUBSTR('123456',2,2) FROM DUAL;
SELECT SUBSTR('123456',2) FROM DUAL;
SELECT INSTR('ABCABC','AB') FROM DUAL;
-----INITCUP
SELECT INITCAP('aaabbgb') FROM DUAL;
----CONCAT
SELECT CONCAT(EMP.NAME,EMP.HIREDATE) FROM EMPLOYEE EMP;
---LTRIM
SELECT LTRIM('AAABCAA','AA') FROM DUAL;
SELECT LTRIM('A12AAABCDAAA12','A12') FROM DUAL;
SELECT LTRIM('A12AAABCDAAA12','12') FROM DUAL;
---TRIM
SELECT TRIM(' AFASFDA AAA ') FROM DUAL;
----REPLACE
SELECT REPLACE('AAABBBBBBBAAA','A','XY') FROM DUAL;
-----TRIM
SELECT TRIM('A' FROM 'ABAXYZABAbAAAA') FROM DUAL;
---LENGTH
SELECT LENGTH('ABC大大') FROM DUAL;
----数字函数
--ROUND
SELECT ROUND(-5.678,2), ROUND(5.678,2) FROM DUAL;
SELECT ROUND(1.678,-1) FROM DUAL;
--TRUNC
SELECT TRUNC(5.687,2) FROM DUAL;
--POWER
SELECT POWER(2,3) FROM DUAL;
--ABS
SELECT ABS(-12.69) FROM DUAL;
--MOD
SELECT MOD(12,7) FROM DUAL;
--FLOOR
SELECT FLOOR(15.4 ) FROM DUAL;
SELECT FLOOR(-15.4) FROM DUAL;
---CEIL
SELECT CEIL(15.4) FROM DUAL;
SELECT CEIL(-15.4) FROM DUAL;
----SQRT
SELECT SQRT(4) FROM DUAL;
-----日期函数
SELECT SYSDATE FROM DUAL;
--加两天
SELECT SYSDATE + 2 FROM DUAL;
----加四个小时
SELECT SYSDATE + 2/12 FROM DUAL;
---加了两个小时
SELECT SYSDATE + 2/24 FROM DUAL;
---加了两分钟
SELECT SYSDATE + 2/1440 FROM DUAL;
--日期相减
SELECT TO_DATE('2009-5-9','YYYY-MM-DD') - TO_DATE('2008-5-9','YYYY-MM-DD') FROM DUAL;
---Add_moths
SELECT ADD_MONTHS(TO_DATE('2009-5-9','YYYY-MM-DD'),2) FROM DUAL;
---MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(TO_DATE('2009-7-19','YYYY-MM-DD'),TO_DATE('2009-6-11','YYYY-MM-DD')) FROM DUAL;
----ROUND 2010-9-11是星期六
SELECT ROUND(TO_DATE('2010-9-11','YYYY-MM-DD'),'DAY') FROM DUAL; --0912
SELECT ROUND(TO_DATE('2010-9-8','YYYY-MM-DD'),'DAY') FROM DUAL;---0905
SELECT ROUND(TO_DATE('2010-9-11','YYYY-MM-DD'),'MONTH') FROM DUAL;--0901
SELECT ROUND(TO_DATE('2010-9-15','YYYY-MM-DD'),'MONTH') FROM DUAL;--0901
SELECT ROUND(TO_DATE('2010-9-16','YYYY-MM-DD'),'MONTH') FROM DUAL;--1001
SELECT ROUND(TO_DATE('2010-7-5','YYYY-MM-DD'),'YEAR') FROM DUAL;--2010
SELECT ROUND(TO_DATE('2010-7-16','YYYY-MM-DD'),'YEAR') FROM DUAL;--2011
SELECT ROUND(TO_DATE('2010-6-16','YYYY-MM-DD'),'YEAR') FROM DUAL;--2011
SELECT ROUND(TO_DATE('2010-7-16 11:59:59','YYYY-MM-DD HH24:MI:SS')) FROM DUAL;--0716
SELECT ROUND(TO_DATE('2010-7-16 12:00:00','YYYY-MM-DD HH24:MI:SS')) FROM DUAL;--0716
--日期中没写时间,to_round控制不会显示
SELECT ROUND(TO_DATE('2010-7-16','YYYY-MM-DD HH24:MI:SS')) FROM DUAL;
---日期中没写时间,to_char控制会显示
SELECT TO_CHAR(ROUND(TO_DATE('2010-7-16','YYYY-MM-DD HH24:MI:SS')),
'MM-DD-YYYY HH24:MI:SS') FROM DUAL;
----NEXT_DATE
SELECT NEXT_DAY(TO_DATE('2010-9-11','YYYY-MM-DD'),'星期日') FROM DUAL;
SELECT NEXT_DAY(TO_DATE('2010-9-11','YYYY-MM-DD'),'SUNDAY') FROM DUAL;
-----修改语言环境
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE';
---LAST_DATE
SELECT LAST_DAY(TO_DATE('2010-9-11','YYYY-MM-DD')) FROM DUAL;
------ 转换函数
--TO_NUMBER
SELECT TO_NUMBER('12') FROM DUAL;
SELECT TO_NUMBER('12','9