CREATE DEFINER=`root`@`localhost` PROCEDURE `diso_all_cities`(
	 p_countrycode varchar(3)
)
BEGIN


	declare myname varchar(23);
   declare popu varchar(23);
    
      -- this flag will be set to true when cursor reaches end of table
   DECLARE exit_loop BOOLEAN; 
    
	-- Declare the cursor
   DECLARE city_cursor CURSOR FOR
    select name, population
    from city where countrycode = p_countrycode;
     
   -- set exit_loop flag to true if there are no more rows
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
   
  
   -- open the cursor
   OPEN city_cursor;
   
   delete from city_process;
   
   -- start looping
   city_loop: 
   LOOP
     -- read the name from next row into the variables 
	FETCH  city_cursor INTO myname, popu;
     
     insert into city_process ( name, population )
    values ( myname, popu );
     
     
     
     -- check if the exit_loop flag has been set by mysql, 
     -- close the cursor and exit the loop if it has.
     IF exit_loop THEN
         CLOSE city_cursor;
         LEAVE city_loop;
         
     END IF;
     
   END LOOP city_loop;
   

	
    
END