日期:2014-05-16 浏览次数:20821 次
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;