ORACLE不能像MSSQL那樣支持直接返回表類型，所以要先創建一種自定義類型。這裏用到的是嵌套表（Nested Table）。
-- Nested Table
CREATE OR REPLACE TYPE split_str IS TABLE OF VARCHAR(100);
/
-- Function
CREATE OR REPLACE FUNCTION fn_Split
(
p_Str VARCHAR2,
p_Delimiter VARCHAR2
)
RETURN split_str PIPELINED
AS
v_Str VARCHAR(4000) := p_Str;
v_Index NUMBER;
v_SubLength NUMBER;
BEGIN
-- p_Delimiter is 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_SubLength := LENGTH(v_Str) - (v_index + LENGTH(p_Delimiter) - 1);
v_Str := SUBSTR(v_Str,-v_SubLength,v_SubLength);
v_index := INSTR(v_Str,p_Delimiter);
END LOOP;
PIPE ROW(v_Str);
RETURN;
END;
/
-- 調用（第二種方式更好）
SELECT fn_split('This@@is@@it','@@') FROM dual;
SELECT * FROM Table(fn_split('This@@is@@it','@@'));
更多信息请查看IT技术专栏
