Advanced Search

Nested loop with cursor in procedure

/ Published in: MySQL
Save to your folder(s)
This snippet just showing how to make nested loops with cursors in MySQL.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DROP PROCEDURE IF EXISTS createQueenslandWards;
  2. DELIMITER //
  3. CREATE PROCEDURE createQueenslandWards()
  4. 	BEGIN
  5. 		DECLARE done INT DEFAULT 0;
  6. 		DECLARE wardName varchar(255);
  7. 		DECLARE Lang varchar(255);
  8. 		DECLARE Lat varchar(255);
  9. 		DECLARE cursorWard CURSOR FOR SELECT DISTINCT `ward` FROM `regions` WHERE `territory` = 'QLD';
  10. 		DECLARE cursorLangLat CURSOR FOR SELECT `centroid_y`, `centroid_x` FROM `regions` WHERE `ward` = wardName;
  11. 		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  12.  
  13. 		OPEN cursorWard;
  14.  
  15. 		REPEAT
  16. 			FETCH cursorWard INTO wardName;
  17. 			IF NOT done THEN
  18. 				OPEN cursorLanglat;
  19. 					block2: BEGIN
  20. 						DECLARE doneLangLat INT DEFAULT 0;
  21. 						DECLARE firstLang varchar(255) DEFAULT '';
  22. 						DECLARE firstLat varchar(255) DEFAULT '';
  23. 						DECLARE i int DEFAULT 1;
  24. 						DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneLangLat = 1;
  25.  
  26. 						REPEAT
  27. 							FETCH cursorLangLat INTO Lang, Lat;
  28.  
  29. 							IF i == 1 THEN
  30. 								SET firstLang = Lang;
  31. 								SET firstLat = Lat;
  32. 							END IF;
  33.  
  34. 							INSERT INTO `points` SET `coordinates` = GeomFromText(CONCAT('POINT(', Lang, ' ', Lat ,')')), `type` = wardName, to_text = CONCAT(Lang, ',', Lat), `description` = 'Queensland territory';
  35. 							SET i = i + 1;
  36. 						UNTIL doneLangLat END REPEAT;
  37.  
  38. 						INSERT INTO `points` SET `coordinates` = GeomFromText(CONCAT('POINT(', firstLang, ' ', firstLat ,')')), `type` = wardName, to_text = CONCAT(firstLang, ',', firstLat), `description` = 'Queensland territory';
  39.  
  40. 					END block2;
  41. 				CLOSE cursorLangLat;
  42. 			END IF;
  43. 		UNTIL done END REPEAT; 
  44.  
  45. 		CLOSE cursorWard;
  46.  
  47. 	END//
  48. DELIMITER ;
  49. CALL createQueenslandWards();

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.