The crucial clues to solve the task I found at Adrian Billington’s Blog. It’s an older post, but still valid: introduction to bulk PL / SQL Enhancements in 9i .
The code below should be self explaining with the notes.
To show how to the data, I have added an output via DBMS_OUTPUT.
— First you have to create the type for nested collection
CREATE OR REPLACE type emp_t
create or replace TYPE tbl_emp AS TABLE OF emp_t
CAST (MULTISET( SELECT empno,ename,sal, comm FROM emp e WHERE e.deptno= d.deptno
) AS tbl_emp
) as emps -- I need an alias to be able to refer the embedded Collection
FROM dept d;
TYPE tbl_dept IS TABLE OF c1%ROWTYPE;
depts tbl_dept ;
FETCH c1 BULK COLLECT INTO depts;
FOR i IN 1..depts.COUNT
FOR j IN 1.. depts(i).emps.COUNT
We had to rewrite a commonly used PL / SQL function. The code suffered heavily from the Context Switch. He literally was called millions of times. We set ourselves the goal of having a single Bulk Collect to read all data from the database . I give here a code skeleton that I have adapted to the scott / tiger schema.
SELECT deptno, dname
CURSOR c2 (p_deptno NUMBER)
SELECT empno,ename,sal, comm
WHERE deptno= p_deptno;
FOR c1rec IN C1
FOR c2rec IN c2(c1rec.deptno)
In reality, of course, there was complex logic instead of null in the loop. I worked jointly with a developer of the Software vendors on the problem. We did not have enough time to understand this code. We decided to only formally convert the code and to keep the loops. As mentioned, we wanted to read all the data in a single step in a nested array.
There should be an outer department collection that contains an inner employee collection as a nested table. There are other solutions (as Bryn Llewellyn thankfully pointed out one in the comment), but the nested table solution merges smoothly with the legacy code. As in our example also in reality the amount of data per query was so small that it all would easily fit into one array .
How does the select look like?