Oracle 常見問題 FAQ 精選：從特殊字元查詢到排程作業完整索引

前言

整理自 Oracle 官方 CHM 幫助文件的常見問題清單，涵蓋 SQL 查詢、PL/SQL 開發、效能調校、安全管理等核心主題，供開發人員與 DBA 快速檢索。

SQL 查詢與資料操作

• 特殊字元查詢：如何查詢包含 %、_ 的資料（使用 ESCAPE 子句）
• 單引號插入：INSERT INTO t VALUES ('It''s OK'); 或使用 q'[...]' 引用符
• 事務一致性設定：SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
• 游標更新資料：FOR UPDATE 鎖定列再 UPDATE ... WHERE CURRENT OF cursor;
• 自訂例外：DECLARE e_custom EXCEPTION; PRAGMA EXCEPTION_INIT(e_custom, -20001);
• 十/十六進制轉換：TO_NUMBER('FF','XX')、TO_CHAR(255,'FMXX')
• SYS_CONTEXT 詳細用法：取得 Session、IP、認證資訊等上下文
• 日期函數大全：取得星期、月首/末日、下個工作日、閏年判斷
• 隨機抽樣：SAMPLE(n)、DBMS_RANDOM.VALUE、ORDER BY DBMS_RANDOM.RANDOM
• 分頁查詢 (N 到 M 行)：12c OFFSET ... FETCH、舊版 ROWNUM 子查詢寫法
• 重複資料查詢/刪除：ROW_NUMBER() OVER (PARTITION BY ...)、GROUP BY ... HAVING COUNT(*) > 1
• 自治事務：PRAGMA AUTONOMOUS_TRANSACTION; 獨立提交/回滾
• 程式暫停：DBMS_LOCK.SLEEP(seconds); 或 DBMS_SESSION.SLEEP (18c+)
• 交易耗時/Redo 量計算：V$TRANSACTION、V$SESSTAT 統計
• 暫存表建立：CREATE GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE/DELETE ROWS;
• PL/SQL 執行 DDL：EXECUTE IMMEDIATE 'CREATE INDEX ...'; 需動態 SQL
• 取得 IP 位址：SYS_CONTEXT('USERENV','IP_ADDRESS') 或 UTL_INADDR.GET_HOST_ADDRESS
• 加密預存程序：WRAP 工具或 DBMS_DDL.WRAP (參見文章 125367)
• 排程執行預存程序：DBMS_SCHEDULER.CREATE_JOB (10g+ 推薦) 或 DBMS_JOB (舊版)
• 取得毫秒：SYSTIMESTAMP、DBMS_UTILITY.GET_TIME (百分秒)、SYSDATE 無毫秒
• UPSERT (MERGE)：MERGE INTO target USING source ON (...) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...
• Join 類型：LEFT/RIGHT/FULL OUTER JOIN、(+) 舊語法
• 多表插入：INSERT ALL WHEN cond1 THEN INTO t1 ... WHEN cond2 THEN INTO t2 ... SELECT ... FROM src;
• 行列轉換 (PIVOT/UNPIVOT)：11g+ PIVOT/UNPIVOT 子句、舊版 DECODE/CASE 聚合
• 分組取前 N 筆：ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val DESC) <= N
• 相鄰列合併：LISTAGG(col, ',') WITHIN GROUP (ORDER BY seq) (11g+)
• 第 N 大值：NTH_VALUE、DENSE_RANK、子查詢 ROWNUM
• 查詢結果匯出文字檔：SPOOL file.txt、UTL_FILE、DBMS_SCHEDULER 搭配 Shell
• Definer/Invoker 權限：AUTHID DEFINER | CURRENT_USER
• SQL*Plus 執行 OS 指令：HOST cmd、!cmd (Unix)、$cmd
• 快速取得表/分區筆數：DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO + USER_TAB_MODIFICATIONS、NUM_ROWS 統計值
• 發送 Email：UTL_MAIL.SEND、UTL_SMTP 低階控制
• 寫入 OS 檔案 (Log)：UTL_FILE.PUT_LINE、DBMS_SCHEDULER 外部作業

使用建議

1. 以關鍵字在本清單搜尋對應主題。
2. 進階細節請參考 Oracle 官方文件：SQL Language Reference、PL/SQL Packages and Types Reference。
3. 實務上建議建立團隊內部 Wiki，將常用語法片段標準化沉澱。

結語

這份 FAQ 索引涵蓋了 Oracle 開發與管理 90% 以上的常見場景。熟練掌握這些技巧，能大幅提升解決問題的速度與品質。