ORACLE不能像MSSQL那樣支持直接返回表類型，所以要先創建一種自定義類型。這裏用到的是嵌套表（Nested Table）。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- 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','@@'));
