CODEBOXER.com

MySQL 5.0 SP with Cursor Logic

I have written a bunch of SPs over the years (stored procedure, as I learned it, but MySQL calls them Stored Routines - SRs?). I even have my best/largest/most complicated SP ever linked here on codeboxer.com at http://codeboxer.com/read.php?a=the-Monstertrak-migration.

That one was of course written in PL/SQL, and I have now officially moved out of the world of Oracle and into the world of MySQL 5.0 - which is sort of like moving from New York City to San Jose (weather notwithstanding).

Anyway, I found precious little RECENT coverage on how MySQL implemented it's 'stored routines', and I got to tell ya it took some digging - never mind trying to figure out how cursors work. MySQL, btw, has an ok documentation section but it is mediocre at best, somehow following in the fragmented example of the Rails community in what seems to be the curse of the open-source ADD.

So in the spirit of this site which has always been to keep pieces of code lying around that I may use later - here is my very first MySQL sp, which does nothing really useful except use a cursor to insert values into a table. Come back next week for the syntax needed to use parameters, lol.

Other things to notice - you have to declare a separate handler for pulling out of a cursor loop when you run out of records (does anyone else remember fondly how easy ADO recordsets were to use?) but other than that things look all right in MySQL land. Bye Oracle!

CREATE PROCEDURE testy ()
BEGIN
declare a, b int;

DECLARE done int DEFAULT 0;

declare masterCursor CURSOR FOR
SELECT SUM(sales.amount) amt, sales.item_month
FROM sales
WHERE RETAILER_ID = 336
GROUP BY sales.item_month;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN masterCursor;
REPEAT
FETCH masterCursor INTO a, b;
IF NOT done THEN
insert into revplan (amount, item_month)
values (a,b);
END IF;

UNTIL done END REPEAT;

CLOSE masterCursor;

END

ps, gotta give a shout out to http://www.razorsql.com/ which so far has been easy to use and has served as stand-in IDE for Toad, which is what I was used to.Original post blogged on codeboxer.com.