Compare and Merge two Collections

How to Compare Two Collections and MERGE into third Collection?

We can merge two Tables and in the same way two Collections can also be merged to form a single Collection.

/*Create Object Type First*/

CREATE TYPE MyType AS OBJECT

(

ID  NUMBER(10),

Val VARCHAR2(30) 

);

/*Create Nested Table of Object Type MyType*/

CREATE TYPE NT AS TABLE OF MyType;

So, in this Example, we will be going to Create two NESTED Table Objects Obj1 and Obj2 with Data As shown below and will merge that to Single Object(Obj3). Based on ID Values we need to compare Obj1 and Obj2 and if there is any match we need to add sum of Val else Insert into Obj3. Using the TABLE () operator, you can manipulate your collections using SQL statements just like if they were DB tables.

/*Below PL/SQL Block that will add Values to Two Nested Table Object(Obj1 and Obj2) and will merge Data from both the Objects into Third Object(Obj3)*/

SET SERVEROUTPUT ON;

DECLARE

Obj1 NT:= NT(MyType(1,100), MyType(2,200), MyType(3,300));

Obj2 NT:= NT(MyType(2,200), MyType(3,200), MyType(4,400), MyType(5,500));

Obj3 NT;

BEGIN

/*Display Data From First Object*/

DBMS_OUTPUT.PUT_LINE('Data From First Object:');

FOR Rec IN (Select tx.ID, tx.Val From TABLE(Obj1) tx)

LOOP

DBMS_OUTPUT.PUT_LINE(Rec.ID||' - '||Rec.Val);

ENd LOOP;

/*Display Data From Second Object*/

DBMS_OUTPUT.PUT_LINE('Data From Second Object:');

FOR Rec IN (Select tx.ID, tx.Val From TABLE(Obj2) tx)

LOOP

DBMS_OUTPUT.PUT_LINE(Rec.ID||' - '||Rec.Val);

END LOOP;

 /*Insert Data into Third Object by comparing the ID values from Obj1 and Obj2*/

SELECT MyType(ID,NVL(T1.val,0)+NVL(T2.val,0)) BULK COLLECT INTO Obj3

FROM TABLE(Obj1) T1 FULL OUTER JOIN TABLE(Obj2) T2 USING(ID);

/*Display Data From Third Object Obj3*/

DBMS_OUTPUT.PUT_LINE('Data After Merging Data From Obj1 and Obj2:');

FOR Rec IN (Select tx.ID, tx.Val From TABLE(Obj3) tx)

LOOP

DBMS_OUTPUT.PUT_LINE(Rec.ID||' - '||Rec.Val);

END LOOP;

END;

NOTE: Table functions: PL/SQL functions that return collections and can be called in the FROM clause of a SELECT statement. 
  

OUTPUT:

Data From First Object:

1 - 100

2 - 200

3 - 300

Data From Second Object:

2 - 200

3 - 200

4 - 400

5 - 500

Data After Merging Data From Obj1 and Obj2:

1 - 100

2 - 400

3 - 500

4 - 400

5 - 500

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page