Definition of a read/write cursor in DB2

Cursor in DB2It is a very important concept. A cursor provides a flexible means to operate the data retrieved from a table. In essence, A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records.

Use location to change rows
Updatable cursors support using cursors to update row data modification statements. When you locate a row in an updatable cursor, you can perform update or delete operations for creating the base table row of the current row in the cursor. These are called location updates. Locate the update and execute it on the same connection that opens the cursor. This allows data modification to share the same transaction space as the cursor, and the lock held by the cursor will not prevent updates.

There are two methods to perform location update in the cursor:
The where current of clause in the UPDATE or DELETE statement.
The database API locates the UPDATE function or method, such as the ODBC SQLSetPos function.

Execute location update using Transact-SQL
The where current of clause OF Transact-SQL is typically used in the stored procedure, trigger, and script OF Transact-SQL.
When you need to modify a specific row in the cursor ). Stored Procedures, triggers, or scripts will:
DECLARE and OPEN cursors.
Use the FETCH statement to locate a row in the cursor.
Use the where current of clause to execute the UPDATE or DELETE statement. Use cursor_name in the declare statement
As the cursor_name in the where current of clause.
Routine:
Declare cursor_name cursor
Select * from T2
For update
Open cursor_name
Fetch next from cursor_name
While @ Fetch_Status = 0
Begin
Update T2 set sname = 'lx _ '+ sname -- right (sname, len (sname)-3)
Where current of cursor_name
Fetch next from cursor_name
End
Close cursor_name
Deallocate Cursor_Name

Similar usage is also available in DB2. Because the SQL syntax in DB2 is different from that in SQL SERVER, the stored procedure in DB is as follows:
Create procedure kyjt. SP_TESTT ()
Specific kyjt. SQL060421171925193
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
Declare pname varchar (50 );
Declare pid int;
DECLARE v_count int;
Declare sqlstate char (5 );
DECLARE at_end int default 0;
DECLARE not_found condition for sqlstate '201312 ';

DECLARE C1 cursor for select id, name from kyjt. testt for update;
Declare continue handler for not_found
SET at_end = 1;
OPEN C1;
INS_LOOP:
LOOP
FETCH C1 into pid, PNAME;
If at_end = 1 then
LEAVE INS_LOOP;
End if;
Update kyjt. TESTT
Set name = (select name from kyjt. test B where B. ID = PID) WHERE CURRENT OF C1;
End loop;
CLOSE C1;
END