CODEWITHSUNDEEP
plsqloracle11g
cableload
cableload

Reputation: 4375

plsql record type and collection of record type

I have a plsql record type mrec and mreclist is collection of that record. I would like to know if it is possible to add each record into mreclist through one statement. Or is there another efficient way to do the same.

declare
  type mrec is record ( a varchar2(10),b varchar2(20));
  type mreclist is table of mrec;
  r mrec;
  rlist mreclist;
begin

rlist:=mreclist();

--insert value 

 select 'dummy1','dummy2' into r.a,r.b from dual;

--how to copy the above value into the mreclist with one single statement instead of the following statements. 
 rlist.Extend(1);
 rlist(1).a:=r.a;
 rlist(1).b:=r.b;

select 'dummy3','dummy4' into r.a,r.b from dual;
 rlist.Extend(1);
 rlist(2).a:=r.a;
 rlist(2).b:=r.b;
end;

Upvotes: 2

Views: 1679

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

Maybe are you looking for a combination of BULK COLLECT and MULTISET operators?

Something like that:

declare
  type mrec is record ( a varchar2(10),b varchar2(20));
  type mreclist is table of mrec;
  r mrec;
  rlist mreclist;
  tlist mreclist; -- <-- we need two collections here
begin

 select 'dummy1','dummy2' bulk collect into rlist from dual;
 --     collect initial data into the collection `rlist`      

 select 'dummy3','dummy4' bulk collect into tlist from dual;
 --     collect next data into the other collection `tlist`      

 rlist := rlist multiset union all tlist;
 --     merge `rlist` and `tlist`. Replace the collection `rlist` by the result.

I wouldn't say much about the efficiency of such code. It is probably very dependent of your concrete use case. Please note however that at some point, you hold both rlist and the result of the union in memory. For large collections this could be prohibitive.

For very basic use cases, you probably only need BULK COLLECT and a simple UNION ALL:

 select * bulk collect into rlist from (
     select 'dummy1','dummy2' from dual
     union all select 'dummy3','dummy4'from dual
   );

Finally, I would suggest you to take a look at "Taking Up Collections" by Steven Feuerstein for considerations about the result order of multiset operations. Just to quote few words:

Oracle documentation states that the difference between nested tables and varying arrays is that the data stored in a nested table column does not preserve its order, while that order is preserved in a varying array. Prior to Oracle Database 10g, this distinction did not mean much in the PL/SQL world. Now, with the set operators, the special characteristics of a multiset, or nested table, show themselves quite clearly.

Upvotes: 1

Related Questions