As promised in the MySQL Cursors and Loops article, this article explores the use of cursors and their role in stored procedure programming. A cursor is a special kind of loop for traversing through an SQL resultset one row at a time. That allows us to perform operations on every record on a one-by-one basis.
The DECLARE <cursor name> statement declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor. Cursor declarations must appear after variable and condition declarations but before handler declarations.
DECLARE done INT DEFAULT 0;
DECLARE client_id INT;
DECLARE cur1 CURSOR FOR SELECT id from clients WHERE name IS NULL;
Also keep in mind that the cursor SELECT statement cannot have an INTO clause; it's strictly read-only.
While we won't be looking at the use of multiple cursors within the same stored proc, it is entirely possible to have multiple cursors in the same code block, but each cursor must have a unique name.
DECLARE ... HANDLER Syntax
The DECLARE… HANDLER statement specifies a handler that deals with one or more conditions. If one of these conditions occurs, the specified statement executes. The statement can be a simple statement such as SET var_name = value, or a compound statement written using BEGIN and END.
A handler_action constant may be used to indicate what action the handler takes after execution of the handler statement. The two that relate to cursors are:
- CONTINUE: Execution of the current program continues.
- EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.
The condition value for the DECLARE ... HANDLER identifies the specific condition or class of conditions that activates the handler. In cursors, we tend to employ the NOT FOUND condition value.
Hence, this is what a typical DECLARE… HANDLER statement looks like:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
NOT FOUND is shorthand for a class of SQLSTATE values that begin with '02'. It fires when a cursor reaches the end of a data set. At that point, a No Data condition fires with SQLSTATE value '02000'. Some people check for this string literal to detect a NOT FOUND condition, but I personally feel that the NOT FOUND constant is clearer.
When a CONTINUE handler for the NOT FOUND condition fires, that's your cue to set the value of a status variable so that you can terminate the cursor loop.
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cursor1; cursor_loop:LOOP FETCH cursor1 INTO var1, var2, var3; IF done=1 THEN LEAVE cursor_loop; END IF; -- Do something with the row fetched. END LOOP cursor_loop; CLOSE cursor1; SET done=0;
As a best practice, you should always reset your status variable to 0 after the cursor loop terminates; otherwise, subsequent or nested cursor loops may terminate prematurely.
Some Typical Uses for Cursors
There are many tasks well suited to cursors, but there are some common uses.
One is to loop through a recordset and update a single row in a table based on a single row lookup in another table.
DELIMITER // DROP PROCEDURE IF EXISTS sp_set_name // CREATE PROCEDURE sp_set_name () BEGIN DECLARE done INT DEFAULT 0; DECLARE client_id INT; DECLARE cur1 CURSOR FOR SELECT id from clients WHERE name IS NULL; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; read_loop: LOOP IF done THEN LEAVE read_loop; END IF; FETCH cur1 INTO client_id; UPDATE clients SET name = (SELECT name from orders WHERE id = client_id) WHERE id = client_id; END LOOP; close cur1; END; // DELIMITER ;
Our next example generates a comma-delimited list of phone numbers.
DELIMITER // DROP PROCEDURE IF EXISTS build_phone_list // CREATE PROCEDURE build_phone_list (INOUT phone_list varchar(4000)) BEGIN DECLARE finished INTEGER DEFAULT 0; DECLARE phone varchar(15) DEFAULT ""; DEClARE phone_cursor CURSOR FOR SELECT phone FROM contacts; DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; OPEN phone_cursor; phone_loop: LOOP FETCH phone_cursor INTO phone; IF finished = 1 THEN LEAVE phone_loop; END IF; SET phone_list = CONCAT(phone,",",phone_list); END LOOP phone_loop; CLOSE phone_cursor; END; // DELIMITER ; You can test the build_phone_list stored procedure using the following code: SET @phone_list = ""; CALL build_phone_list(@phone_list); SELECT @phone_list;
- Cursors are read-only so they can't be used to modify data, although in the loop and row traversal, you can take data from the cursor to perform an update. Thus, a write cursor like declare curs1 cursor for Update table1 set id=2 is strictly forbidden.
- Cursors are non-scrollable, so they can be traversed only in the forward direction and cannot skip rows.
- Generally, cursors are sensitive, insensitive, or asensitive to changes. MySQL cursors, on the other hand, are always "asensitive". Here is more information on each:
Changes made to the database after the cursor is opened are visible in the result table. The cursor has some degree of sensitivity to any updates or deletions made to the rows underlying its result table.
Once the cursor is opened, it does not have sensitivity to inserts, updates, or deletions of rows underlying its result table. The cursor is read-only and a temporary result is created. In addition, the SELECT statement cannot contain a FOR UPDATE clause and the application must allow a copy of the data.
An asensitive cursor points at the real data, as opposed to a cached or copy. This type of cursor is faster than the previous types because no copying is done. However, any changes made to the data from another process may affect the data being used by the cursor, leading to unpredictable results.
With all the processing power that cursors provide, there is a downside in that large resultsets can be as slow as molasses to process. As with all database tools, you must carefully consider how cursors meet the needs of your users and determine whether using them within a procedure or function will provide the best solution to the problem you are attempting to solve.