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.
What is the difficulty level of this exercise?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/plsql-exercises/package/plsql-package-exercise-17.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics