create or replace function f_split
(
/*功能:分割字符串
实现:通过分割符对字符串进行分割
编写人: 20111205
*/
var_source_string in varchar2,--要分割的字符串
var_separator in varchar2,--分隔符
var_number in number:=0 --输分割的第几个值,如果为0则表示全部输出
)
return TABLETYPE_VARCHAR2 is
v_source_string varchar2(2000);
v_separator_position integer;--分割符位置
v_source_length integer;--要分割的字符串的长度
v_separator_length integer;--分割符的长度
v_result TABLETYPE_VARCHAR2:=TABLETYPE_VARCHAR2();
v_subset varchar2(2000);
v_count integer:=0;
begin
if trim(var_separator) is null then
raise_application_error(-20001,'必须输入分割符!');
end if;
v_source_string:=trim(var_source_string);
--判断分割符的位置
v_separator_position:=instr(v_source_string,var_separator);
--分隔符的长度
v_separator_length:=length(var_separator);
--源分割字符串的长度
v_source_length:=length(var_source_string);
--源字符串最后必须有分隔符
if substr(v_source_string,v_source_length-v_separator_length+1,v_separator_length)<>var_separator then
raise_application_error(-20002,''''||v_source_string||'''字符串最后必须有分隔符!');
end if;
while v_separator_position>0 loop
--截取分割后的字符串
v_subset:=substr(v_source_string,1,v_separator_position-1);
if trim(v_subset) is not null then
v_result.extend;--数组添加一个空下标
v_result(v_result.last):=v_subset;--为数组最后一位赋值
end if;
v_source_string:=substr(v_source_string,v_separator_position+v_separator_length);
v_separator_position:=instr(v_source_string,var_separator,1);
v_count:=v_count+1;
if var_number=v_count then
v_result.delete;
v_result.extend;--数组添加一个空下标
v_result(v_result.last):=v_subset;--为数组最后一位赋值
return(v_result);
end if;
end loop;
return(v_result);
end f_split;
/*
测试代码:
declare
-- Local variables here
i integer;
results tabletype_varchar2;
begin
-- Test statements here
results:=f_split('"AAB999":"123","AAB004":"123","AAB003":"123","AAE006":"",
"AAE048":"","AAE051":"","AAE049":"","AAE047":"","AAB007":"","AAB008":"","AAB009":"",
"AAB023":"","AAB013":"123","AAB014":"123","AAE159":"","AAE007":"","AAB030":"123",
"AAZ066":"123","AAE004":"","AAE005":"","AAZ198":"","AAB301":"","AAB078":"",
"AAE013":"","AAB022":"","AAB021":"","AAB065":"","AAB006":"5","AAA149":"2",
"AAB020":"143","AAB019":"91","AAE011":"经办人","AAA027":"统筹区编码",', ',',7);
for i in 1 .. results.count loop
dbms_output.put_line(results(i));
end loop;
end;
*/
/
(
/*功能:分割字符串
实现:通过分割符对字符串进行分割
编写人: 20111205
*/
var_source_string in varchar2,--要分割的字符串
var_separator in varchar2,--分隔符
var_number in number:=0 --输分割的第几个值,如果为0则表示全部输出
)
return TABLETYPE_VARCHAR2 is
v_source_string varchar2(2000);
v_separator_position integer;--分割符位置
v_source_length integer;--要分割的字符串的长度
v_separator_length integer;--分割符的长度
v_result TABLETYPE_VARCHAR2:=TABLETYPE_VARCHAR2();
v_subset varchar2(2000);
v_count integer:=0;
begin
if trim(var_separator) is null then
raise_application_error(-20001,'必须输入分割符!');
end if;
v_source_string:=trim(var_source_string);
--判断分割符的位置
v_separator_position:=instr(v_source_string,var_separator);
--分隔符的长度
v_separator_length:=length(var_separator);
--源分割字符串的长度
v_source_length:=length(var_source_string);
--源字符串最后必须有分隔符
if substr(v_source_string,v_source_length-v_separator_length+1,v_separator_length)<>var_separator then
raise_application_error(-20002,''''||v_source_string||'''字符串最后必须有分隔符!');
end if;
while v_separator_position>0 loop
--截取分割后的字符串
v_subset:=substr(v_source_string,1,v_separator_position-1);
if trim(v_subset) is not null then
v_result.extend;--数组添加一个空下标
v_result(v_result.last):=v_subset;--为数组最后一位赋值
end if;
v_source_string:=substr(v_source_string,v_separator_position+v_separator_length);
v_separator_position:=instr(v_source_string,var_separator,1);
v_count:=v_count+1;
if var_number=v_count then
v_result.delete;
v_result.extend;--数组添加一个空下标
v_result(v_result.last):=v_subset;--为数组最后一位赋值
return(v_result);
end if;
end loop;
return(v_result);
end f_split;
/*
测试代码:
declare
-- Local variables here
i integer;
results tabletype_varchar2;
begin
-- Test statements here
results:=f_split('"AAB999":"123","AAB004":"123","AAB003":"123","AAE006":"",
"AAE048":"","AAE051":"","AAE049":"","AAE047":"","AAB007":"","AAB008":"","AAB009":"",
"AAB023":"","AAB013":"123","AAB014":"123","AAE159":"","AAE007":"","AAB030":"123",
"AAZ066":"123","AAE004":"","AAE005":"","AAZ198":"","AAB301":"","AAB078":"",
"AAE013":"","AAB022":"","AAB021":"","AAB065":"","AAB006":"5","AAA149":"2",
"AAB020":"143","AAB019":"91","AAE011":"经办人","AAA027":"统筹区编码",', ',',7);
for i in 1 .. results.count loop
dbms_output.put_line(results(i));
end loop;
end;
*/
/