oracle split

1、创建一个类型

CREATE OR REPLACE TYPE Strsplit_Type IS TABLE OF VARCHAR2(4000)

2、创建函数

CREATE OR REPLACE FUNCTION Fun_Strsplit(p_Value VARCHAR2,
                                        p_Split VARCHAR2 := ‘,‘)
--usage: select * from table(fun_strsplit(‘1,2,3,4,5‘))
 RETURN Strsplit_Type
  PIPELINED IS
  v_Idx       INTEGER;
  v_Str       VARCHAR2(500);
  v_Strs_Last VARCHAR2(4000) := p_Value;

BEGIN
  LOOP
    v_Idx := Instr(v_Strs_Last, p_Split);
    EXIT WHEN v_Idx = 0;
    v_Str       := Substr(v_Strs_Last, 1, v_Idx - 1);
    v_Strs_Last := Substr(v_Strs_Last, v_Idx + 1);
    PIPE ROW(v_Str);
  END LOOP;
  PIPE ROW(v_Strs_Last);
  RETURN;

END Fun_Strsplit;

oracle split

标签：