Employee information retrieval using PL/SQL cursor

PL/SQL While Loop: Exercise-5 with Solution

Write a PL/SQL program to display the employee IDs, names, and department names 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

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

DECLARE
v_employee_idemployees.employee_id%TYPE;
v_first_nameemployees.first_name%TYPE;
v_last_nameemployees.last_name%TYPE;
v_department_namedepartments.department_name%TYPE;
  CURSOR employee_cursor IS
    SELECT e.employee_id, e.first_name, e.last_name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id;
BEGIN
  OPEN employee_cursor;
  FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name, v_department_name;
  WHILE employee_cursor%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee First Name: ' || v_first_name);
    DBMS_OUTPUT.PUT_LINE('Employee Last Name: ' || v_last_name);
    DBMS_OUTPUT.PUT_LINE('Department Name: ' || v_department_name);
    DBMS_OUTPUT.PUT_LINE('-------------------');
    FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name, v_department_name;
  END LOOP;
  CLOSE employee_cursor;
END;

Sample Output:

Employee ID: 100
Employee First Name: Steven 
Employee Last Name: King 
Department Name: Executive
-------------------
Employee ID: 101
Employee First Name: Neena
Employee Last Name: Kochhar
Department Name: Executive
-------------------
Employee ID: 102
Employee First Name: Lex
Employee Last Name: De Haan
Department Name: Executive
-------------------
Employee ID: 103
Employee First Name: Alexander 
Employee Last Name: Hunold
Department Name: IT
-------------------
Employee ID: 104
Employee First Name: Bruce 
Employee Last Name: Ernst 
Department Name: IT
-------------------

.....

Explanation:

The said code in Oracle's PL/SQL that retrieves the employee information from the table 'employees' and 'departments' and display the employee IDs, first names, last names, and department names of all employees.

A cursor named employee_cursordefines that retrieves the employee ID, first name, last name, and department name from the employees and departments tables.

A FOR loop iterates over each record fetched by the cursor and the DBMS_OUTPUT.PUT_LINE procedure displays the employee ID, first name, last name, and department name.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: PL/SQL Program to Display Employee Information.
Next: Display employee ids, names, and manager names.