Employee job history report

PL/SQL While Loop: Exercise-10 with Solution

Write a PL/SQL program to display the employee IDs, names, and job history end dates of all employees.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

employee_id			integer
start_date			date
end_date			date
job_id				varchar(25)
department_id			integer

PL/SQL Code:

DECLARE
v_employee_idemployees.employee_id%TYPE;
v_first_nameemployees.first_name%TYPE;
v_end_datejob_history.end_date%TYPE;
  CURSOR c_employees IS
    SELECT e.employee_id, e.first_name, jh.end_date
    FROM employees e
    JOIN job_history jh ON e.employee_id = jh.employee_id;
BEGIN
  OPEN c_employees;
  FETCH c_employees INTO v_employee_id, v_first_name, v_end_date;
  WHILE c_employees%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_first_name);
    DBMS_OUTPUT.PUT_LINE('End Date: ' || v_end_date);
    DBMS_OUTPUT.PUT_LINE('----------------------');
    FETCH c_employees INTO v_employee_id, v_first_name, v_end_date;
  END LOOP;
  CLOSE c_employees;
END;

Sample Output:

Employee ID: 101
Employee Name: Neena
End Date: 27-OCT-93
----------------------
Employee ID: 101
Employee Name: Neena
End Date: 15-MAR-97
----------------------
Employee ID: 102
Employee Name: Lex
End Date: 24-JUL-98
----------------------
Employee ID: 114
Employee Name: Den        
End Date: 31-DEC-99
----------------------
Employee ID: 122
Employee Name: Payam
End Date: 31-DEC-99
----------------------
Employee ID: 176
Employee Name: Jonathon   
End Date: 31-DEC-98
----------------------
Employee ID: 176
Employee Name: Jonathon   
End Date: 31-DEC-99
----------------------

.....

Explanation:

The said code in Oracle's PL/SQL that generates a report showing the job history of employees, including their ID, name, and end date of employment by retrieving data from two tables 'employees' and 'job_history'.

The variables v_employee_id, v_first_name, and v_end_date are of same datatype as the respective columns of the tables are declared .

A cursor c_employees is defined to select the employee ID, first name, and end date from the 'employees' and 'job_history' tables, joined by the employee ID.

The FETCH statement fetches the first set of data is from the cursor into the declared variables.

A WHILE loop is initiated to iterate through the fetched result set. The WHILE loop iterates through the fetched result set and executes as long as the cursor has found a row.

Within the loop, the employee ID, name, and end date are displays using the DBMS_OUTPUT.PUT_LINE function.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Displaying employee information.
Next: Display department information.