The words CURRENT OF, followed by the cursor name, refer to the last row that was retrieved through the cursor with the FETCH command. The set-at-a-time update to which you referred is called a searched update.
The FETCH_STATUS value is 0 until there are all rows are fetched then FETCH_STATUS becomes 1.
The Output of the above program will be as follows A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes.
Check out all our database development sessions at OOW19 Thanks for the question, Peter.
Asked: January 12, 2016 - pm UTC Answered by: Chris Saxon - Last updated: January 13, 2016 - am UTC Category: Database - Version: 184.108.40.206 Viewed 10K times!
Using the where current of for the deletes means it'll delete the row you're processing: Your session will be able to see the results of the updates though.
Depending upon what else happens in the transaction, this may not be what you want.
As it processes each row, it needs to split amounts into separate new records (accounting data being redistributed to other accounting categories). Clearly, there are multiple better ways to do this.
It deletes WHERE CURRENT OF and then inserts multiple records that would match the cursor's selection criteria. But for the sake of understanding, is a query's result set being processed row-by-row in a cursor loop isolated from DML performed on the same table matching the selection criteria after the cursor is opened?
Instead of this complex looping construct, I can use the following single SQL statement instead: This single SQL statement should run faster than the same work being done in the loop.