ORACLE中的SPLIT

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-627014/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-627014/