Oracle SQL*Plus 替換變數進階應用：從基礎定義到腳本化最佳實踐

核心價值：提升 SQL 互動靈活性與重用性

在 SQL*Plus 等工具中，替換變數（以 & 前綴）讓 SQL 語句參數化，執行時動態提示輸入，大幅提升互動性與腳本重用性。

一、定義與使用替換變數

基本語法

SELECT &col_list FROM employees WHERE department_id = &dept_id;

• 變數名前加 & 即為替換變數
• 執行時自動提示輸入對應值
• 適用 SELECT、UPDATE、INSERT 等各類 DML

字元/日期型變數的引號處理

-- 定義時包含引號，執行時無需輸入引號
SELECT * FROM employees WHERE last_name = '&emp_name';
-- 執行時輸入: King  (而非 'King')

原則：SQL 語句中需引號的地方，定義替換變數時就加上引號。

二、定義預設值 (DEFINE)

語法與規則

DEFINE dept_id = 10
DEFINE emp_name = 'King'

• 定義後不再提示輸入，直接使用預設值
• 引號規則：SQL 需引號處定義時加引號；不需處（如欄位名）則不加
• 僅支援字元型預設值
• 作用域：當前 Session，Session 結束失效

管理命令

DEFINE                -- 列出所有替換變數
DEFINE dept_id        -- 查看特定變數值
UNDEFINE dept_id      -- 取消預設值

腳本化最佳實踐

• 將 DEFINE 寫在腳本開頭，隨腳本自動載入
• 下次 Session 直接執行腳本無需重複定義

三、數值型變數預設值 (ACCEPT)

DEFINE 僅支援字元型，數值型需用 ACCEPT：

ACCEPT salary NUMBER PROMPT 'Enter salary: ' DEFAULT 5000

四、腳本化與可讀性增強技巧

1. 註釋用途：腳本開頭說明用途、參數含義、建立日期
2. 命名規範：欄位變數用 col_ 前綴，條件變數用 cond_ 前綴
3. PROMPT 輸出提示：執行前輸出說明資訊
4. PAUSE 互動確認：關鍵步驟前暫停，按 Enter 繼續

五、進階互動方案：ACCEPT + PAUSE + PROMPT 組合

PROMPT === Employee Query Script ===
ACCEPT dept_id NUMBER PROMPT 'Department ID: ' DEFAULT 10
ACCEPT emp_name CHAR PROMPT 'Employee Name (optional): '
PROMPT Querying department &dept_id ...
PAUSE 'Press Enter to execute...'
SELECT * FROM employees WHERE department_id = &dept_id AND (last_name = '&emp_name' OR '&emp_name' IS NULL);

六、綁定變數 (BIND VARIABLES) — 替代方案

• 僅存在當前 Session，同名以最後定義為準
• 賦值需 VARIABLE 宣告 + EXECUTE 賦值，較繁瑣
• 實際應用較少，替換變數更直觀