Map Member Functions For Multiset Operations

MAP member function are special functions used to compare objects.The MAP member functions are used for performing comparisons between a single attribute of an object instance to a single attribute of another object instance.
A sample object type with a MAP member function

Step 1 : Create Object

CREATE OR REPLACE TYPE obj_emp AS OBJECT (
   ename   VARCHAR2 (20),
   dep     NUMBER,
   MAP MEMBER FUNCTION elist
      RETURN RAW
);
/

Step 2 : Create Type

CREATE OR REPLACE TYPE ntyp_emp IS TABLE OF obj_emp;
/


Step 3 : Create Type Body

CREATE OR REPLACE TYPE BODY obj_emp
AS
   MAP MEMBER FUNCTION elist
      RETURN RAW
   IS
   BEGIN
      RETURN UTL_RAW.cast_to_raw (SELF.ename || SELF.dep);
   END;
END;
/

For a complex type of object (row type), need to provide MAP function to work with all MULTISET operations.Multiset operators combine the results of two nested tables into a single nested table.

A sample code for using  Multiset operators.


DECLARE
   lt_emp_dtls        ntyp_emp;
   lt_emp_per_dtls    ntyp_emp;
   lt_emp_distinct    ntyp_emp;
   lt_emp_union       ntyp_emp;
   lt_emp_intersect   ntyp_emp;
BEGIN
   lt_emp_dtls := ntyp_emp (obj_emp (NULL, NULL));
   lt_emp_per_dtls := ntyp_emp (obj_emp (NULL, NULL));
   lt_emp_distinct := ntyp_emp (obj_emp (NULL, NULL));
   lt_emp_union := ntyp_emp (obj_emp (NULL, NULL));
   lt_emp_intersect := ntyp_emp (obj_emp (NULL, NULL));

   BEGIN
      SELECT obj_emp (ename, did)
      BULK COLLECT INTO lt_emp_dtls
        FROM emp_details;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error In emp details');
   END;

   BEGIN
      SELECT obj_emp (ename, did)
      BULK COLLECT INTO lt_emp_per_dtls
        FROM emp_per_dtls;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error In emp personal details');
   END;

 
    lt_emp_union  := lt_emp_dtls MULTISET UNION lt_emp_per_dtls;
 
   FOR i IN 1 .. lt_emp_union.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('Multiset Union ' || lt_emp_union (i).ename);
   END LOOP;

    lt_emp_intersect  := lt_emp_dtls MULTISET INTERSECT lt_emp_per_dtls;

   FOR i IN 1 .. lt_emp_intersect.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('Multiset Intersect ' || lt_emp_intersect (i).ename
                           );
   END LOOP;
 
    lt_emp_distinct  := lt_emp_dtls MULTISET EXCEPT DISTINCT lt_emp_per_dtls;
 
   FOR i IN 1 .. lt_emp_distinct.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('Multiset Distinct ' || lt_emp_distinct (i).ename);
   END LOOP;
 
END;

Output:

Multiset Union Ram
Multiset Union John
Multiset Union Jasmin
Multiset Union Raj
Multiset Union Rose
Multiset Union Jasmin
Multiset Union Rose
Multiset Union Raj
Multiset Intersect Jasmin
Multiset Intersect Raj
Multiset Intersect Rose
Multiset Distinct Ram
Multiset Distinct John
PL/SQL procedure successfully completed.