PL/SQL Employee Management Package Example

PL/SQL Package: gestionEmpleados Specification

CREATE OR REPLACE PACKAGE gestionEmpleados
AS
  -- Cursor to get average salary per department
  CURSOR curMediaSueldo IS
    SELECT AVG(salario) "media", dept_no 
    FROM emple 
    GROUP BY dept_no;

  -- Cursor to select all employees
  CURSOR curEmpleados IS 
    SELECT * 
    FROM emple;

  -- Cursor to select all departments
  CURSOR curDepartamentos IS 
    SELECT * 
    FROM depart;

  -- Package variables
  nombre VARCHAR2(40);
  media NUMBER;
  departamento VARCHAR2(20);

  -- Function declaration to get department name
  FUNCTION nomDepart(numeroDpt depart.dept_no%TYPE) 
    RETURN gestionEmpleados.departamento%TYPE;

END gestionEmpleados;

PL/SQL Package Body: gestionEmpleados

CREATE OR REPLACE PACKAGE BODY gestionEmpleados
AS
  -- Function definition to get department name
  FUNCTION nomDepart(numeroDpt depart.dept_no%TYPE)
    RETURN gestionEmpleados.departamento%TYPE
  IS
    v_departamento gestionEmpleados.departamento%TYPE := NULL; -- Local variable for return
  BEGIN
    FOR i IN curDepartamentos LOOP
      IF numeroDpt = i.dept_no THEN -- Corrected condition: find matching department number
        -- DBMS_OUTPUT.PUT_LINE(i.dnombre); -- Optional output
        v_departamento := i.dnombre;
        EXIT; -- Exit loop once found
      END IF;
    END LOOP;
    gestionEmpleados.departamento := v_departamento; -- Assign to package variable if needed elsewhere
    RETURN v_departamento;
  END nomDepart;

END gestionEmpleados;

Procedure: empleados

Compares employee salaries against a given employee's salary.

CREATE OR REPLACE PROCEDURE empleados
AS
  nom VARCHAR2(40);
  sal emple.salario%TYPE;
BEGIN
  -- Prompt for employee last name (using substitution variable)
  nom := '&nom'; 
  
  -- Store the input name in the package variable
  gestionEmpleados.nombre := nom; 

  -- Get the salary of the specified employee
  SELECT salario 
  INTO sal 
  FROM emple 
  WHERE apellido = nom;

  -- Loop through all employees and print those with higher salary
  DBMS_OUTPUT.PUT_LINE('Employees earning more than ' || nom || ' (' || sal || '):');
  FOR i IN gestionEmpleados.curEmpleados LOOP
    IF sal < i.salario THEN
      DBMS_OUTPUT.PUT_LINE(i.apellido || ' ' || i.salario);
    END IF;
  END LOOP;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found: ' || nom);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred in procedure empleados: ' || SQLERRM);
END empleados;

Procedure: empleadoDatos1

Displays details for the employee whose name is stored in the package variable.

CREATE OR REPLACE PROCEDURE empleadoDatos1
AS
  v_found BOOLEAN := FALSE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Details for employee: ' || gestionEmpleados.nombre);
  FOR i IN gestionEmpleados.curEmpleados LOOP
    IF gestionEmpleados.nombre = i.apellido THEN
      DBMS_OUTPUT.PUT_LINE(i.emp_no || ' ' || i.apellido || ' ' || i.oficio || ' ' || i.dir || ' ' || i.fecha_alt || ' ' || i.salario || ' ' || i.comision || ' ' || i.dept_no);
      v_found := TRUE;
    END IF;
  END LOOP;
  IF NOT v_found THEN
     DBMS_OUTPUT.PUT_LINE('No data found for employee: ' || gestionEmpleados.nombre || '. Ensure procedure empleados was run first.');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred in procedure empleadoDatos1: ' || SQLERRM);
END empleadoDatos1;

Procedure: modifSalario

Increases salary by 20% for the department with the lowest average salary.

CREATE OR REPLACE PROCEDURE modifSalario
AS
  v_departamento emple.dept_no%TYPE; -- Use a local variable
BEGIN
  -- Find the department number with the minimum average salary
  SELECT dept_no 
  INTO v_departamento 
  FROM emple 
  GROUP BY dept_no 
  HAVING AVG(salario) = (SELECT MIN(AVG(salario)) 
                         FROM emple 
                         GROUP BY dept_no);

  -- Update salaries for that department
  UPDATE emple 
  SET salario = salario * 1.20 
  WHERE dept_no = v_departamento;
  
  DBMS_OUTPUT.PUT_LINE('Salaries updated for department: ' || v_departamento || '. Rows updated: ' || SQL%ROWCOUNT);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Could not find department with minimum average salary.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Multiple departments found with the same minimum average salary. No update performed.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred in procedure modifSalario: ' || SQLERRM);
END modifSalario;

Procedure: verEmpleados

Displays details for a specified department.

CREATE OR REPLACE PROCEDURE verEmpleados (numeroDpt depart.dept_no%TYPE)
IS
  v_dnombre depart.dnombre%TYPE;
  v_loc depart.loc%TYPE;
  v_dept_found BOOLEAN := FALSE;
BEGIN
  -- Get department details using the package function
  gestionEmpleados.nombre := gestionEmpleados.nomDepart(numeroDpt); -- Sets package variable, potentially redundant

  -- Find and display department details directly
  FOR i IN gestionEmpleados.curDepartamentos LOOP
    IF i.dept_no = numeroDpt THEN
      DBMS_OUTPUT.PUT_LINE('Department Details:');
      DBMS_OUTPUT.PUT_LINE(i.dept_no || ' ' || i.dnombre || ' ' || i.loc);
      v_dept_found := TRUE;
      EXIT;
    END IF;
  END LOOP;

  IF NOT v_dept_found THEN
    DBMS_OUTPUT.PUT_LINE('Department not found: ' || numeroDpt);
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred in procedure verEmpleados: ' || SQLERRM);
END verEmpleados;