Oracle split(分隔字符串函数)

一 Oracle版本: 10g PLSQL: 8.0
二 具体代码如下:
1 类型

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

2 函数

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
  RETURN ty_str_split
IS
  j INT := 0;
  i INT := 1;
  len INT := 0;
  len1 INT := 0;
  str VARCHAR2 (4000);
  str_split ty_str_split := ty_str_split ();
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);
        str_split.EXTEND;
        str_split (str_split.COUNT) := str;

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

  RETURN str_split;
END fn_split;

3 具体应用

CREATE OR REPLACE procedure testsplit(objids in varchar2,message out varchar2)
as
objid varchar2(32);
begin
 for x in (select  column_value as objid from (SELECT * FROM TABLE (CAST (fn_split (objids, ',') AS ty_str_split)))t) loop
       objid := x.objid;
       --执行删除或者有关与ID相关的操作
       --delete t wher id=objid;
       --commit;
       message := message || ' ID:' || objid;    
 end loop;
EXCEPTION
  WHEN OTHERS THEN
  message :='测试split函数失败...';
end testsplit;

其它运用如下：

select t.column_value,rownum row_ from  
table(  
    cast (  
        fn_split ('1,2,3,4,5,,7,,', ',') as ty_str_split  
    )  
) t