Oracle 資料庫常用函數

變數

DEFINE var = '123456';
SELECT &var FROM DUAL; -- number
SELECT '&var' FROM DUAL; -- string

SYSDATE

不包含毫秒與時區

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL; --2022/01/26 17:59:22

SYSTIMESTAMP

包含毫秒與時區

SELECT SYSTIMESTAMP FROM DUAL; --26-JAN-22 05.59.42.697000000 PM +08:00

TO_CHAR()

將日期轉為字串，並格式化

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL; --2022/01/26 17:59:22

TO_DATE()

將資料轉為日期格式

SELECT TO_DATE('27-OCT-99') FROM DUAL;
SELECT TO_DATE('1999/10/27', 'YYYY/MM/DD') FROM DUAL;
SELECT TO_DATE('October 27, 1999', 'Month dd, YYYY', 'NLS_DATE_LANGUAGE = American') FROM DUAL;

TRUNC()

將 DATE 截斷

DEFINE dateString = '2021/11/11 12:35:49';
DEFINE formatString = 'YYYY/MM/DD HH24:MI:SS';

SELECT TO_CHAR(TRUNC(TO_DATE('&dateString', '&formatString')), '&formatString') FROM DUAL;         -- 預設是DD 2021/11/11 00:00:00
SELECT TO_CHAR(TRUNC(TO_DATE('&dateString', '&formatString'), 'SS'), '&formatString') FROM DUAL;   -- ORA-01899: bad precision specifier
SELECT TO_CHAR(TRUNC(TO_DATE('&dateString', '&formatString'), 'MI'), '&formatString') FROM DUAL;   -- 保留到分 2021/11/11 12:35:00
SELECT TO_CHAR(TRUNC(TO_DATE('&dateString', '&formatString'), 'HH'), '&formatString') FROM DUAL;   -- 保留到時 2021/11/11 12:00:00
SELECT TO_CHAR(TRUNC(TO_DATE('&dateString', '&formatString'), 'DD'), '&formatString') FROM DUAL;   -- 保留到日 2021/11/11 00:00:00
SELECT TO_CHAR(TRUNC(TO_DATE('&dateString', '&formatString'), 'D'), '&formatString') FROM DUAL;    -- 周開始日 2021/11/07 00:00:00
SELECT TO_CHAR(TRUNC(TO_DATE('&dateString', '&formatString'), 'MM'), '&formatString') FROM DUAL;   -- 保留到月 2021/11/01 00:00:00
SELECT TO_CHAR(TRUNC(TO_DATE('&dateString', '&formatString'), 'YYYY'), '&formatString') FROM DUAL; -- 保留到年 2021/01/01 00:00:00

LENGTH()

計算字串的長度
英文 1，中文 3

SELECT LENGTH('Apple') FROM DUAL; --5