Combine collections in oracle?

Question

I need to combine two collections. I know that two tables can be combined, but not sure about collections. What is the best way to combine collections? Say below, this is sample code

------------ CREATE OR REPLACE TYPE obj_test AS OBJECT( id number(9), val number (9) ) / CREATE OR REPLACE TYPE obj_test_list AS TABLE OF obj_test / 

I have two lists / collections

 list1 obj_test_list ; list2 obj_test_list ; list3 obj_test_list ; list1 id val 1 100 2 200 3 300 list2 id val 1 300 4 500 

I want to match list1 and list2 based on id and add val else insert. I want it on list3 as follows.

 list3 id val 1 400 2 200 3 300 4 500 

Can someone give some sample code for this?

Answer 1

Using the TABLE() operator, you can manipulate your collections with SQL statements, as if they were database tables. In your case, for example, to execute a FULL OUTER JOIN :

 SELECT obj_test(id,NVL(T1.val,0)+NVL(T2.val,0)) BULK COLLECT INTO list3 FROM TABLE(list1) T1 FULL OUTER JOIN TABLE(list2) T2 USING(id); 

Given your two sample lists, this will be stored in list3:

 1 400 2 200 3 300 4 500 

---

Full code to verify:

 DECLARE list1 obj_test_list := obj_test_list(obj_test(1,100),obj_test(2,200),obj_test(3,300)); list2 obj_test_list := obj_test_list(obj_test(1,300),obj_test(4,500)); list3 obj_test_list; indx PLS_INTEGER; BEGIN SELECT obj_test(id,NVL(T1.val,0)+NVL(T2.val,0)) BULK COLLECT INTO list3 FROM TABLE(list1) T1 FULL OUTER JOIN TABLE(list2) T2 USING(id); indx := list3.FIRST; WHILE(indx IS NOT NULL) LOOP DBMS_OUTPUT.PUT(list3(indx).id); DBMS_OUTPUT.PUT(' '); DBMS_OUTPUT.PUT_LINE(list3(indx).val); indx := list3.NEXT(indx); END LOOP; END; 

Answer 2

Collections can be combined with SQL. Create collections, convert collections to tables, join tables, and then convert them back to collection.

This can be tricky the first time you encounter this stream of logic in an external linear representation. Especially with advanced features such as object types, cross-connects, and cast / collect. The steps are numbered and numbered to help you keep track. The advantage of constructing a query in this way is that it is much easier to debug. Start in the middle, select and run the request block in your IDE and continue moving until you understand the entire request.

 --#4: Create new collection of results. select cast(collect(obj_test(id, val)) as obj_test_list) from ( --#3: Join lists and add results - returns results in normalized format. select coalesce(list_1_normalized.id, list_2_normalized.id) id, coalesce(list_1_normalized.val, 0) + coalesce(list_2_normalized.val, 0) val from ( --#2a: List 1 normalized. select id, val from ( --#1a: List 1 objects. select obj_test_list(obj_test(1,100),obj_test(2,200),obj_test(3,300))list from dual ) list_1_objects cross join table(list_1_objects.list) ) list_1_normalized full outer join ( --#2b: List 2 normalized. select id, val from ( --#1b: List 2 objects. select obj_test_list(obj_test(1,300),obj_test(4,500))list from dual ) list_2_objects cross join table(list_2_objects.list) ) list_2_normalized on list_1_normalized.id = list_2_normalized.id );