Oracle 學習之路 (五)：數據類型、運算符、變量賦值計算、類型轉換與作用域完整指南

Oracle 學習之路 (五)：數據類型、運算符、變量賦值計算、類型轉換與作用域完整指南

PL/SQL 程式設計的基石在於對數據類型、運算符、變量生命周期的精準掌握。本文系統整理 Oracle 常用運算符、變量聲明賦值規則、NULL 參與運算的特殊語義、顯式/隱式類型轉換函數、變量作用域與可見性規則，並附帶可直接運行的代碼示例。

一、 常用運算符速查表

二、 變量聲明與賦值規範

1. 聲明語法

-- 單純聲明
v_name  VARCHAR2(30);
v_sal   NUMBER(8,2);
v_hiredate DATE;

-- 聲明並初始化 (推薦)
v_count   NUMBER := 0;
v_flag    BOOLEAN := TRUE;
v_deptno  NUMBER(2) NOT NULL := 10; -- NOT NULL 約束需初始化

-- 使用 %TYPE 錨定類型 (最佳實踐)
v_ename   emp.ename%TYPE;
v_sal     emp.sal%TYPE;

-- 使用 %ROWTYPE 錨定記錄
v_emp_rec emp%ROWTYPE;

2. 賦值語法

-- 直接賦值
v_name := 'SCOTT';

-- SELECT INTO 賦值 (單行結果)
SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7788;

-- 多行結果需用游標或 BULK COLLECT
-- 錯誤示範: SELECT ename INTO v_name FROM emp; -- TOO_MANY_ROWS

三、 變量計算中的 NULL 語義陷阱

NULL 代表「未知」，而非「零」或「空字符串」。任何與 NULL 運算結果多為 NULL，例外：字符串連接 ||。

四、 數據類型顯式轉換函數

雖然 Oracle 支援隱式轉換，但生產代碼強制要求顯式轉換，避免格式模型不匹配 (ORA-01861)、性能損耗、計劃不穩定。

常用日期格式元素

YYYY  四位年    MM  月(01-12)    DD  日(01-31)
HH24  24制時    MI  分(00-59)    SS  秒(00-59)
DAY   星期名    MON  月份縮寫    DY  星期縮寫
FM    填充模式關閉 (去除前導零/空格)
FX    精確匹配模式 (格式字符串需完全匹配)

五、 變量作用域與可見性規則

PL/SQL 採用**靜態詞法作用域**，塊結構決定變量生命周期。

核心規則

1. 作用域： 從聲明處開始，到所在塊 (BEGIN...END、循環、IF、子程序、包) 結束。
2. 可見性： 僅在聲明它的塊及其**子塊**中可見 (除非被重新聲明遮蔽)。
3. 遮蔽： 子塊聲明同名變量，將遮蔽外層同名變量，子塊內只能訪問內層變量。
4. 生命周期： 進入塊時分配內存，退出塊時釋放 (包級變量隨會話生命周期)。

作用域遮蔽實戰演示

DECLARE
  v_msg VARCHAR2(20) := 'Outer Block'; -- 外層變量
BEGIN
  DBMS_OUTPUT.PUT_LINE('1. ' || v_msg); -- 輸出: Outer Block
  
  DECLARE
    v_msg VARCHAR2(20) := 'Inner Block'; -- 內層同名變量 (遮蔽外層)
  BEGIN
    DBMS_OUTPUT.PUT_LINE('2. ' || v_msg); -- 輸出: Inner Block
  END;
  
  DBMS_OUTPUT.PUT_LINE('3. ' || v_msg); -- 輸出: Outer Block (內層結束，外層恢復可見)
END;
/
-- 輸出結果:
-- 1. Outer Block
-- 2. Inner Block
-- 3. Outer Block

標籤塊與限定訪問

<>
DECLARE
  v_num NUMBER := 100;
BEGIN
  <>
  DECLARE
    v_num NUMBER := 200;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Inner v_num: ' <>.v_num); -- 200
    DBMS_OUTPUT.PUT_LINE('Outer v_num: ' <>.v_num); -- 100 (標籤限定訪問外層)
  END;
END outer_block;
/

六、 綜合實戰案例：員工年薪計算與輸出

DECLARE
  -- 錨定類型聲明
  v_empno     emp.empno%TYPE := 7788;
  v_ename     emp.ename%TYPE;
  v_sal       emp.sal%TYPE;
  v_comm      emp.comm%TYPE;
  v_annual    NUMBER(12,2);
  c_tax_rate  CONSTANT NUMBER(3,2) := 0.03; -- 常量聲明
BEGIN
  -- 查詢賦值，處理 NULL 佣金
  SELECT ename, sal, NVL(comm, 0)
  INTO   v_ename, v_sal, v_comm
  FROM   emp
  WHERE  empno = v_empno;
  
  -- 計算年薪: (月薪 + 佣金) * 12
  v_annual := (v_sal + v_comm) * 12;
  
  -- 輸出格式化
  DBMS_OUTPUT.PUT_LINE('員工: ' || v_ename);
  DBMS_OUTPUT.PUT_LINE('月薪: ' || TO_CHAR(v_sal, 'FM99,999.00'));
  DBMS_OUTPUT.PUT_LINE('佣金: ' || TO_CHAR(v_comm, 'FM99,999.00'));
  DBMS_OUTPUT.PUT_LINE('年薪: ' || TO_CHAR(v_annual, 'FM999,999.00'));
  DBMS_OUTPUT.PUT_LINE('稅後: ' || TO_CHAR(v_annual * (1 - c_tax_rate), 'FM999,999.00'));
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('員工編號 ' || v_empno || ' 不存在');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('錯誤: ' || SQLERRM);
END;
/

七、 避坑清單總結

1. 隱式轉換： `WHERE date_col = '2024-01-01'` 依賴 NLS 格式，跨環境失效 → 必須 `TO_DATE`。
2. NULL 連接： `first_name || ' ' || last_name` 若任一為 NULL，Oracle 返回非 NULL (空串行為)，但 SQL Server/PostgreSQL 返回 NULL → 移植時注意。
3. BOOLEAN 類型： 僅 PL/SQL 支援，SQL 不支援，不能 `SELECT ... INTO v_bool`，需用 `NUMBER(1)` 或 `VARCHAR2(1)` 映射。
4. 變量未初始化： 默認 NULL，直接參與計算導致結果 NULL → 聲明即賦值 `:= 默認值`。
5. 作用域混淆： 循環/IF 塊內聲明同名變量遮蔽外層 → 使用有意義的命名前綴 (`l_` 局部、`g_` 全局/`p_` 參數) 或標籤限定。

扎實掌握類型系統、運算符語義、NULL 邏輯、轉換函數與作用域規則，是編寫健壯、可移植、高性能 PL/SQL 代碼的前提。建議建立團隊編碼規範，強制顯式轉換、NULL 防禦、錨定類型、標準命名，通過代碼審查與靜態分析工具 (如 PMD、SonarQube PL/SQL 插件) 落地執行。