Oracle 字串分割函數：自訂型別回傳集合、PIPELINED 表函數實作

Oracle 無內建 SPLIT_STRING 回傳表型別，需自建 Nested Table 型別 + PIPELINED Table Function 實現「一行變多行」分割邏輯。本文提供完整型別定義、函數實作 (支援多字元分隔符、NULL 分隔符逐字分割)、調用方式 (TABLE() 轉換) 與效能說明。

一、建立集合型別

CREATE OR REPLACE TYPE split_str IS TABLE OF VARCHAR2(100);
/
-- 此為 Nested Table 型別，可用於 TABLE() 操作符轉換為關聯式結果集

二、分割函數實作 (PIPELINED)

CREATE OR REPLACE FUNCTION fn_split (
  p_str       VARCHAR2,
  p_delimiter VARCHAR2
) RETURN split_str PIPELINED AS
  v_str      VARCHAR2(4000) := p_str;
  v_idx      NUMBER;
  v_sublen   NUMBER;
BEGIN
  -- 分隔符為 NULL：逐字元分割
  IF p_delimiter IS NULL THEN
    FOR x IN 1 .. LENGTH(v_str) LOOP
      PIPE ROW(SUBSTR(v_str, x, 1));
    END LOOP;
    RETURN;
  END IF;

  -- 一般分割邏輯
  v_idx := INSTR(v_str, p_delimiter);
  WHILE v_idx <> 0 LOOP
    PIPE ROW(SUBSTR(v_str, 1, v_idx - 1));
    v_sublen := LENGTH(v_str) - (v_idx + LENGTH(p_delimiter) - 1);
    v_str := SUBSTR(v_str, -v_sublen, v_sublen);
    v_idx := INSTR(v_str, p_delimiter);
  END LOOP;
  PIPE ROW(v_str);  -- 最後一段
  RETURN;
END;
/

三、調用方式

-- 方式 1：標量子查詢 (單行單欄回傳集合物件，較少用)
SELECT fn_split('This@@is@@it', '@@') FROM dual;

-- 方式 2：TABLE() 轉換為關聯式結果集 (標準用法，推薦)
SELECT * FROM TABLE(fn_split('This@@is@@it', '@@'));

-- 結果：
-- COLUMN_VALUE
-- ------------
-- This
-- is
-- it

四、進階擴展建議

五、純 SQL 替代方案 (11gR2+，無需建型別)

WITH data AS (SELECT 'This@@is@@it' AS str FROM dual)
SELECT REGEXP_SUBSTR(str, '[^@@]+', 1, LEVEL) AS token
FROM data
CONNECT BY REGEXP_SUBSTR(str, '[^@@]+', 1, LEVEL) IS NOT NULL;

優點：無物件依賴、優化器可推演、適合即席查詢。缺點：極大資料量時正則效能略低於 PL/SQL 迴圈。

六、效能與使用提醒

• PIPELINED 函數**逐行產出**，記憶體佔用低，適合大量資料流式處理。
• 函數內**避免 DML/COMMIT**，保持純查詢語意，否則在並行查詢中會報錯。
• 集合型別建立於 Schema 層級，跨 Schema 呼叫需授權 EXECUTE ON split_str。