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 中像表一樣使用。REGEXP_SUBSTR(10g+)。JSON_TABLE 或 XMLTABLE 解析結構化分隔字串。