Retrieve employees with the same job title and manager package in PL/SQL

PL/SQL Package: Exercise-17 with Solution

Write a code in PL/SQL which create a package that contains a function to retrieve the names of employees who have the same job title and 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_job_and_manager_employees(emp_id IN NUMBER) RETURN employee_names_type;
END emp_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  FUNCTION get_same_job_and_manager_employees(emp_id IN NUMBER) RETURN employee_names_type IS
same_job_and_manager_employeesemployee_names_type;
job_titleemployees.job_id%TYPE;
manager_idemployees.manager_id%TYPE;
  BEGIN
    SELECT job_id, manager_id
    INTO job_title, manager_id
    FROM employees
    WHERE employee_id = emp_id;
    SELECT first_name || ' ' || last_name
    BULK COLLECT INTO same_job_and_manager_employees
    FROM employees
    WHERE job_id = job_title
    AND manager_id = manager_id
    AND employee_id != emp_id;

    RETURN same_job_and_manager_employees;
  END get_same_job_and_manager_employees;
END emp_pkg;
/

Sample Output:

Package created.
Package Body created.

Flowchart:

To execute the package:

DECLARE
same_job_and_manager_employeesemp_pkg.employee_names_type;
emp_id NUMBER := 110; -- Specify the employee ID
BEGIN
same_job_and_manager_employees := emp_pkg.get_same_job_and_manager_employees(emp_id);
  FOR i IN 1..same_job_and_manager_employees.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(same_job_and_manager_employees(i));
  END LOOP;
END;
/

Sample Output:

Statement processed.
Daniel      Faviet
Ismael      Sciarra
Jose ManueUrman
Luis        Popp

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that retrieve the names of employees who have the same job title and manager as a particular employee.

A function get_same_job_and_manager_employees is declared in this package. It takes a single input parameter emp_id, representing the employee ID for whom it finds colleagues with the same job title and manager and returns a collection of employee names.

The three local variables same_job_and_manager_employees of type employee_names_type, job_title of type employees.job_id%TYPE, and manager_id of type employees.manager_id%TYPE are declared inside the function.

The SQL query that retrieves the job_id and manager_id of the employee against the specific emp_id and another SQL query utilizes the BULK COLLECT INTO clause to fetch the first name and last name of all employees who have the same job title and the same manager as the specific employee.

The names of the employees with the same job title and manager are collected into the same_job_and_manager_employeescollection containing the colleagues with the same job title and manager as the specified employee is returned as the result of the function.

Previous: Average tenure of employees by job category package in PL/SQL.