Oracle 替代變數完整應用指南：互動式查詢、腳本參數化與進階技巧

替代變數讓 SQL 語句在執行時動態接收使用者輸入，實現互動式查詢與腳本參數化。本文系統整理 &、&&、DEFINE/UNDEFINE、ACCEPT 等指令用法、資料型別處理、多變數應用、提示自訂與最佳實踐。

一、基礎語法：& 變數

-- 數值型：直接輸入數字
SELECT ename, job, sal, hiredate FROM emp WHERE sal > &input_sal;

-- 字串型：需自行加單引號 (不便)
SELECT * FROM emp WHERE ename = '&input_ename';

-- 改良：利用 UPPER 自動加引號並統一大小寫
SELECT * FROM emp WHERE ename = UPPER('&input_ename');

執行時：SQL Developer 彈出輸入對話框；SQL*Plus 命令列提示 Enter value for input_ename:。

二、進階應用場景

三、雙 && 變數：定義一次、重複使用

解決 GROUP BY 需重複輸入同一值的問題：

SELECT &&grp_col, SUM(sal), AVG(sal)
FROM emp
GROUP BY &&grp_col;

第一次提示輸入 grp_col，第二次自動復用。變數生命週期至 Session 結束或執行 UNDEFINE grp_col。

四、DEFINE / UNDEFINE：預先定義與清除

-- 定義變數 (無提示，直接賦值)
DEFINE input_dname = 'ACCOUNTING'

-- 查詢已定義變數
DEFINE input_dname

-- 使用 (前綴 &)
SELECT * FROM dept WHERE dname = '&input_dname';

-- 清除變數
UNDEFINE input_dname

五、ACCEPT：客製化提示、資料驗證、隱藏輸入

語法：ACCEPT var_name [TYPE] [FORMAT fmt] [PROMPT 'text'] [HIDE]

實戰腳本範例 (query_emp.sql)

ACCEPT v_empno NUMBER PROMPT '輸入員工編號: '
ACCEPT v_hiredate DATE FORMAT 'YYYY-MM-DD' PROMPT '入職日期 (YYYY-MM-DD): '

SELECT empno, ename, job, hiredate
FROM emp
WHERE empno = &v_empno
  AND hiredate = TO_DATE('&v_hiredate', 'YYYY-MM-DD');

執行：SQL> @query_emp.sql

六、最佳實踐建議

1. 字串變數**統一用 UPPER('&var')**，避免大小寫與引號困擾。
2. 腳本檔案統一 .sql 副檔名，SQL*Plus 直接 @filename 執行。
3. 生產環境腳本**避免互動式輸入**，改用 DEFINE 或外部傳參 (SQLPLUS user/pwd @script.sql arg1 arg2 → 腳本內用 &1 &2)。
4. 敏感資訊 (密碼) 僅用 ACCEPT ... HIDE，勿硬編碼在腳本中。
5. 用完即 UNDEFINE，避免變數殘留干擾後續腳本。