SQL*Plus 綁定變數 (Substitution Variables) 讓腳本具備互動與參數化能力。本文詳細說明 &/&& 替代語法、ACCEPT 互動輸入、DEFINE/UNDEFINE 預設值管理、字串/日期型別引號處理、腳本參數傳遞 (@script.sql p1 p2)，並提供實戰範例與避坑清單。
| 語法 | 行為 | 適用場景 |
|---|---|---|
&var | 每次遇到提示輸入 (可用 SET VERIFY OFF 關閉新舊值顯示) | 臨時互動查詢 |
&&var | 第一次提示後快取，同腳本後續直接使用 | 腳本內重複引用同一參數 |
-- 1. 互動式輸入 (ACCEPT)
ACCEPT dept_id NUMBER PROMPT 'Enter Department ID: '
ACCEPT emp_name CHAR PROMPT 'Enter Name: ' HIDE
ACCEPT hire_dt DATE FORMAT 'YYYY-MM-DD' PROMPT 'Hire Date (YYYY-MM-DD): '
-- 2. 預設值 (DEFINE)
DEFINE dept_id = 10
DEFINE emp_name = 'King'
-- 查看定義
DEFINE dept_id
-- 移除定義
UNDEFINE dept_id
-- 3. 關閉驗證顯示
SET VERIFY OFF -- 隱藏 old/new 行
SET VERIFY ON -- 顯示 (預設)
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = &dept_id;
-- 輸入 10 → WHERE department_id = 10
SELECT employee_id, last_name, salary
FROM employees
WHERE last_name = '&emp_name'; -- 字串
WHERE hire_date > TO_DATE('&start_dt', 'YYYY-MM-DD'); -- 日期
-- 輸入 King → WHERE last_name = 'King'
-- 輸入 2020-01-01 → TO_DATE('2020-01-01', 'YYYY-MM-DD')
-- script.sql
SET VERIFY OFF
ACCEPT p_dept NUMBER PROMPT 'Dept ID: '
ACCEPT p_min_sal NUMBER PROMPT 'Min Salary: '
ACCEPT p_max_sal NUMBER PROMPT 'Max Salary: '
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id = &p_dept
AND salary BETWEEN &p_min_sal AND &p_max_sal
ORDER BY salary DESC;
-- 呼叫方式
@script.sql 10 5000 10000
START script.sql 10 5000 10000
-- 腳本內部引用 &1, &2, &3 ... &9
SELECT * FROM employees
WHERE department_id = &1
AND salary BETWEEN &2 AND &3;
| 問題 | 原因 | 解決 |
|---|---|---|
| 輸入含 & 字元報錯 | SQL*Plus 視為變數 | SET DEFINE OFF 或 SET SCAN OFF 或改用 SET DEFINE # 更換前綴 |
| 字串/日期未加單引號 | SQL 語法錯誤 | WHERE 子句中 '&var'、TO_DATE('&var','fmt') |
| 數值輸入含千分位逗號 | 轉換失敗 | ACCEPT ... NUMBER 自動驗證，或輸入前移除逗號 |
| &&var 快取跨腳本殘留 | Session 級快取 | 腳本結束執行 UNDEFINE var |
| 腳本參數含空白 | 參數分割錯誤 | 用雙引號包裹 @script.sql "Sales Dept" 5000 → 腳本內 '&1' |
SET VERIFY OFF SET FEEDBACK OFF (僅輸出結果)。ACCEPT ... PROMPT 提供清楚提示，NUMBER/CHAR/DATE 型別驗證。&1..&9，避免互動提示阻塞自動化流程。ACCEPT ... HIDE 或環境變數傳入，勿硬編碼。善用 SQL*Plus 綁定變數，能將固定 SQL 轉為可重用、可參數化、可自動化的腳本，大幅提升 DBA 與開發者日常操作效率。
