逗号分割的字符串转成表

1.創建一個TYPE:

  CREATE OR REPLACE TYPE "TOOLS_STRLIST"

2.創建一個Function:

  create or replace function Tools_Str2List(str_in in varchar2) return Tools_StrList  is
   v_Str   Long Default Str_In || ',';
   v_Index Number;
   v_List  Tools_StrList := Tools_StrList();
  Begin
  Loop
   v_Index := Instr(v_Str, ',');
   Exit When(Nvl(v_Index, 0) = 0);
   v_List.Extend;
   v_List(v_List.Count) := Trim(Substr(v_Str, 1, v_Index - 1));
   v_Str := Substr(v_Str, v_Index + 1);
  End Loop;
  Return v_List;
End;

3.使用:SELECT * FROM TABLE(Tools_Str2List('1,2,3'));