pl/sql 兑现类似于split 的分割字符串的功能

1、先建立一个包，定义数组类型：
CREATE OR REPLACE PACKAGE pkg_string is

? -- Purpose : 字符串处理

? -- Public type declarations
? TYPE StringArray IS VARRAY(2000) OF VARCHAR2(2000);

END pkg_string;

2、拆分字符串的通用函数：
CREATE OR REPLACE FUNCTION fun_ParseToArray(
? a_SourceString? IN VARCHAR2,??? --源字符串
? a_Delimiter???? IN VARCHAR2???? --拆分的分隔符
) RETURN pkg_string.StringArray IS
-- ********************************************************
-- 根据分隔符拆分字符串为数组
-- ********************************************************
v_Result????? pkg_string.StringArray; --数组
iCount??????? INTEGER;
iBegin??????? INTEGER;
sValue??????? VARCHAR2(2000);
sSplitString? VARCHAR2(2000);
BEGIN
??? --检查参数
??? IF a_SourceString IS NULL OR a_Delimiter IS NULL THEN
????? RETURN(v_Result);
??? END IF;
??? IF a_SourceString = '' THEN
????? RETURN(v_Result);
??? END IF;
??? --初始化数组
??? v_Result := pkg_string.StringArray();
??? --Only one entry was found
??? IF instr(a_SourceString,a_Delimiter) = 0 THEN
????? -- 数组加1维
????? v_Result.extend();
????? -- 将数据存入数组
????? v_Result(1) := a_SourceString;
????? RETURN(v_Result);
??? END IF;
??? iCount := 0;
??? sSplitString := a_SourceString;
??? LOOP
??????? iBegin := INSTR(sSplitString,a_Delimiter);
??????? EXIT WHEN iBegin < 1;
??????? sValue := Substr(sSplitString,1,iBegin - 1);
??????? sSplitString := SubStr(sSplitString,iBegin + 1);
??????? -- 计数器加1
?????? iCount := iCount + 1;
?????? -- 数组加1维
?????? v_Result.extend();
?????? -- 将数据存入数组
??????? v_Result(iCount) := sValue;
??? END LOOP;
??? --Set last entry
??? sSplitString := SubStr(sSplitString, iBegin);

??? --Update array and counter if necessary
??? IF Length(sSplitString) > 0 THEN
???? -- 计数器加1
????? iCount := iCount + 1;
????? -- 数组加1维
????? v_Result.extend();
???? -- 将数据存入数组
???? v_Result(iCount) := sSplitString;
??? END IF;

??? RETURN(v_Result);
EXCEPTION
?? WHEN others THEN
?????? return v_Result ;
END fun_ParseToArray;


3、组成SQL
...
vs_row pkg_string.StringArray;
vs_column pkg_string.StringArray;
...
vs_row := fun_ParseToArray(你的一列数据,'#');
--再用for循环拆分vs_row的列:
vs_column(j) := fun_ParseToArray(vs_row(i),'|');
再把拆分出来的数据入表

?

?

?

今天闲着没事就写了一个小程序：
1>：对这种格式的分割：'123,zhonggou,mama,' 最后要有一个逗号。

set serveroutput on;
create or replace procedure sp_test_proce(str varchar2)
as
v_Str char(1);
j NUMBER :=1;
vsResult varchar2(256);
begin
for i IN 1..length(str) loop
v_Str := substr(str,i,1);
if v_Str = ',' then
vsResult := substr(str,j,i-j);
j :=i+1;
dbms_output.put_line(vsResult);
end if;
end loop;
end;

?

2:

create or replace procedure Test
is
tempValue varchar(2000);
AID varchar2(32);
begin
begin
????? tempValue:='ABCD:BDF:CD:DDF:EDF:F:G:H:I:J:K:';

????? while(instr(tempValue,':')>0) loop
?????? if INSTR(tempValue,':' ) > 0 then
???????????? AID := SUBSTR(tempValue ,1,INSTR(tempValue,':') - 1 );
???????????? DBMS_OUTPUT.put_line(AID);
???????????? tempValue := SUBSTR(tempValue,INSTR(tempValue,':') + 1 , LENGTH(tempValue) - INSTR(tempValue,':'));
????????? else
???????????? AID := tempValue;
???????????? tempValue := '';
????????? end if;
????? end loop;

end;
end;

?

2>：对这种格式的分割：'123,zhonggou,mama'

create or replace procedure Test
is
temp varchar(2000);
result varchar2(32);
begin
begin
????? temp:='123,zhonggou,mama';

????? while(length(temp)>0) loop
?????? if INSTR(temp,',' ) > 0 then
???????????? result:= SUBSTR(temp ,1,INSTR(temp,',') - 1 );
????????????
???????????? temp := SUBSTR(temp,INSTR(temp,',') + 1 , LENGTH(temp) - INSTR(temp,','));
????????? else
???????????? result:= temp;
???????????? temp:= '';
????????? end if;
DBMS_OUTPUT.put_line(result);
????? end loop;

end;
end;