在 Oracle SQL 與 PL/SQL 中,& 字元預設被視為 **替換變數** 前導符號(SQL*Plus、SQL Developer、SQLcl 預設行為)。當資料本身包含 &(如 'AT&T'、'R&D')時,若未正確處理,會導致:「提示輸入變數值」、「語法錯誤」、「資料截斷」等問題。本文系統整理各工具環境下的處理方法、SET DEFINE 控制、ESCAPE 機制、連接運算子替代方案,以及批量資料匯入的最佳實務。
SET DEFINE ON(預設開啟),遇到 &var 提示輸入或使用已定義變數值。SET DEFINE OFF 或 SET DEFINE # (改用其他字元)。SET ESCAPE ON (預設) 時,\& 視為字面 &。| 環境 | 問題現象 | 解決方案 | 範例 |
|---|---|---|---|
| SQL*Plus / SQLcl | 提示「Enter value for ...」 | 1. SET DEFINE OFF2. SET ESCAPE ON 使用 \&3. 使用連接運算子 'A' || '&' || 'B' | SET DEFINE OFF
INSERT INTO t VALUES ('R&D');
-- 或
SET ESCAPE ON
INSERT INTO t VALUES ('R\&D'); |
| PL/SQL 區塊 | 編譯/執行時解析替換變數 | 1. 區塊外 SET DEFINE OFF2. 雙 && 定義變數(不提示,用同值)3. 連接運算子 || CHR(38) || | DECLARE v VARCHAR2(10) := 'A' || CHR(38) || 'B'; BEGIN INSERT INTO t VALUES (v); END; |
| SQL Developer | 執行腳本 (F5) 時提示變數;單語句 (Ctrl+Enter) 可能自動轉義 | 1. 工具偏好設定 → Database → 取消「Define substitution variables」 2. 使用 || CHR(38) || (最穩健)3. 匿名區塊包裹 | INSERT INTO t(col) VALUES ('A' || CHR(38) || 'B');
-- CHR(38) = '&' ASCII 碼 |
| JDBC / OCI / ODP.NET | 無替換變數機制,直接傳遞參數 | 使用**繫結變數** PreparedStatement.setString(1, "R&D"),完全避開問題 | String sql = "INSERT INTO t VALUES (?)"; ps.setString(1, "R&D"); |
| 外部檔案匯入 (SQL*Loader, External Table) | 資料檔含 & 字元 | 無替換變數問題,直接載入;控制檔中若引用參數才需注意 | -- |
跨環境、跨工具、無副作用、語意明確:
-- SQL / PL/SQL 通用
INSERT INTO companies (name) VALUES ('AT' || CHR(38) || 'T');
INSERT INTO depts (name) VALUES ('R' || CHR(38) || 'D');
-- PL/SQL 變數賦值
v_name := 'AT' || CHR(38) || 'T';
CHR(38) 為 & 的 ASCII/Unicode 碼值,不受 DEFINE/ESCAPE 設定影響。-- 動態 SQL 建構時,確保 & 不被誤解析
v_sql := 'INSERT INTO t VALUES (''A' || CHR(38) || 'B'')';
EXECUTE IMMEDIATE v_sql;
-- 若從外部讀取含 & 的字串變數,直接繫結最安全
v_sql := 'INSERT INTO t VALUES (:val)';
EXECUTE IMMEDIATE v_sql USING v_input_string; -- v_input_string 可包含任意 &
若需生成大量含 & 的 INSERT 腳本檔:
-- 1. 查詢生成時使用 CHR(38)
SELECT 'INSERT INTO t VALUES (''' || REPLACE(name, '&', '' || CHR(38) || '') || ''');'
FROM source_table;
-- 2. 或在匯出工具中設定不轉義,直接寫入字面值
-- SQL Developer: Export → Format: INSERT → 勾選 "Use bind variables" 或手動處理
| 場景 | 最推薦 | 備選 | 不推薦 |
|---|---|---|---|
| 手工執行單條 SQL (SQL*Plus) | SET DEFINE OFF | \& (SET ESCAPE ON) | 不處理直接執行 |
| PL/SQL 區塊/程序/函數 | CHR(38) 連接 | 區塊外 SET DEFINE OFF | && (易混淆) |
| SQL Developer 執行腳本 | CHR(38) 連接 | 關閉工具偏好設定 | 依賴自動轉義 |
| 應用程式 (Java/.NET/Python) | 繫結變數 | -- | 字串拼接 SQL |
| 生成部署腳本檔 | CHR(38) 連接 | SET DEFINE OFF 區塊包裹 | 直接寫 & |
& 在 Oracle 客戶端工具中是「替換變數」觸發字元,非資料庫引擎層面限制。**最通用、最安全、最可維護**的做法是:**統一使用 CHR(38) 連接運算子組合字串**,或在應用層使用繫結變數。僅在互動式 SQL*Plus 快速測試時使用 SET DEFINE OFF。將此規範納入團隊開發規範與代碼審查清單,可徹底消除此類語法錯誤與資料錯誤。