Package to retrieve employees with the same manager in PL/SQL

PL/SQL Package: Exercise-14 with Solution

Write a PL/SQL code that create a package that contains a function to retrieve the names of employees who have the same manager.

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:

CREATE OR REPLACE PACKAGE emp_pkg IS
  TYPE employee_names_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  FUNCTION get_same_manager_employees(emp_id IN NUMBER) RETURN employee_names_type;
END emp_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  FUNCTION get_same_manager_employees(emp_id IN NUMBER) RETURN employee_names_type IS
same_manager_employeesemployee_names_type;
manager_id NUMBER;
  BEGIN
    SELECT manager_id INTO manager_id
    FROM employees
    WHERE employee_id = emp_id;
    SELECT first_name || ' ' || last_name
    BULK COLLECT INTO same_manager_employees
    FROM employees
    WHERE manager_id = manager_id
    AND employee_id != emp_id;
    RETURN same_manager_employees;
  END get_same_manager_employees;
END emp_pkg;
/

Sample Output:

Package created.
Package Body created

Flowchart:

To execute the package:

DECLARE
same_manager_employeesemp_pkg.employee_names_type;
emp_id NUMBER := 100; 
BEGIN
same_manager_employees := emp_pkg.get_same_manager_employees(emp_id);
  FOR i IN 1..same_manager_employees.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(same_manager_employees(i));
  END LOOP;
END;
/

Sample Output:

Statement processed.
NeenaKochhar
Lex De Haan
Alexander Hunold
Bruce Ernst 
David Austin 
ValliPataballa
Diana Lorentz 
Nancy Greenberg 
Daniel Faviet
John Chen 
......

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that will return a collection of employee names who have the same manager as the specified employee, excluding the employee with the given emp_id.

A function get_same_manager_employees is declared in this package. The function takes a single input parameter emp_id, representing the employee ID for whom it find colleagues with the same manager.

The function returns a collection of employee names.

Inside the function, two local variables same_manager_employees of type employee_names_type and manager_id of type NUMBER are declared.

The SQL query retrieves the manager_id of the employee for the specific emp_id. Another SQL query utilizes the BULK COLLECT INTO clause to fetch the first name and last name of all employees who share the same manager as the given employee. The condition employee_id != emp_id ensures that the specified employee is excluded from the result.

The names of the employees with the same manager are collected into the same_manager_employees collection.

Finally, the collection same_manager_employees is returned as the result of the function.s

Previous: Calculate average age of employees in PL/SQL.
Next: Highest paid employees package in PL/SQL.