源码-PL/SQL从入门到精通-第九章-SQL内置函数-Part 1

Oracle内置函数还是很丰富的，即使对Excel函数比较熟悉，看到这么多的Oracle内置函数还是有点头大的。

要全部记住不太现实，如果有一张函数表可随时查询就方便多了。

也可以在Oracle官网提供的SQL参考文档上检索， 链接：http://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm

--第9章开始
--9.1 基本函数

--大小写转换函数
SELECT INITCAP(first_name || ' ' || last_name) AS 姓名,
       LOWER(email) 电子邮件,
       UPPER(first_name) 姓
FROM   employees
WHERE  ROWNUM <= 5;

--代码9.1 大小写转换函数PL/SQL使用示例
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;


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


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;


--代码9.3 字符串替代函数示例
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;

--9.1.2 数字型函数
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;

SELECT ROUND(SYSDATE), TRUNC(SYSDATE)
FROM   DUAL;

SELECT SYSDATE
FROM   DUAL;

SELECT *
FROM   employees;


--9.1.3 日期时间函数
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';


SELECT SYSDATE - 1 当前日期减1,
       SYSDATE - (SYSDATE - 100) 两个日期相减,
       SYSDATE + 5 / 24 当前日期加5小时
FROM   DUAL;
SELECT SYSDATE - (SYSDATE - 100)
FROM   DUAL;

--代码9.4 常用的日期运算函数使用示例
BEGIN
  DBMS_OUTPUT.put_line('两个日期之间的差异月份：' ||
                       MONTHS_BETWEEN('1995-01-01', '1994-11-01'));
  DBMS_OUTPUT.put_line('向指定日期添加月份：' || ADD_MONTHS(SYSDATE, 6));
  DBMS_OUTPUT.put_line('下个星期五为：' || NEXT_DAY(SYSDATE, '星期五'));
  DBMS_OUTPUT.put_line('显示当前月的最后1天：' || LAST_DAY(SYSDATE));
END;

--这部分没看明白
SELECT TRUNC(SYSDATE, 'MONTH'),
       ROUND(SYSDATE, 'YEAR'),
       ROUND(SYSDATE, 'DAY'),
       TRUNC(SYSDATE, 'YEAR'),
       TRUNC(SYSDATE, 'DAY'),
       TRUNC(SYSDATE, 'HH24'),
       TRUNC(SYSDATE, 'MI')
FROM   DUAL;


--使用to_char将日期转换为字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS AM')
FROM   DUAL  
       

SELECT TO_CHAR(SYSDATE, 'ddspth')
FROM   DUAL;

SELECT TO_CHAR(SYSDATE, 'ddthsp')
FROM   DUAL;

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS AM')
FROM   DUAL;

SELECT TO_CHAR(SYSDATE, 'DD "of" MONTH')
FROM   DUAL;

SELECT TO_CHAR(SYSDATE, 'A.D.YYYY"年"-MONTH-DD"日"-DAY')
FROM   DUAL;

SELECT TO_CHAR(SYSDATE, 'W')
FROM   DUAL;

--使用to_char将数字型转换为字符串
SELECT TO_CHAR(123.45678, 'L99999.999')
FROM   DUAL;

SELECT TO_CHAR(1234, 'C9999')
FROM   DUAL;

SELECT TO_CHAR(123456789, 'L999G999G999D99', 'NLS_CURRENCY=%')
FROM   DUAL;

--使用to_date将字符串转换为日期
SELECT TO_DATE('2010/09/13', 'YYYY-MM-DD', 'NLS_DATE_LANGUAGE=english')
FROM   DUAL;

SELECT TO_DATE('20100913', 'YYYY-MM-DD')
FROM   DUAL;

--使用to_number将字符串转换为数字
SELECT TO_NUMBER('$1234.5678', '$9999.9999')
FROM   DUAL;

SELECT TO_NUMBER('$123,456,789.00', '$999G999G999D99')
FROM   DUAL;