Andrew Fraser DBA

Analytics row_number to identify first parent row in oracle pl/sql cursor loop

Let’s say you have 10m parent rows and 11m child rows and need to process them row-at-a-time in pl/sql. Two methods of doing that are below.

• The second method will fire the combined select just the once, picking up all 11m rows in one go.
• The first method will fire the child select 10m times individually, with a different id value passed into the where clause each time. It uses bind variables for that, so doesn’t hard parse 10m times, but still will take time.

The first method is the simplest to read, maintain and debug, so stick with that unless performance is required.

Method 1 – traditional way, two cursor loops

BEGIN
   FOR r_dept IN ( SELECT deptno , dname FROM dept )
   LOOP
      -- put parent (department) row processing code here.
      FOR r_emp IN ( SELECT empno , ename FROM emp WHERE emp.deptno = r_dept.deptno )
      LOOP
         -- put child (employee) row processing code here.
      END LOOP ;
   END LOOP ;
END ;

Method 2 – single cursor loop

BEGIN
   FOR r_both IN (
      SELECT dept.deptno
           , dept.dname
           , emp.empno
           , emp.ename
           , ROW_NUMBER() OVER ( PARTITION BY dept.deptno ORDER BY dept.deptno , emp.empno ) AS rn
        FROM dept
        LEFT OUTER JOIN emp  -- outer join if you want to process childless parents = departments with no employees assigned. 
          ON dept.deptno = emp.deptpno
   )
   LOOP
      IF r_both.rn = 1  -- starting to work on a different parent (department) record.
      THEN
         -- put parent (department) row processing code here.
      END IF ;
      -- put child (employee) row processing code here.
   END LOOP ;
END ;