In MariaDB and MySQL, Cursors can only be used within a Stored Program, are slow, and have very limited functionalities. That said, they can still be useful in some cases. This page explains how to use them in action, with a trivial example.
Here is our example Stored Procedure which uses a Cursor:
DELIMITER || DROP TABLE IF EXISTS `test`.`tab1`; CREATE TABLE `test`.`tab1` (`c` TINYINT UNSIGNED); INSERT INTO `test`.`tab1` (`c`) VALUES (1), (2), (3); DROP PROCEDURE IF EXISTS `test`.`demo`; CREATE PROCEDURE `test`.`demo`() READS SQL DATA BEGIN DECLARE `eof` BOOL; DECLARE `val` INT; DECLARE `crs` CURSOR FOR SELECT `c` FROM `test`.`tab1`; DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = TRUE; OPEN `crs`; `lp`: LOOP FETCH `crs` INTO `val`; IF `eof` IS TRUE THEN LEAVE `lp`; END IF; SELECT `val`; END LOOP; CLOSE `crs`; -- workaround for server bug DO (SELECT `c` FROM `test`.`tab1` WHERE FALSE); END; || DELIMITER ;
This Procedure is totally useless in the Real World, but serves us as an example: it returns a different ResultSet for each row in
test.tab1 table. Now, I’m going to explain how I implemented it.
DECLARE some variables.
eof is very important, becuase it indicates if the Cursor has read all rows from its query; for now, it’s
FALSE. We’ll use it later.
BOOL doesn’t really exist in Maria and My, and is mapper to
TRUE is just a synonym for 1, and
FALSE is a synonym for 0.But using the
FALSE keywords makes code more readable.
val is the variable which contains the value that we’re going to read from the table.
DECLARE the Cursor. In this statement there are the Cursor’s name (
crs) and its query. The query must be hardcoded here, because dynamic SQL is not allowed for Cursors. However, there is a workaround for this: see my post Cursors for Dynamic SQL.
Then, I declare an Error
HANDLER for the Errors which are in the
NOT FOUND class. Here we are interested in the 1329 error, which occurs when the Cursor has read all the rows from the query, and tries to read another row. I’ll explain this better later.
OPEN the Cursor. Its query is executed at this point.
And then, there is a
LOOP on the rows. What happens here? Well, first we try to
FETCH a new row. If this operation succedes the value of the only field we’re
SELECTing is copied into
val. We check the value of
eof, and since it is
FALSE, we skip the
IF part. And then we produce a ResultSet with the value we have read, and it is sent to the client. This happens three times, because we created a table with three rows.
The fourth time, the
FETCH statement fails, because there are no more rows. An Error raises, so we go into the
HANDLER which we defined before. Here, the
eof variable is set to
TRUE. Since it is a
CONTINUE HANDLER, after that statement the Routine execution is resumed and finds again the
IF. This time
TRUE, so we step into the
IF. And we find a
LEAVE statement which refers to the
lp is the
LOOP, so we step out of the
CLOSE the Cursor.
Now, we have a little problem. Versions of MySQL older than 5.6, and MariaDB, have a bug (which MariaDB is solving, however) that makes the 1329 Error survive after the
HANDLER. If we don’t do something to remove that error, it will be sent to the client. But if we run a query against a physical table, the Error is forgotten (the exact reason is beyond the purpose of this post). We don’t want another ResultSet to be returned, so we use the
SELECT as a subquery of the
DO statement (which doesn’t return anything).
WHERE FALSE is used to make sure that we only add an acceptable overhead to the Procedure.
This time I’ll give you some simple excercises. If you solve them, you can post your code in a comment, if you want. If you can’t solve them, you can post some questions so I’ll help you. I suggest you try these excercises, because you don’t really learn much without practice.
1) Modify the
demo Procedure so that it terminates if it finds more than one
2) Write a new Procedure which reads rows from the
PROCESSLIST table, in the
information_schema database, and
KILLs all connections that are
idle and exist since more than
N seconds – where
N is a Procedure’s parameter.