|
create or replace type obj_table1 as object(
id varchar2(3),
name varchar2(10)
);
create or replace type tbl_table1 as table of obj_table1; --如果要删除type类型对象，则需要先释放与object对象相关联的对象
create or replace procedure prc_table1(p_tbl_table1 out tbl_table1)
is
i number;
flag number;
vtemp varchar2(100);
objtable1 obj_table1;
begin
i := 0;
p_tbl_table1 := new tbl_table1();
for v in (select distinct id from table1) loop
i := i +1;
flag := 0;
for vv in (select name from table1 where id = v.id) loop
if flag = 0 then
vTemp := vv.name;
else
vTemp := vTemp || '+' || vv.name;
end if;
flag := 1;
end loop;
objtable1.id := v.id;
objtable1.name := vTemp;
p_tbl_table1.extend;
p_tbl_table1(i) := objtable1;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
declare
atbl_table1 tbl_table1;
begin
atbl_table1 := new tbl_table1();
prc_table1(atbl_table1);
for i in 1..atbl_table1.count loop
dbms_output.put_line(atbl_table1(1).id||','||atbl_table1(1).name);
end loop;
end;
| 
