Using Cursors - MariaDB Crash Course - page 210

Caution
DECLARE Statement Sequence DECLARE statements, if used, must be issued in a
specific order. Local variables defined with DECLARE must be defined before any cur-
sors or handlers are defined, and handlers must be defined after any cursors. Failure to
follow this sequencing generates an error message.
If you were to call this stored procedure it would define variables and a
CONTINUE HANDLER , define and open a cursor, repeat through all rows,
and then close the cursor.
With this functionality in place you can now place any needed processing
inside the loop (after the FETCH statement and before the end of the loop).
Note
REPEAT or LOOP ? In addition to the REPEAT statement used here, MariaDB also
supports a LOOP statement that can be used to repeat code until the LOOP is manu-
ally exited using a LEAVE statement. In general, the syntax of the REPEAT statement
makes it better suited for looping through cursors.
To put this all together, here is one further revision of our example stored pro-
cedure with cursor, this time with some actual processing of fetched data:
▼ Input
CREATE PROCEDURE processorders()
BEGIN
-- Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- Create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT, total DECIMAL(8,2));
-- Open the cursor
OPEN ordernumbers;