Posted By

lucien144 on 08/19/10

Tagged

Versions (?)

Last Edited at 08/19/10 04:59pm

Statistics

Viewed 1974 times

Favorited by 0 user(s)

Related snippets

Nested loop with cursor in procedure

/ Published in: MySQL

This snippet just showing how to make nested loops with cursors in MySQL.

Highlighted TextExpand

DROP PROCEDURE IF EXISTS createQueenslandWards;
DELIMITER //
CREATE PROCEDURE createQueenslandWards()
	BEGIN
		DECLARE done INT DEFAULT 0;
		DECLARE wardName varchar(255);
		DECLARE Lang varchar(255);
		DECLARE Lat varchar(255);
		DECLARE cursorWard CURSOR FOR SELECT DISTINCT `ward` FROM `regions` WHERE `territory` = 'QLD';
		DECLARE cursorLangLat CURSOR FOR SELECT `centroid_y`, `centroid_x` FROM `regions` WHERE `ward` = wardName;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cursorWard;

REPEAT
			FETCH cursorWard INTO wardName;
			IF NOT done THEN
				OPEN cursorLanglat;
					block2: BEGIN
						DECLARE doneLangLat INT DEFAULT 0;
						DECLARE firstLang varchar(255) DEFAULT '';
						DECLARE firstLat varchar(255) DEFAULT '';
						DECLARE i int DEFAULT 1;
						DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneLangLat = 1;
						
						REPEAT
							FETCH cursorLangLat INTO Lang, Lat;
							
							IF i == 1 THEN
								SET firstLang = Lang;
								SET firstLat = Lat;
							END IF;
							
							INSERT INTO `points` SET `coordinates` = GeomFromText(CONCAT('POINT(', Lang, ' ', Lat ,')')), `type` = wardName, to_text = CONCAT(Lang, ',', Lat), `description` = 'Queensland territory';
							SET i = i + 1;
						UNTIL doneLangLat END REPEAT;

INSERT INTO `points` SET `coordinates` = GeomFromText(CONCAT('POINT(', firstLang, ' ', firstLat ,')')), `type` = wardName, to_text = CONCAT(firstLang, ',', firstLat), `description` = 'Queensland territory';

END block2;
				CLOSE cursorLangLat;
			END IF;
		UNTIL done END REPEAT;

CLOSE cursorWard;

END//
DELIMITER ;
CALL createQueenslandWards();

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.