Oracle 特殊字元轉義完全指南：&（定義變數）與 '（單引號）的處理藝術

Oracle 特殊字元轉義完全指南：&（定義變數）與 '（單引號）的處理藝術

在 SQL*Plus、SQLcl、SQL Developer 執行腳本時，& 與 ' 是最常踩的兩個坑。& 預設識別替換變數，' 為字串定界符。本文系統整理三種轉義方案，並對比效能與適用場景。

一、&（定義變數前綴）轉義

SQL*Plus 內建參數 DEFINE & (hex 26) 表示 & 觸發變數替換。輸入 INSERT INTO t VALUES ('Tom & Jerry') 會提示輸入 Jerry 變數值。

方法 1：關閉 DEFINE（最推薦，效能最佳）

SET DEFINE OFF;
-- 或 SET SCAN OFF (舊版相容)
-- 後續所有腳本不再解析 &，直接當作字面字元

• SQL*Plus：一次設定，全腳本生效，直到 SET DEFINE ON。
• TOAD/SQL Developer：建議每個腳本首行加 SET DEFINE OFF，避免多腳本執行時狀態殘留。

方法 2：CHR(38) 字元碼替換（效能次之）

SELECT 'Tom' || CHR(38) || 'Jerry' FROM DUAL;
-- CHR(38) = '&' 的 ASCII 碼

• 需呼叫函數，有微小 CPU 開銷。
• 適用於動態 SQL 拼接、PL/SQL 變數賦值。

方法 3：字串拆分連接（效能最差）

SELECT 'Tom' || '&' || 'Jerry' FROM DUAL;
-- 實際執行兩次連接操作

• 解析器視為三段字串連接，額外開銷最大。
• 不推薦用於高頻執行 SQL。

二、'（單引號）轉義

Oracle 使用雙單引號表示字面單引號：'' = '。

方法 1：雙引號法（標準、通用）

SELECT 'Test' || '''' FROM DUAL;  -- 結果：Test'
-- 解析：'Test'  ||  ''  '  →  字串 + 單引號字面值

• 四個單引號：外層兩對定界字串，中間 '' 代表一個單引號字元。

方法 2：替代引用符號 Q'[]'（10g+ 推薦，可讀性最佳）

SELECT Q'[Tom & Jerry's]' FROM DUAL;  -- 直接包含 ' 和 &
SELECT Q'{It's "quoted"}' FROM DUAL;  -- 括號可用 {} [] () <> 等成對符號

• 語法：Q'[內容]'，括號內原樣輸出，無需轉義。
• 極大提升含引號、&、換行的複雜字串可讀性。

三、效能與最佳實務對照表

四、實戰範例：安全插入含特殊字元的文本

SET DEFINE OFF;
DECLARE
  v_text VARCHAR2(4000) := Q'[User's input: "Tom & Jerry" - 100% valid]';
BEGIN
  INSERT INTO log_table(msg) VALUES (v_text);
  COMMIT;
END;
/
-- 或使用繫結變數（最安全）
VARIABLE v_msg VARCHAR2(4000);
EXEC :v_msg := Q'[User's input: "Tom & Jerry" - 100% valid]';
INSERT INTO log_table(msg) VALUES (:v_msg);

總結

1. & 轉義：首選 SET DEFINE OFF（零成本），次選 CHR(38)，避免字串拆分。
2. ' 轉義：首選 Q'[]'（可讀性最強、無轉義負擔），標準雙引號 '' 為備選。
3. 動態 SQL 永遠使用繫結變數，而非字串拼接，根治注入與轉義問題。