Oracle Split功能函数

在pl/sql时经常会碰到将某些特定的符号转换成行,这功能相当于split.以前在网上收集的split函数如下:

CREATE OR REPLACE TYPE DJ_STR_SPLIT IS TABLE OF VARCHAR2 (4000);

CREATE OR REPLACE PACKAGE Dj_Function_API IS
module_  CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'DjFunction';

Function  Get_Split_Change(
  str_                     IN VARCHAR2,
  delimiter_               IN VARCHAR2:=';')
Return Dj_Str_Split;

END DJ_FUNCTION_API;
/
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------ 
CREATE OR REPLACE PACKAGE BODY Dj_Function_API IS

---------------------------Get_Split_Change------------------------------------
Function  Get_Split_Change(
  str_                     IN VARCHAR2,
  delimiter_               IN VARCHAR2:=';')
    RETURN Dj_Str_Split
IS
    j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (4000);
    str_split Dj_Str_Split :=Dj_Str_Split();
BEGIN
    len := LENGTH (str_);
    len1 := LENGTH (delimiter_);

    WHILE j < len
    LOOP
        j := INSTR (str_, delimiter_, i);

        IF j = 0
        THEN
            j := len;
            str := SUBSTR (str_, i);
            str_split.EXTEND;
            str_split (str_split.COUNT) := str;

            IF i >= len
            THEN
                EXIT;
            END IF;
        ELSE
            str := SUBSTR (str_, i, j - i);
            i := j + len1;
            str_split.EXTEND;
            str_split (str_split.COUNT) := str;
        END IF;
    END LOOP;
    RETURN str_split;
END  Get_Split_Change;
------------------------------------------------------------------------    
END Dj_Function_API;
/

select * from table(Dj_Function_API.Get_Split_Change('11;22;33'))

结果:

11

22

33