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.