MariaDB/MySQL Cursors: a brief Tutorial

Versione italiana

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.

First, I 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 TINYINT, while TRUE is just a synonym for 1, and FALSE is a synonym for 0.But using the BOOL, TRUE and FALSE keywords makes code more readable. val is the variable which contains the value that we’re going to read from the table.

Then, I 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.

Now I 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 eof is TRUE, so we step into the IF. And we find a LEAVE statement which refers to the lp label. lp is the LOOP, so we step out of the LOOP.

Then, we 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.

Exercises

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 NULL value.

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.

Enjoy!

Advertisement