Oracle 自定義字符串分割函數：PIPELINED 實現行轉列

分割函數完整代碼

CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION splitstr(
  p_string    IN VARCHAR2,
  p_delimiter IN VARCHAR2
) RETURN str_split PIPELINED AS
  v_length  NUMBER := LENGTH(p_string);
  v_start   NUMBER := 1;
  v_index   NUMBER;
BEGIN
  WHILE v_start <= v_length LOOP
    v_index := INSTR(p_string, p_delimiter, v_start);
    IF v_index = 0 THEN
      PIPE ROW(SUBSTR(p_string, v_start));
      v_start := v_length + 1;
    ELSE
      PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
      v_start := v_index + 1;
    END IF;
  END LOOP;
  RETURN;
END splitstr;
/
-- 測試
SELECT * FROM TABLE(splitstr('Hello,Cnblogs!', ','));
-- 輸出兩行：Hello / Cnblogs!

進階：將分割行轉為多列顯示

SELECT a.column_value v1, b.column_value v2
FROM (SELECT * FROM (SELECT ROWNUM rn, t.* FROM TABLE(splitstr('Hello,Cnblogs!', ',')) t)) a,
     (SELECT * FROM (SELECT ROWNUM rn, t.* FROM TABLE(splitstr('Hello,Cnblogs!', ',')) t)) b
WHERE a.rn = 1 AND b.rn = 2;
-- 結果：v1=Hello, v2=Cnblogs!

原理：兩次調用分割函數，分別取第 1、2 行通過 ROWNUM 連接。實際項目中建議將分割結果存入臨時表或集合變量避免重複計算。