Database Reference
In-Depth Information
2.
After it is declared, the cursor must be opened for use. This process
actually retrieves the data using the previously defined
SELECT
state-
ment.
3.
With the cursor populated with data, individual rows can be fetched
(retrieved) as needed.
4.
When it is done, the cursor must be closed.
After a cursor is declared, it may be opened and closed as often as needed. After
it is open, fetch operations can be performed as often as needed.
Cursors are created using the
DECLARE
statement (seen in Chapter 23,
“Working with Stored Procedures”).
DECLARE
names the cursor and takes
a
SELECT
statement, complete with
WHERE
and other clauses if needed.
For example, this statement defines a cursor named
ordernumbers
using a
SELECT
statement that retrieves all orders:
▼
Input
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;
▼
Analysis
This stored procedure does not do a whole lot. A
DECLARE
statement is used
to define and name the cursor—in this case
ordernumbers
. Nothing is done
with the cursor, and as soon as the stored procedure finishes processing it ceases
to exist (as it is local to the stored procedure itself).
Now that the cursor is defined, it is ready to be opened.
Cursors are opened using the
OPEN CURSOR
statement, like this:
▼
Input
OPEN ordernumbers;
