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
  

• 查詢使用者

  SELECT username, created
  FROM all_users
  

• 查詢權限

  SELECT grantor, grantee, table_schema, table_name, privilege
  FROM all_tab_privs
  WHERE grantee = 'user_name'
  

與 MySQL 不同之處

• 沒有 LIMIT

  -- 錯誤
  SELECT name FROM person ORDER BY age
  LIMIT 3;
  
  -- 使用 ROWNUM
  SELECT * FROM (
    SELECT name FROM person ORDER BY age
  ) WHERE ROWNUM <= 3;
  
  -- 使用 FETCH
  SELECT name FROM person ORDER BY age
  FETCH FIRST 3 ROWS ONLY;
  
  -- 使用 OFFSET
  SELECT name FROM person ORDER BY age
  OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;