分割字符串返回数组

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))   
); 
  

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/believefym/archive/2009/01/15/3791122.aspx