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
»
See All Articles by Columnist Ian Gilfillan
» 