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' UNION SELECT * FROM TABLE(CAST(split('001,002',',') AS mytable)) );http://blog.csdn.net/believefym/article/details/1836162
发现函数和类型的定义
