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..net/oracle-develop/177537.html