Database Reference
In-Depth Information
After a cursor is opened, each row can be accessed individually using a
FETCH
statement.
FETCH
specifies what is to be retrieved (the desired columns) and
where retrieved data should be stored. It also advances the internal row pointer
within the cursor so the next
FETCH
statement will retrieve the next row (and
not the same one over and over).
The first example retrieves a single row from the cursor (the first row):
▼
Input
CREATE PROCEDURE processorders()
BEGIN
-- Declare local variables
DECLARE o INT;
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Open the cursor
OPEN ordernumbers;
-- Get order number
FETCH ordernumbers INTO o;
-- Close the cursor
CLOSE ordernumbers;
END;
▼
Analysis
Here
FETCH
is used to retrieve the
order_num
column of the current row
(it'll start at the first row automatically) into a local declared variable named
o
.
Nothing is done with the retrieved data.
In the next example, the retrieved data is looped through from the first row to
the last:
