How to create a stored procedure to process some data
Few steps as simple as this:
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
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;
EXCEPTIONWHEN OTHERS THEN
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.