Requirement:

 We wrote two objects - a procedure and function.

 Stored Procedure -  bonus_calculation - let us add little complexity to the requirement - if the department_id is 30 then add 3 percent to bonus.

         function  -  find_department

Let us combine these two into a package called bonus_utility

Package called bonus utility:

First let us create Package Specification also called package spec:

CREATE OR REPLACE PACKAGE HR.bonus_utility AS

PROCEDURE bonus_calculation

(parm_bonus NUMBER);

FUNCTION find_department(parm_employee_id IN NUMBER)

RETURN NUMBER;

END bonus_utility;

Compile the above code in PL/SQL window and package specification will be created in the database.

Now let us create package body bonus_utility:

CREATE OR REPLACE PACKAGE BODY HR.bonus_utility AS

PROCEDURE bonus_calculation

(parm_bonus NUMBER)

IS

local_variable_bonus NUMBER;

FOR cur_rec IN ( SELECT

e.last_name||' '||e.first_name employee_name,

e.job_id employee_job,

e.salary employee_salary,

e.department_id employee_department

FROM

employees e

ORDER BY department_id )

LOOP

IF cur_rec.employee_department = 30 THEN

local_variable_bonus := ((cur_rec.employee_salary * (parm_bonus + 3)) /100);

ELSE

local_variable_bonus := ((cur_rec.employee_salary * parm_bonus) /100);

END IF;

dbms_output.put_line (cur_rec.employee_name||' employed in '||cur_rec.employee_department||', employed as '|| cur_rec.employee_job ||' has bonus of '||local_variable_bonus);

local_variable_bonus := 0;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line (SQLERRM);

END;

FUNCTION find_department(parm_employee_id IN NUMBER)

RETURN NUMBER

IS

local_dept_id NUMBER;

SELECT department_id

INTO local_dept_id

FROM employees

WHERE employee_id = parm_employee_id;

dbms_output.put_line ('Department id for '||parm_employee_id||' is '|| local_dept_id);

RETURN(local_dept_id);

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line (SQLERRM);

local_dept_id := 0;

RETURN(local_dept_id);

END;

END bonus_utility;

Compile the above code in PL/SQL window and package body will be created in the database.

Execute this package.procedure (bonus_utility.bonus_calculation with the bonus parameter and the results will be displayed.

if the department id is 3, then additional 3% will be added in the results.