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','@@'));
From:
無明之師