善用腳本檔管理 Oracle 資料庫：建立、編輯、測試與參數化最佳實踐

資料庫管理員（DBA）每天面對大量重複指令，將驗證過的命令封裝成腳本檔（.sql），不僅能減少輸入錯誤、提升效率，更能建立可重複、可版控的作業知識庫。本文分享從建立到維護的完整流程。

為何需要腳本檔？

• 降低指令輸入量與拼字錯誤。
• 將調試通過的命令固化，下次直接複用。
• 支援參數化，同一腳本適用多環境、多物件。
• 納入 Git 等版控系統，追蹤變更歷程。

步驟一：建立腳本檔

推薦工具鏈

1. 外部編輯器（VS Code、Notepad++、Sublime Text）：語法高亮、多游標、捲動搜尋，編輯體驗遠優於 SQL*Plus 內建編輯器。
2. SQL*Plus / SQLcl：僅用於測試執行。

流程：外部編輯器撰寫 → 複製貼上到 SQL*Plus 測試 → 通過後存檔為 xxx.sql（副檔名必須為 .sql，SQL*Plus 才能直接 @xxx 呼叫）。

腳本檔格式三大鐵律

1. 最後一行必須以 / 結尾，告知 SQL*Plus 執行緩衝區指令。
2. 最後一條 SQL 陳述式勿以 ; 結尾，否則 / 會重複執行導致錯誤。
3. 變數以 &1, &2... 表示，執行時依序代入；無需預先宣告。

範例：備份表腳本 backup_table.sql

-- backup_table.sql
-- 用法: @backup_table 來源表 目標表
CREATE TABLE &2 AS SELECT * FROM &1
/

執行：SQL> @backup_table emp emp_bak_20240101

步驟二：編輯與維護

• 微調：直接在 SQL*Plus 用 EDIT filename 呼叫外部編輯器修改。
• 大幅重構：回到外部編輯器完整編輯，再重新測試。
• 不可變更的結構：保留尾端 /、避免誤刪、確認編碼為 UTF-8。

進階技巧

1. 互動式確認

ACCEPT confirm CHAR PROMPT '確定要刪除 &1 表嗎? (Y/N): '
BEGIN
  IF UPPER('&confirm') = 'Y' THEN
    EXECUTE IMMEDIATE 'DROP TABLE &1 PURGE';
  END IF;
END;
/

2. 記錄執行日誌

SPOOL backup_&1._&_DATE..log
-- 實際操作
SPOOL OFF

3. 條件式邏輯（PL/SQL 區塊）

DECLARE
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM user_tables WHERE table_name = UPPER('&1');
  IF v_cnt = 0 THEN
    DBMS_OUTPUT.PUT_LINE('表 &1 不存在');
  ELSE
    EXECUTE IMMEDIATE 'TRUNCATE TABLE &1';
  END IF;
END;
/

作者心得

「筆者已累積近百支腳本，涵蓋備份、效能收集、物件重建、權限稽核等。記事本（Notepad）配合 SQL*Plus 測試，輕量且足夠應付 90% 場景。關鍵在於每支腳本上線前必須實測，未測試的腳本就是定時炸彈。」

檢核清單

• [ ] 副檔名 .sql
• [ ] 尾端 /、無多餘 ;
• [ ] 參數採 &n 形式
• [ ] 已在目標環境實測通過
• [ ] 納入版控並標註用途、參數說明