字符串分割 函数

CREATEORREPLACETYPE mytable ASTABLEOFvarchar2(100) 

 

CREATEORREPLACEFUNCTIONsplit

   (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>0THEN

      a.extend;

      a(a.Count) := Trim(SubStr(psrc, j,i-j));

      j := i+1;

      --Dbms_Output.put_line(a(a.Count-1));

    ENDIF;

    EXITWHEN i=0;

  ENDLOOP;

  IF j < Length(psrc) THEN

    a.extend;

    a(a.Count) := Trim(SubStr(psrc, j,Length(psrc)+1-j));

  ENDIF;

  RETURN a;

END;

数组作为select in的查询条件

SELECT * FROM student WHEREidIN (SELECT * FROMTABLE(CAST(split('001,002', ',')AS mytable)));

相关链接：http://blog.csdn.net/believefym/article/details/3791122