SQL内置函数

1.UPPER(字符串大写),LOWER(字符串小写),INITCAP(将字符串每一个单词的首字母大写).

DECLARE
   v_namelower VARCHAR2(50):='this is lower character';
   v_nameupper VARCHAR2(50):='THIS IS UPPER CHARACTER';
BEGIN
   DBMS_OUTPUT.PUT_LINE(UPPER(v_namelower));
   DBMS_OUTPUT.PUT_LINE(LOWER(v_nameupper));
   DBMS_OUTPUT.PUT_LINE(INITCAP(v_nameupper));   
END;     

2.CONCAT（连接字符串），LENGTH（字符串长度）,INSTR(某一个字符第一次出现的位置。)

SELECT CONCAT (first_name, last_name) 姓名, LENGTH (email) 邮件长度,
       INSTR (first_name, 'a') "'a'第一次出现的位置"
  FROM employees WHERE SUBSTR(job_id, 4) = 'CLERK' AND ROWNUM<=5;

3.SUBSTR（截取字符串）

DECLARE
   v_str VARCHAR2(20):='Thisisastring';   
BEGIN
   DBMS_OUTPUT.PUT_LINE('SUBSTR(v_str,5,2)：'||SUBSTR(v_str,5,2));
   DBMS_OUTPUT.PUT_LINE('SUBSTR(v_str,-5,2)：'||SUBSTR(v_str,-5,2)); 
   DBMS_OUTPUT.PUT_LINE('SUBSTR(v_str,5,-2)：'||SUBSTR(v_str,5,-2));      
   DBMS_OUTPUT.PUT_LINE('SUBSTR(v_str,5.23,2.34)：'||SUBSTR(v_str,5.23,2.43));        
END;   

4.REPLACE (替换字符串)

DECLARE
   v_str   VARCHAR (50) := 'This is oracle database';
BEGIN
   DBMS_OUTPUT.put_line (   'REPLACE(v_str,''oracle'',''sqlserver'')：'
                         || REPLACE (v_str, 'oracle', 'sqlserver')
                        );
   DBMS_OUTPUT.put_line (   'REPLACE(v_str,''oracle'')：'
                         || REPLACE (v_str, 'oracle')
                        );
   DBMS_OUTPUT.put_line (   'TRANSLATE(v_str,''is'',''*'')：'
                         || TRANSLATE (v_str, 'is', '*')
                        );
END;

5.ROUND（取小数点后N位），TRUNC(四舍五入取整)

SELECT ROUND (45.927, 2), ROUND (45.923, 0), ROUND (45.923, -1),
       TRUNC (45.923), TRUNC (45.923, 2), MOD (45, 12)
  FROM DUAL;

6.日期函数 SYSDATE（当前时间），MONTHS_BETWEEN（月份差），ADD_MONTHS（增加月份），NEXT_DAY（下一天），LAST_DAY(当前月的最后一天)

/* Formatted on 2011/09/12 16:44 (Formatter Plus v4.8.8) */
SELECT SYSDATE - 1 当前日期减1, SYSDATE - (SYSDATE - 100) 两个日期相减,
       SYSDATE + 5 / 24 当前日期加5小时
  FROM DUAL;
SELECT SYSDATE-(SYSDATE-100) FROM DUAL;

/* Formatted on 2011/09/12 17:04 (Formatter Plus v4.8.8) */
BEGIN
   DBMS_OUTPUT.put_line (   '两个日期之间的差异月份：'
                         || MONTHS_BETWEEN (to_date('1995-01-01','yyyy-mm-dd'), to_date('1994-11-01','yyyy-mm-dd'))
                  );
   DBMS_OUTPUT.put_line ('向指定日期添加月份：' || ADD_MONTHS (SYSDATE, 6));
   DBMS_OUTPUT.put_line ('下个星期五为：' || NEXT_DAY (SYSDATE, '星期五'));
   DBMS_OUTPUT.put_line ('显示当前月的最后1天：' || LAST_DAY (SYSDATE));
END;

/* Formatted on 2011/09/12 17:15 (Formatter Plus v4.8.8) */
SELECT TRUNC (SYSDATE, 'MONTH'), ROUND (SYSDATE, 'YEAR'),
       ROUND (SYSDATE, 'DAY'), TRUNC (SYSDATE, 'YEAR'),
       TRUNC (SYSDATE, 'DAY'), TRUNC (SYSDATE, 'HH24'), TRUNC (SYSDATE, 'MI')
  FROM DUAL;

7.to_char,to_date,to_number.

TO_CHAR(1234,'C9999');
TO_DATE ('20100913', 'YYYY-MM-DD')
TO_NUMBER('$1234.5678','$9999.9999')

8.DECODE,相当于多个CASE.

SELECT empno, ename,
       DECODE (job,
               '职员', 1.15 * sal,
               '销售人员', 1.20 * sal,
               '经理', 2.0 * sal,
               '分析人员', 1.12 * sal,
               sal
              ) 调薪表
  FROM emp
 WHERE ROWNUM < 12;

9.NVL:如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

NVL2:如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

NULLIF:NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

COALESCE:Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。

SELECT last_name 英文名, salary 薪资, NVL (commission_pct, 0) 提成比率,
       (salary * 12) + (salary * 12 * NVL (commission_pct, 0)) 年度薪水
  FROM employees


SELECT last_name 英文名, salary 薪资, commission_pct 提成,
       NVL2 (commission_pct, salary * 12 * commission_pct,
             salary * 12) 收入
  FROM employees
 WHERE department_id IN (50, 80);

SELECT NULLIF(commission_pct,0) FROM employees ;

SELECT empno, ename, COALESCE (mgr, deptno, empno) 员工
  FROM emp
 WHERE empno > 7700 AND ROWNUM < 10;

10.SUM(求和) ,MAX（求最大）,MIN（求最小）,AVG（求平均值）

11.日期相加INTERVAL

select date '2016-07-01' + interval '21' year from dual