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.


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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.