Oracle字串分割三種實作方案：自訂函數、PIPELINED、REGEXP_CONNECT BY對比

從傳統集合函數到正則遞歸查詢，三種字串分割技術完整對比

Oracle無內建SPLIT，常見三種實作路徑各有優劣。本文提供完整代碼、效能特性與選型建議。

方案一：傳統集合型別函數(非PIPELINED)

CREATE OR REPLACE TYPE str_split AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION splitstr(p_string VARCHAR2, p_delimiter VARCHAR2)
RETURN str_split
AS
  v_list str_split := str_split();
  v_start NUMBER := 1;
  v_pos   NUMBER;
BEGIN
  LOOP
    v_pos := INSTR(p_string, p_delimiter, v_start);
    IF v_pos = 0 THEN
      v_list.EXTEND;
      v_list(v_list.COUNT) := SUBSTR(p_string, v_start);
      EXIT;
    ELSE
      v_list.EXTEND;
      v_list(v_list.COUNT) := SUBSTR(p_string, v_start, v_pos-v_start);
      v_start := v_pos + 1;
    END IF;
  END LOOP;
  RETURN v_list;
END;
/
-- 使用：SELECT * FROM TABLE(splitstr('a,b,c', ','));

• 優點：邏輯直觀、相容舊版本
• 缺點：需完整構建集合再返回，記憶體佔用大、無法流式處理

方案二：PIPELINED函數(流式輸出，推薦)

CREATE OR REPLACE TYPE str_split AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION splitstr_pipe(p_string VARCHAR2, p_delimiter VARCHAR2)
RETURN str_split PIPELINED
AS
  v_len   NUMBER := LENGTH(p_string);
  v_start NUMBER := 1;
  v_pos   NUMBER;
BEGIN
  WHILE v_start <= v_len LOOP
    v_pos := INSTR(p_string, p_delimiter, v_start);
    IF v_pos = 0 THEN
      PIPE ROW(SUBSTR(p_string, v_start));
      EXIT;
    ELSE
      PIPE ROW(SUBSTR(p_string, v_start, v_pos-v_start));
      v_start := v_pos + 1;
    END IF;
  END LOOP;
  RETURN;
END;
/
-- 使用：SELECT * FROM TABLE(splitstr_pipe('a,b,c', ','));

• 優點：逐行產出，低記憶體、可管道化並行、支援FETCH FIRST N ROWS提前終止
• 缺點：需PIPELINED關鍵字，10g+支援

方案三：REGEXP_SUBSTR + CONNECT BY(純SQL，無需建立物件)

SELECT DBMS_LOB.SUBSTR(
         REGEXP_SUBSTR('abc,def,ggg,1,2,3,4,5', '[^,]+', 1, x.n)
       ) AS token
FROM DUAL,
     (SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM <= 100) x
WHERE REGEXP_SUBSTR('abc,def,ggg,1,2,3,4,5', '[^,]+', 1, x.n) IS NOT NULL;

• 優點：零物件部署、臨時查詢極便利、正則表達式靈活支援多字元分隔符
• 缺點：CONNECT BY遞歸有上限(預設100/最大MAX_RECURSION_DEPTH)、大量資料效能較差
• 關鍵點：'[^,]+' 為「非逗號字元一或多個」；ROWNUM <= 100 限制最大分割段數

效能與選型建議

12c/19c 新選項

• JSON_TABLE：將JSON陣列轉關聯行
• XMLTABLE：XML路徑提取
• LISTAGG逆向操作(行轉字串)