I wanted to iterate through a result set and then do something with each individual result and I wanted to do it in MySQL(i.e using sql) itself without using a programming language. So, I started off looking for looping structures in MySQL and came upon cursors.
Reading through cursors, got to know that cursors can be used only inside stored procedures, functions and triggers. I had heard of stored procedures before but never of functions. This page explains the difference between the two. Since I was doing some throwaway programming, I did not bother to go in depth into it but the general impression I got was, a function has some restrictions not present in stored procedures.
An extremely simple stored procedure is given below to help newbies. This stored procedure iterates through a table Bar and prints the Id of the table.
DELIMITER $$ DROP PROCEDURE IF EXISTS <schema>.FOO $$ CREATE PROCEDURE <schema>.FOO() BEGIN DECLARE SOME_ID INT; DECLARE DONE_ITERATING BOOLEAN; DECLARE CUR CURSOR FOR SELECT ID AS ID FROM <schema>.BAR; DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_ITERATING = TRUE; OPEN CUR; THE_LOOP:LOOP IF DONE_ITERATING THEN CLOSE CUR; LEAVE THE_LOOP; END IF; FETCH CUR INTO SOME_ID; SELECT SOME_ID; END LOOP THE_LOOP; END $$ DELIMITER;
Once the stored procedure is loaded into db, to call the stored procedure, use the below query:
CALL <schema>.FOO();
Initially, my variable was named ID instead of SOME_ID. Also, the field being read was named ID. Stored procedure was not displaying the values. Once ID field was changed to SOME_ID, it started displaying the values. So, name your variables different from the column names being read.
