[Oracle]分割字符串返回数组

CREATE OR REPLACE TYPE mytable AS TABLE OF varchar2(100)/

 

CREATE OR REPLACE FUNCTION split (src VARCHAR2, delimiter varchar2) RETURN mytable IS psrc VARCHAR2(500); a mytable := mytable(); i NUMBER := 1; -- j NUMBER := 1;BEGIN psrc := RTrim(LTrim(src, delimiter), delimiter); LOOP i := InStr(psrc, delimiter, j); --Dbms_Output.put_line(i); IF i>0 THEN a.extend; a(a.Count) := Trim(SubStr(psrc, j, i-j)); j := i+1; --Dbms_Output.put_line(a(a.Count-1)); END IF; EXIT WHEN i=0; END LOOP; IF j < Length(psrc) THEN a.extend; a(a.Count) := Trim(SubStr(psrc, j, Length(psrc)+1-j)); END IF; RETURN a;END;/

 

数组作为select in的查询条件

SELECT * FROM student WHERE id IN (SELECT * FROM TABLE(CAST(split('001,002', ',')AS mytable)));

 

SELECT * FROM student WHERE id IN(SELECT id FROM student WHERE id='001'UNIONSELECT * FROM TABLE(CAST(split('001,002',',') AS mytable)));