WELCOME, GUEST
 
 
Minimize
Toad World Puzzler - February 2008

PL/SQL Knowledge Quiz  

The employees table in the production Oracle Database 10g Release 2 instance of the MassiveGlobalCorp company contains 2.5 million rows.
 
Below are three different blocks of code, each of which fetch all rows from this table and then "do stuff" with each fetched record. Which will run much slower than the other two, and why?
 

A.

DECLARE
   CURSOR employees_cur IS
      SELECT * FROM employees;
BEGIN
   FOR employee_rec IN employees_cur
   LOOP
      do_stuff (employee_rec);
   END LOOP;
END;

B.

DECLARE
   CURSOR employees_cur IS
      SELECT * FROM employees;
   l_employee   employees%ROWTYPE;
BEGIN
   OPEN employees_cur;
   LOOP
      FETCH employees_cur INTO l_employee;
      EXIT WHEN employees_cur%NOTFOUND;
      do_stuff (l_employee);
   END LOOP;
   CLOSE employees_cur;
END;

C.

DECLARE
   CURSOR employees_cur IS
      SELECT * FROM employees;
   TYPE employees_aat IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER; 
   l_employees   employees_aat;
BEGIN
   OPEN employees_cur;
   LOOP
     FETCH employees_cur
      BULK COLLECT INTO l_employees LIMIT 100; 
      EXIT WHEN l_employees.COUNT () = 0; 
      FOR indx IN 1 .. l_employees.COUNT
     LOOP
         do_stuff (l_employees (indx));
      END LOOP;
   END LOOP;
   CLOSE employees_cur;
END;


Submit