Oracle 變數定義三大方式完整解析：DEFINE、VARIABLE 與 DECLARE

Oracle 變數定義三大方式完整解析

在 Oracle 開發與管理作業中，DEFINE（替代變數/主機變數）、VARIABLE（繫結變數）與 DECLARE（PL/SQL 區域變數）是三種最核心的變數機制。本文系統整理其語法、特性、適用場景與操作範例，助你精準選用。

一、DEFINE：替代變數（主機變數）

本質：SQL*Plus 客戶端層面的字串替換機制，全為 CHAR 型別，Session 級有效。

核心操作

-- 宣告並指定值（必須同步賦值）
DEFINE var_name = value

-- 顯示單一/所有變數
DEFINE var_name
DEFINE

-- 關閉/開啟替代功能
SET DEFINE OFF
SET DEFINE ON

引用方式

• &var_name：直接替換，等同文字取代。
• &var_name.：加點號明確界定變數結尾。
• 未預先定義時使用 &var 會提示輸入（臨時變數，DEFINE 指令查不到）。

典型應用場景

• 腳本參數化：表名、條件值等頻繁變動處。
• 動態建構 SQL 陳述式（需注意 SQL 注入風險）。

注意事項

• 替換發生在 SQL 送往資料庫之前，資料庫完全看不見變數。
• 單引號內的 &var 不會被替換（視為字面字串）。
• SET DEFINE OFF 可避免誤觸發替換（如腳本含大量 & 符號）。

二、VARIABLE：繫結變數

本質：資料庫層面的變數，具資料型別，支援軟解析（一次解析、多次執行），Session 級有效。

核心操作

-- 宣告（僅指定名稱與型別，不可賦值）
VARIABLE var_name NUMBER
VAR v_name VARCHAR2(100)  -- 縮寫

-- 顯示定義/值
VARIABLE var_name
VAR
PRINT var_name
PRINT

賦值方式

1. PL/SQL 區塊：BEGIN :var := value; END;
2. EXECUTE：EXEC :var := value
3. 呼叫函數：CALL func(...) INTO :var
4. 存儲程序 OUT 參數：EXEC proc(:var)
5. REF CURSOR 遊標回傳：VAR cur REFCURSOR → PRINT cur 直接顯示結果集。

引用方式

SQL/PL/SQL 中一律加冒號前綴：:var_name。

效能核心：軟解析

使用繫結變數的 SQL，文本相同（含大小寫）、Session 環境一致時，優化器可重用執行計畫，避免硬解析開銷。可查詢 V$SQL / V$SQLAREA 的 EXECUTIONS 欄位驗證重用次數。

適用性判斷

• ✅ 高頻執行、短時間 SQL（OLTP 核心交易）。
• ❌ 執行數小時的長跑查詢（解析成本可忽略，繫結變數可能干擾最佳化器基數估算）。

三、DECLARE：PL/SQL 區域變數

本質：PL/SQL 區塊宣告區定義的區域變數，具完整型別系統（標量、記錄、集合、REF CURSOR 等），僅在區塊內可見，區塊結束即銷毀。

基本結構

DECLARE
  v_id     NUMBER := 1;
  v_name   VARCHAR2(50) := 'Test';
  v_rec    employees%ROWTYPE;
  v_arr    SYS.ODCIVARCHAR2LIST;
BEGIN
  -- 程式邏輯
  NULL;
END;
/

關鍵特性

• 支援 %TYPE、%ROWTYPE 錨定型別，自動同步資料表結構變更。
• 可宣告常數（CONSTANT）、例外、游標、巢狀區塊變數。
• PL/SQL 引擎內部將參數、區域變數轉為繫結變數處理，故靜態 SQL 優於動態 SQL；必須動態 SQL 時務必使用 USING 繫結變數。

四、三者快速比較表

五、最佳實務建議

1. 應用程式開發：全面使用繫結變數（JDBC PreparedStatement、ODP.NET 參數），禁用字串拼接。
2. SQL*Plus 腳本：DEFINE 處理環境參數，VARIABLE 搭配 PL/SQL 處理複雜邏輯與結果回傳。
3. PL/SQL 程式單元：盡量用 DECLARE 區域變數與靜態 SQL；動態 SQL 必須 USING 繫結。
4. 定期檢視 V$SQLAREA.EXECUTIONS 確認高頻 SQL 達到軟解析重用。

小結

理解 DEFINE/VARIABLE/DECLARE 三者的層級、型別、生命週期與繫結機制，是撰寫高效、安全 Oracle 程式的基石。依據「誰在用、用在哪、為何用」精準選擇，即能發揮各機制最大效益。