Oracle自訂函數實戰：字串分割與Key-Value映射完整代碼

需求背景

Oracle內建函數雖強，但缺乏直接支援「字串分割為集合」與「字串解析為Key-Value映射」的功能。本文提供兩個生產級PL/SQL函數：F_SPLIT_STRING 與 F_PARSE_STRING_TO_MAP，分別解決逗號分隔值（CSV）轉集合、以及「k1=v1;k2=v2」格式轉映射表的需求。

核心物件型別定義

-- 字串集合型別
CREATE OR REPLACE TYPE T_VARCHAR2_LIST AS TABLE OF VARCHAR2(200);
/
-- 鍵值對物件型別
CREATE OR REPLACE TYPE T_STRING_ENTRY AS OBJECT (
    c_key   VARCHAR2(200),
    c_value VARCHAR2(200)
);
/
-- 鍵值對集合型別
CREATE OR REPLACE TYPE T_STRING_MAP AS TABLE OF T_STRING_ENTRY;
/

函數一：F_SPLIT_STRING — 字串分割為集合

CREATE OR REPLACE FUNCTION F_SPLIT_STRING(
    p_source     VARCHAR2,
    p_delimiter  VARCHAR2
) RETURN T_VARCHAR2_LIST AS
    v_list       T_VARCHAR2_LIST := T_VARCHAR2_LIST();
    v_tmp        VARCHAR2(4000);
    v_element    VARCHAR2(4000);
BEGIN
    v_tmp := p_source;
    WHILE INSTR(v_tmp, p_delimiter) > 0 LOOP
        v_element := SUBSTR(v_tmp, 1, INSTR(v_tmp, p_delimiter) - 1);
        v_tmp := SUBSTR(v_tmp, INSTR(v_tmp, p_delimiter) + LENGTH(p_delimiter));
        IF v_element IS NOT NULL AND v_element != p_delimiter THEN
            v_list.EXTEND(1);
            v_list(v_list.COUNT) := v_element;
        END IF;
    END LOOP;
    -- 處理最後一段
    IF v_tmp IS NOT NULL AND v_tmp != p_delimiter THEN
        v_list.EXTEND(1);
        v_list(v_list.COUNT) := v_tmp;
    END IF;
    RETURN v_list;
END F_SPLIT_STRING;
/

使用範例： SELECT * FROM TABLE(F_SPLIT_STRING('A,B,C', ',')); 返回三行：A、B、C。

函數二：F_PARSE_STRING_TO_MAP — 字串解析為鍵值映射表

CREATE OR REPLACE FUNCTION F_PARSE_STRING_TO_MAP(
    p_original_string   VARCHAR2,
    p_entry_delimiter   VARCHAR2,  -- 鍵值對分隔符，如 ';'
    p_key_delimiter     VARCHAR2   -- 鍵值分隔符，如 '='
) RETURN T_STRING_MAP AS
    v_entry_list  T_VARCHAR2_LIST;
    v_map         T_STRING_MAP := T_STRING_MAP();
    v_entry       T_STRING_ENTRY := T_STRING_ENTRY(NULL, NULL);
    v_pos         NUMBER(4);
BEGIN
    -- 先按 entry 分隔符分割
    v_entry_list := F_SPLIT_STRING(p_original_string, p_entry_delimiter);
    FOR i IN v_entry_list.FIRST .. v_entry_list.LAST LOOP
        v_pos := INSTR(v_entry_list(i), p_key_delimiter);
        IF v_pos > 0 THEN
            v_entry.c_key   := SUBSTR(v_entry_list(i), 1, v_pos - 1);
            v_entry.c_value := SUBSTR(v_entry_list(i), v_pos + LENGTH(p_key_delimiter));
            v_map.EXTEND;
            v_map(v_map.COUNT) := v_entry;
        END IF;
    END LOOP;
    RETURN v_map;
END F_PARSE_STRING_TO_MAP;
/

使用範例： SELECT * FROM TABLE(F_PARSE_STRING_TO_MAP('id=100;name=abc;type=X', ';', '=')); 返回三行鍵值對。

應用場景與擴展建議

• ETL過程中解析參數字串、日誌分析、動態SQL構建。
• 若處理超長字串（>4000字元），建議將變數改為CLOB並使用DBMS_LOB操作。
• 可進一步封裝為Package，提供GET_VALUE(p_map, p_key)等便利函數。