萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

PL/SQL 核心資料型別差異深度解析:CHAR vs VARCHAR2、CHARACTER vs CHAR

前言

在 PL/SQL 開發與資料庫設計中,字串型別的選擇直接影響記憶體使用、比較語意、效能與資料完整性。CHAR、VARCHAR2、CHARACTER 三者看似相近,實則在儲存機制、比較規則、子型別關係上有本質差異。本文透過實測程式碼與輸出結果,深度解析這些差異,助您做出正確的型別選擇。

一、CHAR vs VARCHAR2:固定長度 vs 變動長度

1.1 核心差異速查

1.2 實測程式碼與輸出分析

DECLARE
    c CHAR(32767) := ' ';
    v VARCHAR2(32767) := ' ';
BEGIN
    DBMS_OUTPUT.PUT_LINE('c is [' || LENGTH(c) || ']');  -- 輸出: c is [32767]
    DBMS_OUTPUT.PUT_LINE('v is [' || LENGTH(v) || ']');  -- 輸出: v is [1]
    v := v || ' ';
    DBMS_OUTPUT.PUT_LINE('v is [' || LENGTH(v) || ']');  -- 輸出: v is [2]
END;
/

解析:

  • c CHAR(32767) := ' '; 雖然只指派一個空格,但宣告瞬間即分配 32,767 bytes,LENGTH 回傳完整宣告長度。
  • v VARCHAR2(32767) := ' '; 僅分配 1 byte 存放該空格,LENGTH 回傳實際長度 1。
  • 串接後 v := v || ' '; 動態增長為 2 bytes,展示動態分配特性。

1.3 隱含長度宣告的陷阱

DECLARE
    var1 CHAR := ' ';        -- 隱含 CHAR(1)
    var2 CHAR(1) := ' ';     -- 顯式 CHAR(1)
    var3 CHAR(1 BYTE) := ' ';-- 顯式 BYTE 語意
    var4 CHAR(1 CHAR) := ' ';-- 顯式 CHAR 語意(多位元組字元集下不同)
BEGIN
    DBMS_OUTPUT.PUT_LINE('var1 [' || LENGTH(var1) || ']');  -- 1
    DBMS_OUTPUT.PUT_LINE('var2 [' || LENGTH(var2) || ']');  -- 1
    DBMS_OUTPUT.PUT_LINE('var3 [' || LENGTH(var3) || ']');  -- 1
    DBMS_OUTPUT.PUT_LINE('var4 [' || LENGTH(var4) || ']');  -- 1 (單一字元)
END;
/

所有隱含/顯式 CHAR(1) 皆分配 1 byte,但 CHAR(1 CHAR) 在 AL32UTF8 等多位元組字元集下可儲存 1 個多位元組字元(最多 4 bytes),語意更精確。

二、CHARACTER vs CHAR:子型別關係

2.1 定義與關係

-- STANDARD 套件中的定義
SUBTYPE CHARACTER IS CHAR(1 CHAR);  -- 實際為 CHAR(1 CHAR) 的子型別
SUBTYPE CHARACTER VARYING IS VARCHAR2(1 CHAR);
  • CHARACTER 是 CHAR 的子型別,具備相同的基礎型別、大小與比較語意。
  • 兩者間可隱式轉換,無效能損耗。
  • 主要差異在於語意可讀性:CHARACTER 強調「單一字元」,CHAR 強調「固定長度字串」。

2.2 實測驗證

DECLARE
    SUBTYPE code IS CHAR(1 CHAR);
    c CHAR(1 CHAR) := 'A';
    d CODE;
BEGIN
    d := c;  -- 隱式轉換成功
    DBMS_OUTPUT.PUT_LINE('d = ' || d);
END;
/
-- 輸出: d = A
PL/SQL 程序已成功完成。

三、比較語意差異的實務衝擊

3.1 SQL 層面比較規則

