CAST

Lösung: Die Collection in der Collection

Die entscheidenden Hinweise zur Lösung der Aufgabe habe ich bei Adrian Billington gefunden. Es ist schon ein älterer Eintrag, aber immer noch gültig: introduction to bulk pl/sql enhancements in 9i.
Der Code unten sollte mit den Anmerkungen selbst erklärend sein.
Um zu zeigen, wie die Zugriffe auf die Daten funktionieren, habe ich noch eine Ausgabe über DBMS_output eingefügt.

— Zuerst muss man den Typ für die nested collection erzeugen

CREATE OR REPLACE type emp_t
AS
  object
  (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    SAL  NUMBER(7,2),
    COMM NUMBER(7,2) 
  )
/
create or replace TYPE tbl_emp  AS TABLE OF emp_t
/

 

DECLARE
  CURSOR c1
  IS
    SELECT deptno,
      dname,
      CAST (MULTISET( SELECT empno,ename,sal, comm FROM emp e WHERE e.deptno= d.deptno
                     ) AS tbl_emp  
           ) as emps -- ich brauche einen alias um die eingebette Collection referenzieren zu können
  FROM dept d;

  TYPE tbl_dept IS TABLE OF c1%ROWTYPE;
  depts tbl_dept ;
BEGIN
   OPEN c1;
   FETCH c1 BULK COLLECT INTO depts;
   CLOSE c1;
   FOR i IN 1..depts.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE(depts(i).deptno);
      FOR j IN  1.. depts(i).emps.COUNT
      LOOP
        DBMS_OUTPUT.PUT_LINE('***'||depts(i).emps(j).ename);
      END LOOP;
   END LOOP;
END;
/