Requirement:

 Write a stored procedure for calculating bonus of all employees in the HR schema employees table.

 bonus percentage will be passed as a parameter.

Procedure called bonus Calculation:

CREATE OR REPLACE 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 )

LOOP

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

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;

Compile the above code in PL/SQL window and stored procedure will be created in the database.

Execute this procedure (With DBMS_ouput turned on) by passing a bonus percentage (example 10),the results will be displayed.

Note;  parm_bonus is the parameter passed IN.

       local_variable_bonus - is a local variable used to store values.

       FOR LOOP CURSOR - loops through cursor SELECT statement

       dbms_output_put_line - prints the output

       EXCEPTION - part that handles error.