MariaDB Crash Course - Ben Forta [75]
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
This statement defines a CONTINUE HANDLER, code that will be executed when a condition occurs. Here it specifies that when SQLSTATE '02000' occurs, then SET done=1. And SQLSTATE '02000' is a not found condition and so it occurs when REPEAT cannot continue because there are no more rows to loop through.
* * *
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 cursors 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 manually 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 procedure 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;
-- Loop through all rows
REPEAT
-- Get order number
FETCH ordernumbers INTO o;
-- Get the total for this order
CALL ordertotal(o, 1, t);
-- Insert order and total into ordertotals
INSERT INTO ordertotals(order_num, total)
VALUES(o, t);
-- End of loop
UNTIL done END REPEAT;
-- Close the cursor
CLOSE ordernumbers;
END;
Analysis
In this example, we added another variable named t (this stores the total for each order). The stored procedure also creates a new table on the fly (if it does not exist) named ordertotals. This table stores the results generated by the stored procedure. FETCH fetches each order_num as it did before, and then uses CALL to execute another stored procedure (the one we created in the previous chapter) to calculate the total with tax for each order (the result of which is stored in t). And then finally, INSERT is used to save the order number and total for each order.
To try this example, simple CALL it:
Input
CALL processorders();
This stored procedure returns no data, but it does create and populate another table that can then be viewed using a simple SELECT statement:
Input
SELECT *
FROM ordertotals;
Output
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20009 | 40.78 |
+-----------+---------+
And then you have it, a complete working example of stored procedures, cursors, row-by-row processing, and even stored procedures calling other stored procedures.
Summary
In this chapter, you learned what cursors are and why they are used. You also saw examples demonstrating basic cursor use, as well as techniques for looping through cursor results and for row-by-row processing.
25. Using Triggers
In this chapter, you learn what triggers are, why they are used, and how. You also look at the syntax for creating and using them.
Understanding Triggers
MariaDB statements