Oracle PL/SQL 字串分割函數：依分隔符切割並逐行處理

Oracle 無內建 SPLIT 函數，常見需求：將傳入的逗點分隔字串（如 'A,B,C'）拆解為多行處理。本文提供純 PL/SQL 實現，使用 REGEXP_SUBSTR 正則提取第 N 個片段，搭配 REGEXP_REPLACE 計算片段數，無需建立臨時表或型別。

完整 PL/SQL 區塊

DECLARE
    p_str      VARCHAR2(1000) := 'a,c,g';  -- 輸入字串
    l_count    NUMBER := 0;                -- 片段數
    l_str      VARCHAR2(100);              -- 單一片段
BEGIN
    -- 1. 計算分隔符出現次數 + 1 = 片段數
    -- REGEXP_REPLACE 移除非分隔符字元，剩下的長度即分隔符個數
    SELECT NVL(LENGTH(REGEXP_REPLACE(p_str, '[^,]', '')) + 1, 0)
    INTO l_count
    FROM dual;

    -- 2. 單一片段直接輸出
    IF l_count = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Data: ' || p_str);
    END IF;

    -- 3. 多片段迴圈提取
    IF l_count > 1 THEN
        FOR i IN 1 .. l_count LOOP
            -- REGEXP_SUBSTR 取第 i 個非分隔符序列
            SELECT REGEXP_SUBSTR(p_str, '[^,]+', 1, i)
            INTO l_str
            FROM dual;
            DBMS_OUTPUT.PUT_LINE('Data' || i || ':' || l_str);
        END LOOP;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Error msg: ' || SQLERRM);
END;
/

執行結果

Data1:a
Data2:c
Data3:g

關鍵正則解析

進階：封裝為可重用函數

CREATE OR REPLACE FUNCTION split_string(
    p_str IN VARCHAR2,
    p_delim IN VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED IS
    l_count NUMBER;
BEGIN
    SELECT NVL(LENGTH(REGEXP_REPLACE(p_str, '[^' || p_delim || ']', '')) + 1, 0)
    INTO l_count FROM dual;

    FOR i IN 1 .. l_count LOOP
        PIPE ROW(REGEXP_SUBSTR(p_str, '[^' || p_delim || ']+', 1, i));
    END LOOP;
    RETURN;
END;
/
-- 使用方式：
SELECT * FROM TABLE(split_string('apple,banana,orange'));
-- 輸出三行：apple / banana / orange

效能提示

• 適合短字串、低頻呼叫（如報表參數解析）。
• 高頻/大量資料建議改用 XMLTABLE、JSON_TABLE (12c+) 或預先正規化存儲。
• 分隔符若為特殊正則字元（如 |, .），需轉義 '\\|'、'\\.'。

12c+ 更簡潔寫法

SELECT TRIM(COLUMN_VALUE)
FROM XMLTABLE(('"' || REPLACE('a,b,c', ',', '","') || '"'));  -- 需確保無雙引號衝突