Simple Oracle Stored Procedure

Simple Oracle Stored Procedure

-- The salary is increased by 500, old. SAL> 2500 not added, new. SAL> 2500 is changed to 2500;

Select * from EMP -- before salary increase

Create or replace procedure sp_add (add in number) is -- add parameter receives incoming salary increase

-- Declare
-- Cursor emp_cur is select * from EMP;

Begin

For sal_sum in (select * from EMP) loop -- open the cursor emp_cur or

-- (Select * from EMP) (this cursor does not need to be declared in the for loop)
-- Automatically retrieves rows from the activity set and closes the cursor after processing all rows
If sal_sum.sal> 2500 then -- compare values by row
Update EMP set sal = sal_sum.sal where empno = sal_sum.empno;

Elsif (sal_sum.sal + Add) & gt; 2500 then
Update EMP set sal = 2500 where empno = sal_sum.empno;

Else
Update EMP set sal = sal_sum.sal + Add where empno = sal_sum.empno;
End if;

End loop;

End;

Begin

Sp_add (500 );
-- Input the increment range


End;

select * from EMP -- after salary increase