The advantage of using Cursor
- only lock 1 row
- there are an operation that must use Cursor
The disadvantage of using Cursor
- it become more complex
For example if we want to make a Stored Procedure to update salary depends on job. There are 2 ways that we can do, Stored Procedure with an without Cursor.
This is the Stored Procedure without Cursor, the easier way.
------------------------------------------------------------
create procedure proc_2(in bonus double,in work varchar(10))
dynamic result sets 1
p1: begin
update staff set salary = salary + bonus where job= work;
end p1
------------------------------------------------------------
This is the screen shoot
This is the Stored Procedure without Cursor, more complicated query.
------------------------------------------------------------
create procedure proc_1(in bonus double,in work varchar(10))
dynamic result sets 1
p1: begin
declare now_salary double;
declare c1 cursor with return for
select salary from staff where job=work;
open c1;
fetch from c1 into now_salary;
close c1;
set now_salary = now_salary + bonus;
update staff set salary = now_salary where job= work;
end p1
------------------------------------------------------------
This is the screen shoot
You can run this Stored Procedure at DB2cmd. Just write this command.
"db2 call proc_2(100.0,'Mgr')", but don't forget to connect to the DBMS first.
