Oracle updating recordset during cursor
In this code I will use two different "SELECT TOP 1" statements and a WHILE loop to return all 5 records. Here you can see the first SELECT statement selects only the first Customer ID. The WHILE loop is controled by the local variable @Row Num.
Each time through the WHILE loop, the Row Number and Customer ID are printed out.
Inside the WHILE loop the @Row Num variable is incremented by 1 for each record processed.
The calculated Row Number and @Cust Id are then printed out.
A cursor allows applications a mechanism to process through a result set one row at a time.
With a cursor an application is allowed to position itself to a specific row, scroll back and forth, and a number of other things.
To do this I will issue an initial SELECT statement that will return the first row, then a series of follow on SELECT statements where each SELECT statement retrieves the next row.It would take a series of articles to describe all the functionality of a cursor.For the purpose of this article I'm only going to focus on how to use the default scrolling functionality of a cursor.This is done by using the "TOP 1" clause of the SELECT statement, and a WHERE statement.I will use the same example as above and only return the top 5 Customer ID's from the Northwind database Customers table.
Search for oracle updating recordset during cursor:
Lastly, a "FETCH NEXT" statement is used to retrieve the next row before the next cycle of the WHILE loop.