The following package demonstrates all the features required in a proposed web-based database solution.
The purpose of the package is to update the planned hours of an employee, to a project. The employee is constrained to the passed in parameter. An error is raised if the employee or project does not exist. The method of calling the procedure will also be illustrated at the end.
Code :
CREATE OR REPLACE PACKAGE update_planned_hrs
IS
Planned_hours NUMBER(4);
PROCEDURE set_new_planned (p_emp_id IN NUMBER, p_project_id IN NUMBER, p_hours IN NUMBER);
FUNCTION existing_planned (p_emp_id IN NUMBER, p_project_id IN NUMBER) RETURN NUMBER;
END update_planned_hrs;
/
CREATE OR REPLACE PACKAGE BODY update_planned_hrs
IS
PROCEDURE set_new_planned (p_emp_id IN NUMBER, p_project_id IN NUMBER, p_hours IN NUMBER)
IS
BEGIN
UPDATE employee_on_activity ea
SET ea.ea_planned_hours = p_hours
WHERE
ea.ea_emp_id = p_emp_id
AND ea.ea_proj_id = p_project_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20100, 'No such employee or project');
END set_new_planned;
FUNCTION existing_planned (p_emp_id IN NUMBER, p_project_id IN NUMBER) RETURN NUMBER
IS
existing_hours NUMBER(4);
BEGIN
SELECT ea.ea_planned_hours INTO existing_hours
FROM employee_on_activity ea
WHERE
ea.ea_emp_id = p_emp_id
AND ea.ea_proj_id = p_project_id;
RETURN (existing_hours);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20100, 'No such employee or project');
END existing_planned;
END update_planned_hrs;
/
2 Comments
