萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 字符串分割函數:PIPELINED TABLE FUNCTION 返回結果集

為什麼需要自定義類型?

Oracle 無法直接返回表類型,必須先建立 Nested Table Type 或 VARRAY。

完整實作

-- 1. 定義巢狀表類型
CREATE OR REPLACE TYPE SPLIT_STR IS TABLE OF VARCHAR2(100);
/

-- 2. 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_INDEX     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_INDEX := INSTR(V_STR, P_DELIMITER);
  WHILE V_INDEX <> 0 LOOP
    PIPE ROW(SUBSTR(V_STR, 1, V_INDEX - 1));
    V_SUBLEN := LENGTH(V_STR) - (V_INDEX + LENGTH(P_DELIMITER) - 1);
    V_STR := SUBSTR(V_STR, -V_SUBLEN, V_SUBLEN);
    V_INDEX := INSTR(V_STR, P_DELIMITER);
  END LOOP;
  PIPE ROW(V_STR);  -- 最後一段
  RETURN;
END FN_SPLIT;
/

-- 3. 調用方式
-- 方式一:SELECT 直接呼叫(單列)
SELECT FN_SPLIT('This@@is@@it', '@@') FROM DUAL;

-- 方式二:TABLE() 轉為關係結果集(推薦,可 JOIN、WHERE)
SELECT * FROM TABLE(FN_SPLIT('This@@is@@it', '@@'));

關鍵點解析

  • PIPELINED:流式返回,無需在記憶體構建整個集合,適合大量資料。
  • PIPE ROW:逐行產出結果。
  • TABLE():將集合轉為虛擬表,可在 SQL 中像表一樣使用。

效能提示

  • 避免在高頻 SQL 中逐行分割大字串,考慮應用層預處理或正則 REGEXP_SUBSTR(10g+)。
  • 12c+ 可用 JSON_TABLE 或 XMLTABLE 解析結構化分隔字串。
copyright © 萬盛學電腦網 all rights reserved