Increase Performance With Bulk Collect In Oracle

Use Bulk Collect to retrieve multiple rows of data in a single fetch operation. Multiple rows of data in a single fetch reduce the number of network round-trips and improve performance.

The following example describes job listed in the JOB_HISTORY table was the job the employee had but also the job the employee went to after the job listed in the JOB_HISTORY table.

CREATE OR REPLACE PROCEDURE Promotion_Rev

IS
   old_job          hr.job_history.job_id%TYPE;
   new_job          hr.job_history.job_id%TYPE;
   nincr            NUMBER;

   CURSOR cselectjob
   IS
        SELECT employee_id,
               start_date,
               end_date,
               job_id
          FROM hr.job_history
      ORDER BY employee_id, start_date;

   TYPE jh_rec IS RECORD (
      employee_id   hr.job_history.employee_id%TYPE,
      start_date    hr.job_history.start_date%TYPE,
      end_date      hr.job_history.end_date%TYPE,
      job_id        hr.job_history.job_id%TYPE
   );

   TYPE jh_table IS TABLE OF jh_rec
                       INDEX BY PLS_INTEGER;

   jh_table_array   jh_table;
BEGIN
   OPEN cselectjob;

   FETCH cselectjob
   BULK COLLECT INTO jh_table_array;

   CLOSE cselectjob;

   FOR counter IN jh_table_array.FIRST .. jh_table_array.LAST
   LOOP
      IF counter = jh_table_array.LAST
      THEN
         nincr := 0;
      ELSE
         nincr := 1;
      END IF;

      old_job := jh_table_array (counter).job_id;

      IF jh_table_array (counter).employee_id =
            jh_table_array (counter + nincr).employee_id
      THEN
         new_job := jh_table_array (counter + nincr).job_id;
      ELSE
         SELECT job_id
           INTO new_job
           FROM hr.employees
          WHERE hr.employees.employee_id =
                   jh_table_array (counter).employee_id;
      END IF;

      DBMS_OUTPUT.put_line(   'Employee '
                           || jh_table_array (counter).employee_id
                           || ' had job '
                           || old_job
                           || ' for '
                           || (jh_table_array (counter).end_date
                               - jh_table_array (counter).start_date)
                           || ' days and moved to job '
                           || new_job
                           || '.');
   END LOOP;
END;
/

Execute the following procedure:

set serveroutput on;
BEGIN
   promotion_rev;
END;
/
The output should come something like this:

Employee 101 had job AC_ACCOUNT for 1497 days and moved to job AC_MGR.
Employee 101 had job AC_MGR for 1234 days and moved to job AD_VP.
Employee 102 had job IT_PROG for 2018 days and moved to job AD_VP.
Employee 114 had job ST_CLERK for 647 days and moved to job PU_MAN.
Employee 122 had job ST_CLERK for 364 days and moved to job ST_MAN.
Employee 176 had job SA_REP for 282 days and moved to job SA_MAN.
Employee 176 had job SA_MAN for 364 days and moved to job SA_REP.
Employee 200 had job AD_ASST for 2100 days and moved to job AC_ACCOUNT.
Employee 200 had job AC_ACCOUNT for 1644 days and moved to job AD_ASST.
Employee 201 had job MK_REP for 1401 days and moved to job MK_REP.
PL/SQL procedure successfully completed.