ORACLE存储过程-分割字符串.docVIP

create or replace procedure split (is_sourcestr IN VARCHAR2, is_regstr IN VARCHAR2) IS sourcestr_start int := 0; --定义数组类型 TYPE table_name_type IS TABLE OF VARCHAR2 (32); --定义数组对象 va_table_name table_name_type; --截取字符串 sourcestr_next int := 0; vs_sourcestr2 varchar2(50); regstr_length int:=0; --数组长度 vs_varray_length int:=0; BEGIN regstr_length:=length(is_regstr); vs_sourcestr2 := is_sourcestr; va_table_name := table_name_type(is_sourcestr); while regstr_length0 LOOP sourcestr_next := instr(vs_sourcestr2, is_regstr); IF sourcestr_next 0 THEN if sourcestr_start = 0 then sourcestr_start := 1; else --扩展数组 va_table_name.extend(1); end if; --数组赋值 va_table_name(va_table_name.last) := substr(vs_sourcestr2,0,sourcestr_next-1); sourcestr_next:=sourcestr_next+1; vs_sourcestr2 := substr(vs_sourcestr2,sourcestr_next); ELSIF sourcestr_next = 0 THEN if sourcestr_start != 0 then va_table_name.extend(1); end if; va_table_name(va_table_name.last) := vs_sourcestr2; exit; END IF; END LOOP; --获得数组长度 vs_varray_length :=va_table_name.count;  --如果第一个源参数只有一个字符，没有分隔符防止下标越界 IF sourcestr_start = 0 THEN sourcestr_start := sourcestr_start + 1; END IF; -- 循环数组内成员，并且进行相关操作 while vs_varray_length+1 sourcestr_start LOOP dbms_output.put_line(va_table_name(sourcestr_start)); --Execute immediate vs_sqlTmp;  --commit; sourcestr_start := sourcestr_start + 1; END LOOP; END split;