MySQL Stored Procedures: Part 3 - Page 2

Cursors

Cursors are another of the frequently demanded features now supported by MySQL 5. For those familiar with other DBMS implementations, MySQL 5 does not yet support them completely. In fact, it has some quite severe restrictions. MySQL 5's cursors are asensitive (so you should not update the table while using a cursor, otherwise you will get unpredictable results), read-only (you cannot update using the cursor position) and non-scrolling (you can only move forward to the next record, not back and forth).

In general, cursors are used to access a resultset that can be retrieved one or more rows at a time. They are also used for positioning a pointer at a specific row, and can allow updates to rows based upon this current position; although as mentioned, MySQL does not support this. The term cursor is short for CUrrent Set Of Records. In our examples, we will use the same table, sp1, we used in part 1 of this series. When we left off, the table contained the following records:

mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
 id: 3
txt: 414243
*************************** 2. row ***************************
 id: 40
txt: 444546
2 rows in set (0.00 sec)

Here is a sample procedure containing a cursor.

mysql>
CREATE PROCEDURE sp9 (OUT rx INT, OUT ry INT)
 BEGIN
  DECLARE x,y INT;
  DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;
  OPEN sp1_cursor;
   FETCH sp1_cursor INTO x,y;
  CLOSE sp1_cursor;
  SET rx = x;
  SET ry = y;
 END |
 
mysql> CALL sp9(@x,@y)\G
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x,@y\G
*************************** 1. row ***************************
@x: 3
@y: 414243
1 row in set (0.01 sec)

Here we declared variables (x and y and a cursor (sp1_cursor). The order of declarations is important - they must be in a particular order - variables and conditions, then cursors, then handlers.

Next is a new statement - OPEN sp1_cursor, which activates the cursor we declared earlier. The FETCH statement is the one that does all the magic, returning the next row of the actual resultset. Results must be placed somewhere, and the two variables x and y are the recipients of the two columns returned by the SELECT id,txt FROM sp1 query that makes up the cursor. The cursor is then closed (although MySQL will free the resources when it reaches the end of the compound statement block for you if you do not explicitly do so), and the results assigned to the two OUT variables. By calling the procedure, and then querying the two session variables that receive the results, we can see that they have been populated with the first row from the sp1 table, as expected.

However, returning just one row is not particularly useful. We need to loop through the entire resultset and return the results. We can achieve this using a simple REPEAT UNTIL loop, which we were introduced to in part 2.

mysql>
CREATE PROCEDURE sp10 (OUT rx INT, OUT ry INT)
 BEGIN
  DECLARE x,y,z INT;
  DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET z = 1;
  OPEN sp1_cursor;
  REPEAT
   FETCH sp1_cursor INTO x,y;
   UNTIL (z=1)
  END REPEAT;
  CLOSE sp1_cursor;
  SET rx = x;
  SET ry = y;
 END |
 
mysql> CALL sp10(@x,@y)|
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x,@y\G
*************************** 1. row ***************************
@x: 40
@y: 444546
1 row in set (0.00 sec)

The handler is declared for the NOT FOUND condition. The handler sets the variable z to 1, and z=1 is the condition the REPEAT UNTIL loop tests for. As mentioned earlier, the NOT FOUND condition includes all errors with an SQLSTATE beginning with 02, one of which is the No data to FETCH error. This will apply when we have retrieved all the rows in the table. As expected, upon calling the procedure and querying the session variables, this time we see the last row of the sp1table, as in the procedure each row's data is assigned to rx and ry, overwriting the previous contents.

Conclusion

This month was the last of our series on stored procedures. MySQL developers will find them a great new tool in developing applications, and applications running on MySQL can now finally make use of the robustness and flexibility of stored procedures, removing unwanted logic from the application code. Next month, we look at the closely related stored functions feature.

Resources

• MySQL List of error codes

» See All Articles by Columnist Ian Gilfillan