特性CHAR(n)VARCHAR2(n)
長度特性固定長度,不足右補空格變動長度,依實際資料長度儲存
記憶體分配時機宣告時立即分配完整長度執行期動態分配
最大長度 (PL/SQL)32,767 bytes32,767 bytes
最大長度 (SQL/資料欄)2,000 bytes4,000 bytes (12c+ MAX_STRING_SIZE=EXTENDED 可至 32K)
比較語意Blank-padded comparison(補空格比較)Non-padded comparison(精確比較)
儲存空間固定佔用 n bytes實際長度 + 1~2 bytes 長度前綴
比較組合比較規則範例結果
CHAR vs CHAR / 常數Blank-padded:右補空格至同長再比較'A' = 'A ' → TRUE
VARCHAR2 vs VARCHAR2 / CHAR / 常數Non-padded:精確比較,不補空格'A' = 'A ' → FALSE
CHAR vs VARCHAR2Non-padded(Oracle 將 CHAR 隱式轉為 VARCHAR2 比較)CHAR_COL = 'A' 若資料為 'A ' → FALSE

3.2 DECODE/CASE 中的陷阱

CREATE TABLE tt (a1 CHAR(2), a2 VARCHAR2(2));
INSERT INTO tt VALUES ('A', 'A');
INSERT INTO tt VALUES ('A ', 'A ');  -- 第二筆 a1='A ', a2='A '

-- DECODE 對 CHAR 欄位比較時,將常數視為 VARCHAR2 處理(不補空格)
SELECT DECODE(a1, 'A', 'AAAA', 'BBBB') FROM tt;
-- 結果: 兩筆皆回傳 'BBBB' !因為 'A' != 'A ' (非補空格比較)

-- CASE 遵循 SQL 比較規則,CHAR 欄位補空格比較
SELECT CASE a1 WHEN 'A' THEN 'AAA' ELSE 'BBB' END FROM tt;
-- 結果: 兩筆皆回傳 'AAA' !因為 'A' 補空格後 = 'A '

結論: DECODE 內部將參數視為 VARCHAR2 處理,不遵循 CHAR 補空格比較規則;CASE 表達式則遵循標準 SQL 比較語意。這是極易造成 Bug 的陷阱。

3.3 集合運算與隱式轉換

CREATE TABLE t1 (name CHAR(10));
CREATE TABLE t2 (name CHAR(20));
INSERT INTO t1 VALUES ('1'); INSERT INTO t1 VALUES ('2'); ...
INSERT INTO t2 VALUES ('1'); INSERT INTO t2 VALUES ('2'); ...

SELECT name FROM t1 MINUS SELECT name FROM t2;  -- 有輸出 '1'..'5'
SELECT name FROM t1 INTERSECT SELECT name FROM t2; -- 無輸出

集合運算(UNION/INTERSECT/MINUS)採用 VARCHAR2 比較語意(不補空格),導致 CHAR(10) 的 '1........'(含 9 空格)與 CHAR(20) 的 '1...................' 不相等。集合運算不遵循 CHAR 比較規則。

四、選型建議與最佳實務

場景建議型別理由
固定長度代碼(國別碼、貨幣代碼、狀態碼)CHAR(n)長度固定、比較頻繁、索引效能佳、避免行遷移
變動長度文字(姓名、地址、描述、JSON、XML)VARCHAR2(n)節省空間、符合語意、12c+ 支援 32K
單一字元旗標(Y/N、M/F、0/1)CHAR(1) 或 CHARACTER語意明確、固定 1 字元、索引高選擇性
頻繁 UPDATE 長度變動大的欄位CHAR(n)避免行遷移;雖浪費空間但減少 I/O
PL/SQL 變數處理大量字串拼接VARCHAR2(32767) 或 CLOB動態分配、避免記憶體浪費

五、結語

CHAR 與 VARCHAR2 的核心差異在於「固定 vs 變動」儲存機制與「補空格 vs 精確」比較語意。CHARACTER 只是 CHAR 的語意化子型別。關鍵陷阱在於 DECODE 函數將 CHAR 參數視為 VARCHAR2 處理,以及集合運算採用非補空格比較。開發時務必:

  1. 根據資料特性選型,不盲目預設 VARCHAR2。
  2. 比較 CHAR 欄位時,明確使用 RPAD 或 TRIM 統一格式,避免隱式轉換驚喜。
  3. 避免在 DECODE 中直接比較 CHAR 欄位,改用 CASE 或預先 TRIM。
  4. 集合運算涉及 CHAR 欄位時,預期將以精確比較為準。

掌握這些細節,能避免 90% 的字串型別相關 Bug,提升程式碼健壯度。

copyright © 萬盛學電腦網 all rights reserved