Using Cursor at DB2

After Stored Procedure, we will learn about how to use Cursor. Output with Cursor or without Cursor will be the same. The differences is just a the process. With Cursor we lock at 1 row only, without cursor we will lock all record.

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.