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.
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 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?