Analysis of cursor usage in DB2 (1)

When using a cursor in a DB2 database, you may have these questions: If you set a cursor for a table, can you update the table at the same time? How can this operation not affect the cursor position? This article will explain the solution for your reference.

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 the 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