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;
Department without any employee using EXCEPT:
Dept No: 40 Dept name: OPERATIONS
Department with employee using INTERSECT:
Dept No: 10 Dept name: ACCOUNTING
Dept No: 20 Dept name: RESEARCH
Dept No: 30 Dept name: SALES
All distinct department using UNION:
Dept No: 10 Dept name: ACCOUNTING
Dept No: 20 Dept name: RESEARCH
Dept No: 30 Dept name: SALES
Dept No: 40 Dept name: OPERATIONS