MySQL stored programs: cursors2012-06-16 23:23:30 0 comments
Every cursor must have at least 4 elements named below:
CURSOR FOR keywords
The sample procedure that does nothing more that just creating cursor, looks like this:
CREATE PROCEDURE sample_procedure () BEGIN DECLARE cur CURSOR FOR SELECT title FROM film; END;
I've put every part in a new line but in a such simple code it's not recommended. Declaring cursor it's necessary to put it after all variables declarations. It's just a meter of order that this code will work:
DECLARE str VARCHAR(50); DECLARE cur CURSOR FOR SELECT title FROM film;
...and this on will give an error.
DECLARE cur CURSOR FOR SELECT title FROM film; DECLARE str VARCHAR(50);
MySQL Database Error: Variable or condition declaration after cursor or handler declaration.
When the cursor is ready we can finally use it. Use of cursor is based on 3 commands:
The first one and the last one are very simple. When you starting using cursor just write OPEN cursor_name, where cursor_name is your real cursor's name. According to this rule:
When the cursor is not needed anymore you can close it by typing CLOSE cursor_name.
You can't open cursor when it's open, you can't closed cursor that isn't open and you can't fetch cursor that is closed. Contrary to the open and the close commands the fetch is can be quite complicated. A fetch statement consists of 4 elements:
list of variables delimited by a comma
In this statement we are dispatching data from query's result to the variables declared in stored procedure. Most important thing is to set on a list as many variables as columns return in query - every column must have its own variable. Below I present next stage of a procedure's body. I used SELECT to show that the cursor is working, but obviously it's not necessary.
DECLARE str VARCHAR(50); DECLARE cur CURSOR FOR SELECT title FROM film; OPEN cur; FETCH cur INTO str; SELECT str; CLOSE cur;
OK. Cursor is working, at least in some way, but shouldn`t there be more that one row (as many as rows in the film table to be exact)? Of course there should be. So what happened? Cursor was opened, then value of the first row was inserted into str variable, the select statement was made and after all of this the cursor was closed. Probably you are already know that cursor processing must be more complex when its select query retrieves result set rather than single row. This is the place where loops came into play. In MySQL stored programs we can make use of 3 types of loops:
LOOP - END LOOP
WHILE - END WHILE
If you are familiar with some programming language you have probably meet while and repeat loops. Both of them have conditions that tells the program when it should leave the loop. Simple MySQL's loop don't require any type of condition of that type, so loop in its most basic form will run forever. Code of infinite loop is below:
DECLARE i int default 1; simple_loop: LOOP SELECT i; SET i = i + 1; END LOOP simple_loop;
When instead showing infinite list of number we put inside the loop a fetch cursor command we will receive a list of title and an error...
simple_loop: LOOP FETCH cur INTO str; SELECT str; END LOOP simple_loop;
No data - zero rows fetched, selected, or processed
If there is any sense using loop that don't even know where it have to stop? Of course not, but fortunately we can quickly fix it with at least 2 ways. First one is based on a counting fetched rows and comparing current number with number of rows return by query statement. It can look like this:
DECLARE str VARCHAR(50); DECLARE cnt INT; DECLARE i INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT title FROM film LIMIT 5; SELECT COUNT(*) FROM film WHERE film_id <= 5 INTO cnt; OPEN cur; simple_loop: LOOP SET i = i + 1; IF i > cnt THEN LEAVE simple_loop; END IF; FETCH cur INTO str; SELECT str; END LOOP simple_loop; CLOSE cur;
The second solution is based on error handling. We have to define what should happen when there is no more rows. Code below is shorter, cleaner and will give the same result as before.
DECLARE str VARCHAR(50); DECLARE end_loop INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT title FROM film LIMIT 5; DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_loop = 1; OPEN cur; simple_loop: LOOP FETCH cur INTO str; SELECT str; IF end_loop = 1 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; CLOSE cur;
Remember that declaration of a handler must follow cursor declaration. Finally our cursor is completed and it's really working. Next time I'll build the same cursor using two other loops.