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('[email protected]@[email protected]@it','@@') FROM dual; SELECT * FROM Table(fn_split('[email protected]@[email protected]@it','@@'));
