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.

Advertisements