DROP TYPE DEPT_NESTED_TYPE

CREATE OR REPLACE TYPE DEPT_ROW AS OBJECT
(
 DEPNO NUMBER(22),
 DEPNAME VARCHAR(60),
 MAP MEMBER FUNCTION mapfun
    RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY DEPT_ROW AS  
MAP MEMBER FUNCTION mapfun 
  RETURN NUMBER 
IS  
    BEGIN 
      RETURN DEPNO; 
    END; 
END;

CREATE TYPE DEPT_NESTED_TYPE IS TABLE OF DEPT_ROW;

DECLARE
   COLL_DEPT_JOIN_EMP DEPT_NESTED_TYPE;
   COLL_DEPT_MASTER   DEPT_NESTED_TYPE;
   COLL_RESULT        DEPT_NESTED_TYPE;
BEGIN
    SELECT DEPT_ROW(EMP.DEPTNO, DEPT.DNAME) BULK COLLECT INTO COLL_DEPT_JOIN_EMP
    FROM EMP, DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    SELECT DEPT_ROW(DEPT.DEPTNO, DEPT.DNAME) BULK COLLECT INTO COLL_DEPT_MASTER
    FROM DEPT;
    
    COLL_RESULT := COLL_DEPT_MASTER MULTISET EXCEPT COLL_DEPT_JOIN_EMP;
    DBMS_OUTPUT.PUT_LINE ('Department without any employee using EXCEPT:');
    
    FOR idx in COLL_RESULT.FIRST .. COLL_RESULT.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE('          Dept No: ' || COLL_RESULT(idx).DEPNO || ' Dept name: ' || COLL_RESULT(idx).DEPNAME);
    END LOOP;
    
    COLL_RESULT := COLL_DEPT_MASTER MULTISET INTERSECT COLL_DEPT_JOIN_EMP;
    DBMS_OUTPUT.PUT_LINE ('Department with employee using INTERSECT:');
    
    FOR idx in COLL_RESULT.FIRST .. COLL_RESULT.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE('          Dept No: ' || COLL_RESULT(idx).DEPNO || ' Dept name: ' || COLL_RESULT(idx).DEPNAME);
    END LOOP;
    
    COLL_RESULT := COLL_DEPT_MASTER MULTISET UNION DISTINCT COLL_DEPT_JOIN_EMP;
    DBMS_OUTPUT.PUT_LINE ('All distinct department using UNION:');
    
    FOR idx in COLL_RESULT.FIRST .. COLL_RESULT.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE('          Dept No: ' || COLL_RESULT(idx).DEPNO || ' Dept name: ' || COLL_RESULT(idx).DEPNAME);
    END LOOP;    
END;