oracle 分解字符串函数返回数组 在存储过程中调用该函数

create or replace package qoe_public
is

?? TYPE split_type IS TABLE OF VARCHAR2(100);

end qoe_public;

?

?

?

?

?

create or replace function qoe_str_split(var_str in varchar2,var_split in varchar2)
? return QOE_PUBLIC.split_type --根据‘,’分解字符串返回数组
IS
? var_tmp????? varchar2(4000);
? var_element? varchar2(4000);
? result_type? QOE_PUBLIC.split_type;
begin
??? var_tmp:=var_str;
??? result_type:=QOE_PUBLIC.split_type();
??? WHILE instr(var_tmp,var_split)>0
??? LOOP
??????? var_element:=substr(var_tmp,1,instr(var_tmp,var_split)-1);
??????? var_tmp:=substr(var_tmp,
??????????????????????? instr(var_tmp, var_split) + length(var_split),
??????????????????????? length(var_tmp));
??????? result_type.extend;
??????? result_type(result_type.count) := var_element;
??? END LOOP;
??? result_type.extend;
??? result_type(result_type.count):=var_tmp;
???
??? /**
??? --测试
??? FOR i IN 1..result_type.count
??? LOOP
????? dbms_output.put_line(result_type(i));
????? dbms_output.put_line('字符串长度 = '|| TO_CHAR(nvl(length(result_type(i)), 0)));
??? END LOOP;
??? **/

??? RETURN result_type;
end qoe_str_split;

?

?

?

?

create or replace procedure qe_split_test(name in varchar2)
IS
?? result_type QOE_PUBLIC.split_type;
BEGIN
??
?? result_type:=qoe_str_split(name,',');--调用函数返回数组
??
?? dbms_output.put_line(result_type(1));
?? dbms_output.put_line(result_type(2));
??
end qe_aas;

我的异常网推荐解决方案：oracle存储过程,http://www.aiyiweb.com/oracle-develop/177537.html