oracle split 方法

--create or replace type pos_row as object
(
  pos_value VARCHAR2(40),
  digit     VARCHAR2(10)
)


--CREATE OR REPLACE TYPE v_respos IS TABLE OF pos_row

--CREATE OR REPLACE Function Str_Splits(Instring  In Varchar2,
                                      Mark_Char In Varchar2)
  Return v_Respos
  Pipelined Is
  s_Count       Int;
  e_Count       Int;
  Str_Len       Int;
  Section_Count Int;
  Prstring      Varchar2(2048);
  v_Retrow      Pos_Row;
  v_Restr       Varchar2(100);
  v_Sqlcode     Varchar2(500);
  v_Sqlerrm     Varchar2(1500);
Begin
  Prstring      := Instring;
  Str_Len       := Length(Prstring);
  Section_Count := 0;

  While Instr(Prstring, Mark_Char, 1, 1) <> 0 Loop
    s_Count       := 1;
    e_Count       := Instr(Prstring, Mark_Char, s_Count, 1);
    v_Restr       := Substr(Prstring, s_Count, e_Count - 1);
    Section_Count := Section_Count + 1;

    Str_Len := Str_Len - e_Count;

    Prstring := Substr(Prstring, e_Count + 1, Str_Len);
    v_Retrow := Pos_Row(v_Restr, Section_Count);
    Pipe Row(v_Retrow);
  End Loop;

  If Length(Prstring) > 0 Then
    v_Retrow := Pos_Row(Prstring, Section_Count + 1);
    Pipe Row(v_Retrow);
  End If;
  Return;
End;

--如何使用

/*首先申明變量
v_lots v_Respos;

其次
select Str_Splits(lots, '|') into v_lots from dual;
select * from table(v_lots)*/