Increase Performance With Bulk Collect In Oracle
ORDER BY employee_id, start_date;
TYPE jh_rec IS RECORD (
TYPE jh_table IS TABLE OF jh_rec
INDEX BY PLS_INTEGER;
BULK COLLECT INTO jh_table_array;
FOR counter IN jh_table_array.FIRST .. jh_table_array.LAST
IF counter = jh_table_array.LAST
nincr := 0;
nincr := 1;
old_job := jh_table_array (counter).job_id;
IF jh_table_array (counter).employee_id =
jh_table_array (counter + nincr).employee_id
new_job := jh_table_array (counter + nincr).job_id;
WHERE hr.employees.employee_id =
DBMS_OUTPUT.put_line( ‘Employee ‘
|| jh_table_array (counter).employee_id
|| ‘ had job ‘
|| ‘ for ‘
|| (jh_table_array (counter).end_date
– jh_table_array (counter).start_date)
|| ‘ days and moved to job ‘
Execute the following procedure:
set serveroutput on;
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.