bulk collect

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;
/

 

Advertisements

Die Collection in der Collection

wir mussten einen sehr häufig genutzte PL/SQL Function umschreiben. Der Code litt stark unter dem Context Switch. Er wurde millionenfach aufgerufen. Wir setzen uns das Ziel, mit einem einzigen Bulk Collect alle Daten aus der Datenbank aus zu lesen. Ich gebe hier ein codeskellet, dass ich auf das scott/tiger schema angepasst habe.

DECLARE
   CURSOR c1
   IS
   SELECT deptno, dname
     FROM dept;
   CURSOR c2 (p_deptno NUMBER)
       IS
       SELECT empno,ename,sal, comm
         FROM emp
        WHERE deptno= p_deptno;
BEGIN
   FOR c1rec IN C1
   LOOP
      FOR c2rec IN c2(c1rec.deptno)
      LOOP
         NULL;
      END LOOP;
   END LOOP;
END;
/

In der Realität steht natürlich komplexe Logic statt null in der Scheife. Ich arbeitete gemeinsam mit einem Entwickler des Softwareherstellers an dem Problem. Wir hatten nicht genug Zeit um den Code zu verstehen. Wie beschlossen, den Code nur ganz formal um zu wandeln und die Schleifen zu belassen. Wir wollten nul also alle Daten in einem einzigen Schritt in ein geschachteltes Array lesen.
Also, alle Departments enthalten alle Employees. Wie in unserem Beispiel war auch in der Realität die Resultatsmenge pro Abfrage so klein, dass Alles locker in ein Array passte.

Wie sieht der zugehörige Select aus?