crud.sql

CREATE OR REPLACE PACKAGE employee_crud AS
 
	PROCEDURE crea_empleado(p_last_name IN employees.last_name%TYPE, p_email IN employees.email%TYPE, p_hire_date IN employees.hire_date%TYPE, p_job_id IN employees.job_id%TYPE, p_salary IN employees.salary%type);
 
	PROCEDURE borra_empleado(p_employee_id IN employees.employee_id%TYPE);
 
	PROCEDURE actualiza_empleado(p_employee_id IN employees.employee_id%TYPE, p_salary IN employees.salary%TYPE, p_email IN employees.email%TYPE, p_department_id IN employees.department_id%TYPE);
 
	PROCEDURE consulta_empleado(p_employee_id IN employees.employee_id%TYPE, p_last_name OUT employees.last_name%TYPE, p_first_name OUT employees.first_name%TYPE, p_email OUT employees.email%TYPE, p_hire_date OUT employees.hire_date%TYPE, p_job_id OUT employees.job_id%TYPE, p_salary OUT employees.salary%TYPE);
 
END employee_crud;
/
 
CREATE OR REPLACE PACKAGE BODY employee_crud AS
 
	-- Crea empleado
	PROCEDURE crea_empleado(p_last_name IN employees.last_name%TYPE, p_email IN employees.email%TYPE, p_hire_date IN employees.hire_date%TYPE, p_job_id IN employees.job_id%TYPE, p_salary IN employees.salary%type)
	IS
		-- invalid_salary EXCEPTION;
	BEGIN
		-- IF valida_sueldo(p_salary) THEN
		-- 	raise invalid_salary; 
		-- END IF;
		INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary)
		VALUES (employees_seq.nextval, p_last_name, p_email, p_hire_date, p_job_id, p_salary);
	EXCEPTION
		WHEN DUP_VAL_ON_INDEX THEN
			DBMS_OUTPUT.PUT_LINE('Estas intentando crear un valor duplicado en un campo con una restriccion de valor unico');
		-- WHEN invalid_salary THEN
		-- 	DBMS_OUTPUT.PUT_LINE('Salario invalido');
	END crea_empleado;
 
	-- Borra empleado
	PROCEDURE borra_empleado(p_employee_id IN employees.employee_id%TYPE)
	IS
		no_data_deleted EXCEPTION;
	BEGIN
		DELETE FROM employees
		WHERE employee_id = p_employee_id;
	IF SQL%NOTFOUND THEN
		RAISE no_data_deleted;
	END IF;
	EXCEPTION
		WHEN no_data_deleted THEN
			DBMS_OUTPUT.PUT_LINE('Estas intentando borrar un empleado que no existe');
	END borra_empleado;
 
	-- Actualiza empleado
	PROCEDURE actualiza_empleado(p_employee_id IN employees.employee_id%TYPE, p_salary IN employees.salary%TYPE, p_email IN employees.email%TYPE, p_department_id IN employees.department_id%TYPE)
	IS
	BEGIN
		UPDATE employees
		SET salary = p_salary, email = p_email, department_id = p_department_id
		WHERE employee_id = p_employee_id;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE('El empleado que desea actualizar no existe');
		WHEN DUP_VAL_ON_INDEX THEN
			DBMS_OUTPUT.PUT_LINE('El valor que deseas insertar tiene una restriccion de valor unico');
	END actualiza_empleado;
 
	-- Consulta empleado
	PROCEDURE consulta_empleado(p_employee_id IN employees.employee_id%TYPE, p_last_name OUT employees.last_name%TYPE, p_first_name OUT employees.first_name%TYPE, p_email OUT employees.email%TYPE, p_hire_date OUT employees.hire_date%TYPE, p_job_id OUT employees.job_id%TYPE, p_salary OUT employees.salary%TYPE)
	IS
	BEGIN
		SELECT last_name, first_name, email, hire_date, job_id, salary
		INTO p_last_name, p_first_name, p_email, p_hire_date, p_job_id, p_salary
		FROM employees
		WHERE employee_id = p_employee_id;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE('El empleado que desea consultar no existe');
	END consulta_empleado;

	FUNCTION valida_sueldo(p_salary IN employees.salary%TYPE, p_department_id IN employees.department_id%TYPE DEFAULT 80)
	RETURN BOOLEAN
	IS
		v_is_valid BOOLEAN := TRUE;
		v_stddev employees.salary%TYPE;
		v_avg employees.salary%TYPE;
	BEGIN
		SELECT 3*STDDEV(salary), AVG(salary)
		INTO v_stddev, v_avg
		FROM employees
		WHERE department_id = p_department_id
		GROUP BY department_id;

		IF p_salary > (v_avg + v_stddev) or p_salary < (v_avg - v_stddev) THEN
				v_is_valid := FALSE;
		END IF;

		return v_is_valid;
	END valida_sueldo;

END employee_crud;
/