oracle split

1、创建一个类型

<span style="color: #0000ff;">CREATE</span> <span style="color: #808080;">OR</span> <span style="color: #ff00ff;">REPLACE</span> TYPE Strsplit_Type <span style="color: #0000ff;">IS</span> <span style="color: #0000ff;">TABLE</span> <span style="color: #0000ff;">OF</span> <span style="color: #0000ff;">VARCHAR2</span>(<span style="color: #800000; font-weight: bold;">4000</span>)

2、创建函数

<span style="color: #0000ff;">CREATE</span> <span style="color: #808080;">OR</span> <span style="color: #ff00ff;">REPLACE</span> <span style="color: #0000ff;">FUNCTION</span> Fun_Strsplit(p_Value <span style="color: #0000ff;">VARCHAR2</span><span style="color: #000000;">,
                                        p_Split </span><span style="color: #0000ff;">VARCHAR2</span> :<span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
</span><span style="color: #008080;">--</span><span style="color: #008080;">usage: select * from table(fun_strsplit(‘1,2,3,4,5‘))</span>
 <span style="color: #0000ff;">RETURN</span><span style="color: #000000;"> Strsplit_Type
  PIPELINED </span><span style="color: #0000ff;">IS</span><span style="color: #000000;">
  v_Idx       </span><span style="color: #0000ff;">INTEGER</span><span style="color: #000000;">;
  v_Str       </span><span style="color: #0000ff;">VARCHAR2</span>(<span style="color: #800000; font-weight: bold;">500</span><span style="color: #000000;">);
  v_Strs_Last </span><span style="color: #0000ff;">VARCHAR2</span>(<span style="color: #800000; font-weight: bold;">4000</span>) :<span style="color: #808080;">=</span><span style="color: #000000;"> p_Value;
</span><span style="color: #0000ff;">BEGIN</span><span style="color: #000000;">
  LOOP
    v_Idx :</span><span style="color: #808080;">=</span><span style="color: #000000;"> Instr(v_Strs_Last, p_Split);
    </span><span style="color: #0000ff;">EXIT</span> <span style="color: #0000ff;">WHEN</span> v_Idx <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">;
    v_Str       :</span><span style="color: #808080;">=</span> Substr(v_Strs_Last, <span style="color: #800000; font-weight: bold;">1</span>, v_Idx <span style="color: #808080;">-</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">);
    v_Strs_Last :</span><span style="color: #808080;">=</span> Substr(v_Strs_Last, v_Idx <span style="color: #808080;">+</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">);
    </span><span style="color: #0000ff;">PIPE</span><span style="color: #000000;"> ROW(v_Str);
  </span><span style="color: #0000ff;">END</span><span style="color: #000000;"> LOOP;
  </span><span style="color: #0000ff;">PIPE</span><span style="color: #000000;"> ROW(v_Strs_Last);
  </span><span style="color: #0000ff;">RETURN</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">END</span> Fun_Strsplit;

oracle split

标签：