Home » SQL & PL/SQL » SQL & PL/SQL » Combining Collections
Combining Collections [message #183602] Fri, 21 July 2006 09:01 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Collection of object:

create or replace type cust is object (cname varchar2, age number);

create or replace type cust_tab as table of cust;


I have 2 collections of object CUST:

arr1(1): ('Daisy', 23)
arr1(2): ('Tom', 26)
arr1(3): ('Bogart', 18)
arr1(4): ('May', 4)

arr2(1): ('Annie', 6)
arr2(2): ('Bing', 34)
arr2(3): ('Veronica', 62)
arr2(4): ('Holly', 21)
arr2(5): ('Garry', 1)


I would like to combine them into arr1 sorted by age resulting to:

arr1(1): ('Garry', 1)
arr1(2): ('May', 4)
arr1(3): ('Annie', 6)
arr1(4): ('Bogart', 18)
arr1(5): ('Holly', 21)
arr1(6): ('Daisy', 23)
arr1(7): ('Tom', 26)
arr1(8): ('Bing', 34)
arr1(9): ('Veronica', 62)


How do I do that? Thanks in advance...



[Updated on: Fri, 21 July 2006 09:02]

Report message to a moderator

Re: Combining Collections [message #183607 is a reply to message #183602] Fri, 21 July 2006 09:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Using your setup (after adding a length to the VARCHAR2 in the CUST defiition):

john_dba>set serveroutput on size 10000
john_dba>DECLARE
  2    c_cust_1   cust_tab := cust_tab();
  3    c_cust_2   cust_tab := cust_tab();
  4    c_cust_3   cust_tab := cust_tab();
  5  BEGIN
  6    for i in 1..4 loop
  7      c_cust_1.extend;
  8    end loop;
  9    c_cust_1(1) := cust('Daisy',23);
 10    c_cust_1(2) := cust('Tom',26);
 11    c_cust_1(3) := cust('Bogart',18);
 12    c_cust_1(4) := cust('May',4);
 13    
 14    for i in 1..5 loop
 15      c_cust_2.extend;
 16    end loop;
 17    c_cust_2(1) := cust('Annie',6);
 18    c_cust_2(2) := cust('Bing',34);
 19    c_cust_2(3) := cust('Veronica',62);
 20    c_cust_2(4) := cust('Holly',21);  
 21    c_cust_2(5) := cust('Garry',1);
 22    
 23    select cust(cname,age)
 24    bulk collect into c_cust_3
 25    from (select cname,age from table(cast(c_cust_1 as cust_tab))
 26         union all
 27         select cname,age from table(cast(c_cust_2 as cust_tab))
 28         order by age);
 29         
 30    for i in c_cust_3.first .. c_cust_3.last loop
 31      dbms_output.put_line(c_cust_3(i).cname||' '||c_cust_3(i).age);
 32    end loop;
 33  END;
 34  /
Garry 1
May 4
Annie 6
Bogart 18
Holly 21
Daisy 23
Tom 26
Bing 34
Veronica 62

PL/SQL procedure successfully completed.
Re: Combining Collections [message #183799 is a reply to message #183607] Sun, 23 July 2006 23:09 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
That was not as complicated as I thought it would be! Thanks a lot!
Re: Combining Collections [message #184074 is a reply to message #183607] Tue, 25 July 2006 03:49 Go to previous message
padderz
Messages: 9
Registered: July 2005
Junior Member
Minor comments but...

FOR i IN 1 .. 4 LOOP
   c_cust_1.EXTEND;
END LOOP;


...is more easily written as...

c_cust_l.EXTEND (4);


...and the explicit re-instantiation of object type...

SELECT cust (cname, age)
FROM   TABLE (...);


...is more easily written as...

SELECT VALUE (t)
FROM   TABLE (...) t;
Previous Topic: describe a nested table
Next Topic: bind variables to package procedure call stored in table
Goto Forum:
  


Current Time: Sat Dec 10 06:54:45 CST 2016

Total time taken to generate the page: 0.06070 seconds