The Collection in The Collection

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?

 

Advertisements

2 comments

  1. I had to tidy up the example to impose proper style (like adding “order by”) and to make it do something:

    declare
    cursor c1 is
    select d.Deptno, d.Dname from Dept d order by d.Deptno;
    cursor c2 (Deptno Dept.Deptno%type) is
    select e.Empno, e.Ename from Emp e where e.Deptno = c2.Deptno order by e.Empno;
    begin
    for r1 in c1 loop
    DBMS_Output.Put_Line(Chr(10)||r1.Deptno||’ ‘||r1.Dname);
    for r2 in c2(r1.Deptno) loop
    DBMS_Output.Put_Line(‘ ‘||r2.Empno||’ ‘||r2.Ename);
    end loop;
    end loop;
    end;

    It’s performing a left outer join using nested loops programmed in PL/SQL. Here is the output:

    10 ACCOUNTING
    7782 CLARK
    7839 KING
    7934 MILLER

    20 RESEARCH
    7369 SMITH
    7566 JONES
    7788 SCOTT
    7876 ADAMS
    7902 FORD

    30 SALES
    7499 ALLEN
    7521 WARD
    7654 MARTIN
    7698 BLAKE
    7844 TURNER
    7900 JAMES

    40 OPERATIONS

    Here’s the SQL:

    select Deptno, d.Dname, e.Empno, e.Ename
    from Dept d left outer join Emp e using (Deptno)
    order by Deptno, e.Empno

    Programming a join in PL/SQL is one of the famous crimes of procedural guys who are new to SQL.

    We can simply bulk collect this — using the “limit” clause if called for.

    I have to assume that the “complex logic” does something for each row in the driving master Dept loop and then, within that, something for each child Emp row within each master. This is like the SQL*Plus “break” report of old. So is the question actually “How to program ‘break’ logic?”

    Here you are:

    declare
    type Row_t is record(
    Deptno Dept.Deptno %type not null := -1,
    Dname Dept.Dname %type,
    Empno Emp. Empno %type,
    Ename Emp. Ename %type);
    type Rows_t is table of Row_t index by pls_integer;
    Rows Rows_t;
    Prev_Deptno Dept.Deptno%type not null := -1;
    begin
    select Deptno, d.Dname, e.Empno, e.Ename
    bulk collect into Rows
    from Dept d left outer join Emp e using (Deptno)
    order by Deptno, e.Empno;

    for j in 1..Rows.Count loop
    if Rows(j).Deptno Prev_Deptno then
    DBMS_Output.Put_Line(Chr(10)||Rows(j).Deptno||’ ‘||Rows(j).Dname);
    Prev_Deptno := Rows(j).Deptno;
    end if;
    if Rows(j).Empno is null then
    DBMS_Output.Put_Line(‘ No employees’);
    else
    DBMS_Output.Put_Line(‘ ‘||Rows(j).Empno||’ ‘||Rows(j).Ename);
    end if;
    end loop;
    end;

    Here is the output:

    10 ACCOUNTING
    7782 CLARK
    7839 KING
    7934 MILLER

    20 RESEARCH
    7369 SMITH
    7566 JONES
    7788 SCOTT
    7876 ADAMS
    7902 FORD

    30 SALES
    7499 ALLEN
    7521 WARD
    7654 MARTIN
    7698 BLAKE
    7844 TURNER
    7900 JAMES

    40 OPERATIONS
    No employees

    Now tell me what in your question I’m failing to grasp.

    Like

    1. Hi Bryn,
      thanks for replying. You are raising an important point here by suggesting to do the join in SQL rather than in PL/SQL. However as I wrote I wanted two loops rather than one. In your solution you are replacing the other loop with an IF checking for the change of the department number. I was aiming for the employees nested as a collection in the departments. Well, and You don’t need to bother to limit the bulk collect. (You can, if you like).

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s