clob大数据转换为多行数据

--创建类型

CREATE OR REPLACE TYPE picid_rowtype as object(in_picid nvarchar2(50),in_order number)

CREATE OR REPLACE TYPE picid_tabletype as table of picid_rowtype

--以分号为分隔的clob数据。

create or replace function common_GetResultPicIds(v_picid clob,
idcount in number)
return picid_tabletype is
outid picid_tabletype := picid_tabletype();
tempid clob;
begin
tempid := v_picid;
for i in 1 .. idcount loop
outid.extend();--扩展
if i < idcount then
outid(1):=picid_rowtype(substr(tempid,1,instr(tempid,';')-1),i);
tempid:=substr(tempid,instr(tempid,';')+1);
else
outid(i):=picid_rowtype(tempid,i);
end if;
end loop;
return outid;
end;