PL/SQL cursor example: Displaying employee information

PL/SQL While Loop: Exercise-9 with Solution

Write a PL/SQL program to display the employee IDs, names, and job history start 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_start_datejob_history.start_date%TYPE;
  CURSOR c_employees IS
    SELECT e.employee_id, e.first_name, jh.start_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_start_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('Start Date: ' || v_start_date);
    DBMS_OUTPUT.PUT_LINE('----------------------');
    FETCH c_employees INTO v_employee_id, v_first_name, v_start_date;
  END LOOP;
  CLOSE c_employees;
END;

Sample Output:

Employee ID: 101
Employee Name: Neena
Start Date: 21-SEP-89
----------------------
Employee ID: 101
Employee Name: Neena
Start Date: 28-OCT-93
----------------------
Employee ID: 102
Employee Name: Lex
Start Date: 13-JAN-93
----------------------
Employee ID: 114
Employee Name: Den        
Start Date: 24-MAR-98
----------------------
Employee ID: 122
Employee Name: Payam
Start Date: 01-JAN-99
----------------------
Employee ID: 176
Employee Name: Jonathon   
Start Date: 24-MAR-98
----------------------
Employee ID: 176
Employee Name: Jonathon   
Start Date: 01-JAN-99
----------------------
Employee ID: 200
Employee Name: Jennifer   
Start Date: 17-SEP-87
----------------------
Employee ID: 200
Employee Name: Jennifer   
Start Date: 01-JUL-94
----------------------

.....

Explanation:

The said code in Oracle's PL/SQL that retrieves employee information, such as their ID, name, and start date from the 'employees' and 'job_history' tables.

Three variables, v_employee_id, v_first_name, and v_start_date, are of same datatype as the respective columns in the table.

The cursor c_employees is defined to select the employee_id, first_name, and start_date columns from the employees table, joined with the job_history table based on the employee_id column.

The cursor c_employees allows the FETCH statement that retrieves the first row from the result set and stores the values in the declared variables.

The WHILE loop iterates as long as there are rows in the result set and displays the employee ID, name, and start date using the DBMS_OUTPUT.PUT_LINE function.

The loop continues until all rows have been processed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Employee data retrieval using cursor in PL/SQL.
Next: Employee job history report.