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
No comments:
Post a Comment