In the following example, the DECLARE CURSOR statement defines the cursor, C1. The C1 cursor could be used in an UPDATE statement to update the column named in the clause, or used in a DELETE statement to delete a row. In the below example SCOUNT, WPNUM1,VAR1 is host variables. 0046.00 C/EXEC SQL 0047.00 C+ DECLARE C1 SCROLL CURSOR FOR Declaring the. Hence if any kind of updating or insertion happens in the database the cursor is modified accordingly to provide the latest data.
This Oracle tutorial explains how to use the Oracle/PLSQL WHERE CURRENT OF statement with syntax and examples.
Description
If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.
Syntax
The syntax for the WHERE CURRENT OF statement in Oracle/PLSQL is either:
OR
Note
- The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.
Example
Updating using the WHERE CURRENT OF Statement
Here is an example where we are updating records using the WHERE CURRENT OF Statement:
Deleting using the WHERE CURRENT OF Statement
Here is an example where we are deleting records using the WHERE CURRENT OF Statement:
When processing a CURSOR, DB2 SQL give option to update or delete data in base table based on current position of CURSOR in result table. This is called POSITIONED UPDATE or POSITIONED DELETE.Let say there is a table EXAMPLETABLE likeCREATE TABLE EXAMPLETABLE (id numeric, name char(10), processedFlag char(1));idnameprocessedFlag1SUMITN2AMITY3JOHNN4ROHITY1SUMITNYou need to write some processing logic for each record of this table which is not processed yet(means processedFlag ‘Y’) and when processing for a record is completed processedFlag should be updated to ‘Y’.