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.