PL/SQL program: Display employee ids, names, and manager names

PL/SQL While Loop: Exercise-6 with Solution

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

PL/SQL Code:

DECLARE
  CURSOR emp_cursor IS
    SELECT e.employee_id, e.first_name, m.first_name AS manager_name
    FROM employees e
    LEFT JOIN employees m ON e.manager_id = m.employee_id;
emp_recordemp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor INTO emp_record;
  WHILE emp_cursor%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_record.first_name);
    DBMS_OUTPUT.PUT_LINE('Manager Name: ' || emp_record.manager_name);
    DBMS_OUTPUT.PUT_LINE('-------------------------');
    FETCH emp_cursor INTO emp_record;
  END LOOP;
  CLOSE emp_cursor;
END;
/

Sample Output:

Employee ID: 202
Employee Name: Pat 
Manager Name: Michael 
-------------------------
Employee ID: 206
Employee Name: William 
Manager Name: Shelley 
-------------------------
Employee ID: 201
Employee Name: Michael 
Manager Name: Steven 
-------------------------
Employee ID: 101
Employee Name: Neena
Manager Name: Steven 
-------------------------
Employee ID: 102
Employee Name: Lex
Manager Name: Steven 
-------------------------
Employee ID: 114
Employee Name: Den 
Manager Name: Steven 
-------------------------

.....

Explanation:

The said code in Oracle's PL/SQL that retrieves and display the employee details, including their IDs, names, and the names of their respective managers.

The cursor emp_cursor is defined to select the employee ID, first_name, and manager name from the 'employees' table.

The LEFT JOIN links the employees table with itself, matching the manager_id of an employee with the employee_id of their manager and retrieves the manager's name for each employee.

A variable emp_record of type emp_cursor is declared to store each fetched row.

The loop fetches each row from the cursor into the emp_record variable and exits the loop when there are no more rows to fetch.

The DBMS_OUTPUT.PUT_LINE in loop displays the employee ID, first name, and manager name for each employee.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Employee information retrieval using PL/SQL cursor.
Next: Display job ids, titles, and minimum salaries of all jobs.