Oracle PL/SQL Nested Table - MULTISET INTERSECT

Oracle PL/SQL Nested Table MULTISET INTERSECT operator

PL/SQL nested table MULTISET INTERSECT operator results in data this is common between two nested tables. In the example below, the data common between nt_u1 and nt_u2 is returned (1,6,7,2) by multiset intersect. By reversing the nested tables (nt_u2 multiset intersect nt_u1), the result is (1,2,6,7). The SET operator results in distinct dataset in a PL/SQL nested table. The multiset intersect of distinct values of nt_d1 and nt_d2 results in a empty collection (0 element nested table).

DECLARE

nt_u1 nt_numtype := nt_numtype(1,3,6,7,9,2);

nt_u2 nt_numtype := nt_numtype(1,2,4,6,8,7);

nt_d1 nt_numtype := nt_numtype(1,3,3,5,5,7);

nt_d2 nt_numtype := nt_numtype(2,4,4,6,6,8);

nt_uq1 nt_numtype := nt_numtype();

nt_uq2 nt_numtype := nt_numtype();

nt_ix nt_numtype := nt_numtype();

FUNCTION op_nt(i_nt IN nt_numtype) RETURN VARCHAR2 IS

s_Nested_tab VARCHAR2(200);

BEGIN

IF i_nt.COUNT > 0 THEN

FOR idx IN i_nt.FIRST .. i_nt.LAST LOOP

IF idx < i_nt.COUNT THEN

s_nested_tab := s_nested_tab||i_nt(idx)||', ';

ELSE

s_nested_tab := s_nested_tab||i_nt(idx);

END IF;

END LOOP;

ELSE

s_nested_tab := '';

END IF;

RETURN s_nested_tab;

END op_nt;

BEGIN

DBMS_OUTPUT.PUT_LINE('Nested Table nt_U1 ['||nt_u1.COUNT||'] -> ('||op_nt(nt_u1)||')');

DBMS_OUTPUT.PUT_LINE('Nested Table nt_U2 ['||nt_u2.COUNT||'] -> ('||op_nt(nt_u2)||')');

nt_ix := nt_u1 MULTISET INTERSECT nt_u2;

DBMS_OUTPUT.PUT_LINE('nt_U1 Intersect nt_U2 ['||nt_ix.COUNT||'] -> ('||op_nt(nt_ix)||')');

nt_ix := nt_u2 MULTISET INTERSECT nt_u1;

DBMS_OUTPUT.PUT_LINE('nt_U2 Intersect nt_U1 ['||nt_ix.COUNT||'] -> ('||op_nt(nt_ix)||')');

DBMS_OUTPUT.PUT_LINE('Nested Table nt_D1 ['||nt_d1.COUNT||'] -> ('||op_nt(nt_d1)||')');

nt_uq1 := SET(nt_d1);

DBMS_OUTPUT.PUT_LINE('Unique Table nt_d1 ['||nt_uq1.COUNT||'] -> ('||op_nt(nt_uq1)||')');

DBMS_OUTPUT.PUT_LINE('Nested Table nt_d2 ['||nt_d2.COUNT||'] -> ('||op_nt(nt_d2)||')');

nt_uq2 := SET(nt_d2);

DBMS_OUTPUT.PUT_LINE('Unique Table nt_d2 ['||nt_uq2.COUNT||'] -> ('||op_nt(nt_uq2)||')');

nt_ix := nt_numtype();

nt_ix := SET(nt_d1) MULTISET INTERSECT SET(nt_d2);

DBMS_OUTPUT.PUT_LINE('SET(nt_d1) Intersect SET(nt_d2) ['||nt_ix.COUNT||'] -> ('||op_nt(nt_ix)||')');

END;

Back

Oracle registered trademark of Oracle Corporation.

Last Revised On: November 10, 2013

73905