Posted By

lucien144 on 08/19/10


Tagged

procedure loop cursor nested


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

virendrakryadav


Nested loop with cursor in procedure


 / Published in: MySQL
 

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

  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  

You need to login to post a comment.