MariaDB Crash Course - Ben Forta [74]
Creating Cursors
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.
Opening and Closing Cursors
Cursors are opened using the OPEN CURSOR statement, like this:
Input
OPEN ordernumbers;
Analysis
When the OPEN statement is processed, the query is executed, and the retrieved data is stored for subsequent browsing and scrolling.
After cursor processing is complete, the cursor should be closed using the CLOSE statement, as follows:
Input
CLOSE ordernumbers;
Analysis
CLOSE frees up any internal memory and resources used by the cursor, and so every cursor should be closed when it is no longer needed.
After a cursor is closed, it cannot be reused without being opened again. However, a cursor does not need to be declared again to be used; an OPEN statement is sufficient.
* * *
Note: Implicit Closing
If you do not explicitly close a cursor, MariaDB closes it automatically when the END statement is reached.
* * *
Here is an updated version of the previous example:
Input
CREATE PROCEDURE processorders()
BEGIN
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Open the cursor
OPEN ordernumbers;
-- Close the cursor
CLOSE ordernumbers;
END;
Analysis
This stored procedure declares, opens, and closes a cursor. However, nothing is done with the retrieved data.
Using Cursor Data
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:
Input
CREATE PROCEDURE processorders()
BEGIN
-- Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- Open the cursor
OPEN ordernumbers;
-- Loop through all rows
REPEAT
-- Get order number
FETCH ordernumbers INTO o;
-- End of loop
UNTIL done END REPEAT;
-- Close the cursor
CLOSE ordernumbers;
END;
Analysis
Like the previous example, this example uses FETCH to retrieve the current order_num into a declared variable named o. Unlike the previous example, the FETCH here is within a REPEAT, so it is repeated over and over until done is true (as specified by UNTIL done END REPEAT;). To make this work,