Oracle 切割字符串

定义类型:

create or replace TYPE "SPLIT_TYPE"                                          IS TABLE OF VARCHAR2 (4000)

定义分割函数:

function func_split(p_str varchar2, p_delimiter varchar2) return split_type is
    j        INT := 0;
    i        INT := 1;
    len      INT := 0;
    len1     INT := 0;
    str      VARCHAR2(4000);
    my_split split_type := split_type();
  begin
    len  := LENGTH(p_str);
    len1 := LENGTH(p_delimiter);

    WHILE j < len LOOP
      j := INSTR(p_str, p_delimiter, i);

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

        IF i >= len THEN
          EXIT;
        END IF;
      ELSE
        str := SUBSTR(p_str, i, j - i);
        i   := j + len1;
        my_split.EXTEND;
        my_split(my_split.COUNT) := str;
      END IF;
    END LOOP;

    RETURN my_split;
  end;

使用:

select ROWNUM,COLUMN_VALUE from table(PKG_SHAREPOLICY.func_split('1@2@5555', '@'));

结果:

函数中使用:

FOR L_RECORD IN(select ROWNUM,COLUMN_VALUE from table(PKG_SHAREPOLICY.func_split(tsi_param, '@')))
      LOOP
        IF L_RECORD.ROWNUM=1 THEN
            p1:=L_RECORD.COLUMN_VALUE;
        ELSIF L_RECORD.ROWNUM=2 THEN
            p2:=L_RECORD.COLUMN_VALUE;
        ELSIF L_RECORD.ROWNUM=3 THEN
            p3:=L_RECORD.COLUMN_VALUE;
        END IF;
      END LOOP;