Cursor-Based SQL Repetition:
One common need for repetition of statements within a stored procedure is when the procedure executes a query and needs to process the query results, row by row. All of the major dialects provide a structure for this type of processing. Conceptually, the
structures parallel the DECLARE CURSOR, OPEN CURSOR, FETCH, and CLOSE CURSOR statements in embedded SQL or the corresponding SQL API calls. However, instead of fetching the query results into the application program, in this case they are being fetched
into the stored procedure, which is executing within the DBMS itself. Instead of retrieving the query results into application program variables (host variables), the stored procedure
retrieves them into local stored procedure variables.
To illustrate this capability, assume that you want to populate two tables with data from the ORDERS table. One table, named BIGORDERS, should contain customer name and order size for any orders over $10,000. The other, SMALLORDERS, should contain the
salesperson's name and order size for any orders under $1000. The best and most efficient way to do this would actually be with two separate SQL INSERT statements with subqueries, but for purposes of illustration, consider this method instead:
1. Execute a query to retrieve the order amount, customer name, and salesperson
name for each order.
2. For each row of query results, check the order amount to see whether it falls into the
proper range for including in the BIGORDERS or SMALLORDERS tables.
3. Depending on the amount, INSERT the appropriate row into the BIGORDERS or
SMALLORDERS table.
4. Repeat Steps 2 and 3 until all rows of query results are exhausted.
5. Commit the updates to the database.
