Oracle 自定義 SPLIT 與 SPLITSTR 函數：高性能字符串分割與節點提取實戰方案

業務痛點與傳統方案缺陷

批量操作場景下，前端傳入逗號分隔 ID 字符串（如 '1001,1002,1003'），後端需解析處理。傳統方案存在嚴重性能瓶頸：

核心思路：管道函數 + SQL 引擎原生處理

利用 Oracle Pipelined Table Function 將字符串分割轉化為虛擬表，直接參與 SQL 查詢，消除中間表 IO，充分發揮優化器並行能力。

完整實現代碼

1. 定義集合類型

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(32767);

2. SPLIT 函數：字符串轉集合

CREATE OR REPLACE FUNCTION split (
    p_list  IN CLOB,
    p_sep   IN VARCHAR2 DEFAULT ','
) RETURN varchar2_ntt PIPELINED
IS
    l_idx     PLS_INTEGER;
    v_list    VARCHAR2(32767) := p_list;
BEGIN
    IF v_list IS NULL THEN
        RETURN;
    END IF;
    
    LOOP
        l_idx := INSTR(v_list, p_sep);
        IF l_idx > 0 THEN
            PIPE ROW(SUBSTR(v_list, 1, l_idx - 1));
            v_list := SUBSTR(v_list, l_idx + LENGTH(p_sep));
        ELSE
            PIPE ROW(v_list);
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END split;

3. SPLITSTR 函數：提取指定節點

CREATE OR REPLACE FUNCTION splitstr (
    p_str  IN CLOB,
    p_idx  IN NUMBER DEFAULT 0,
    p_sep  IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2
IS
    v_count NUMBER;
    v_str   VARCHAR2(4000);
BEGIN
    -- 參數 0 返回原字符串
    IF p_idx = 0 THEN
        RETURN p_str;
    END IF;
    
    -- 分隔符不存在直接返回分隔符
    IF INSTR(p_str, p_sep) = 0 THEN
        RETURN p_sep;
    END IF;
    
    -- 統計分割後元素總數
    SELECT COUNT(*) INTO v_count FROM TABLE(split(p_str, p_sep));
    
    -- 索引越界返回 NULL
    IF p_idx > v_count OR p_idx < 1 THEN
        RETURN NULL;
    END IF;
    
    -- 精確提取第 N 個元素
    SELECT column_value INTO v_str
    FROM (
        SELECT column_value, ROWNUM rn FROM TABLE(split(p_str, p_sep))
    )
    WHERE rn = p_idx;
    
    RETURN v_str;
END splitstr;

使用示例與效果驗證

場景一：IN 列表動態綁定

-- 傳統寫法需動態拼接 SQL，有注入風險
-- 新寫法：安全、可複用執行計劃
SELECT * FROM orders
WHERE order_id IN (SELECT column_value FROM TABLE(split('1001,1002,1003')));

場景二：批量更新業務鍵

UPDATE customer SET status = 'VIP'
WHERE cust_id IN (SELECT column_value FROM TABLE(split(:p_cust_ids)));

場景三：提取第 N 個元素

SELECT splitstr('A,B,C,D', 2) FROM dual;  -- 返回 'B'
SELECT splitstr('A|B|C', 3, '|') FROM dual;  -- 返回 'C'
SELECT splitstr('ONLY_ONE', 2) FROM dual;  -- 返回 NULL

性能對比測試（10萬條 ID 分割）

進階優化技巧

支持 CLOB 超長字符串

-- split 函數已使用 CLOB 參數，支持 4GB 字符串
-- 但 PIPE ROW 需 VARCHAR2，單元素限長 32767
-- 極長元素需改用 BLOB 或分塊處理

並行執行支持

-- 函數聲明添加 PARALLEL_ENABLE
CREATE OR REPLACE FUNCTION split(...) RETURN ... PIPELINED PARALLEL_ENABLE
-- 允許優化器並行調用，大數據量分割顯著加速

緩存優化

-- 結果集緩存（12c+）
SELECT /*+ RESULT_CACHE */ * FROM TABLE(split(:p_ids));

遷移指南：從臨時表方案平滑切換

1. 部署函數到數據庫（需 CREATE PROCEDURE 權限）
2. 修改應用代碼：將 INSERT INTO temp_table ... 替換為 TABLE(split(...))
3. 驗證執行計劃：確認出現 COLLECTION ITERATOR PICKLER FETCH 操作
4. 壓測對比：關注邏輯讀、CPU、響應時間指標
5. 全量切換：灰度發布，保留回滾方案

常見問題 FAQ