/ Published in: MySQL

This snippet just showing how to make nested loops with cursors in MySQL.
Expand |
Embed | Plain Text
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();
You need to login to post a comment.