I've been dealing with an error in one procedure, where the querie where I declare the cursor is giving an error, it says that I'm missing an END but I see all my queries close and in the appropriate place. Do you see what I'm forgetting?

DELIMITER $$
    CREATE PROCEDURE pCityMean(IN vNameCountry VARCHAR(20))
    BEGIN
    
    DECLARE vid INT default 0;
    DECLARE done INT DEFAULT 0;
    SELECT id_country INTO @id_country From country WHERE name = CONCAT('"%',@vNameCountry,'%"');
    DECLARE cur1 CURSOR FOR SELECT c.id FROM (city AS c JOIN temp_by_city AS tbc JOIN country AS co ON c.id = tbc.id_city AND tbc.id_country = co.id_country) WHERE co.id_country = @id_country GROUP BY c.name;  -- This one
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1
    OPEN cur1
        bucle1:LOOP
        FETCH cur1 INTO vid
        if done = 1 THEN
            leave bucle1;
    SELECT YEAR(dt), COUNT(AverageTemperature),ROUND(AVG(AverageTemperature)) FROM temp_by_city 
    WHERE id_country = @id_country AND id_city = vid
    GROUP BY YEAR(dt), vid;
    
        END LOOP bucle1
    CLOSE cur1
    END $$ 
    DELIMITER ;

CodePudding user response：

There are numerous syntax errors in your procedure. Here's what I see so far:

• All DECLARE statements must be before all other statements (as noted in the comment from @Luuk above).
• Mixing up vNameCountry and @vNameCountry. In MySQL these are two separate variables.
• Many of your statements are missing needed semicolon termination.
• The IF is missing its needed END IF.
• Invalid use of GROUP BY.
• Invalid use of LIKE wildcards.

Besides this, I don't see any need for a cursor in this procedure at all.

The procedure would be far simpler as a single query like this:

DELIMITER $$

CREATE PROCEDURE pCityMean(IN vNameCountry VARCHAR(20))
BEGIN
  SELECT tbc.id_city,
    YEAR(tbc.dt) AS `year`,
    COUNT(*) AS count,
    ROUND(AVG(tbc.AverageTemperature)) AS avg_temp
  FROM temp_by_city AS tbc
  JOIN country AS co ON tbc.id_country = co.id_country
  WHERE co.name = vNameCountry
  GROUP BY tbc.id_city, YEAR(tbc.dt);
END$$

DELIMITER ;

I understand the above procedure would produce a different result than yours. The procedure you wrote would produce multiple result sets, one for each city. But your result sets would not identify which result set goes with which city, because the id_city is not a column in any of the results.

I also eliminated the LIKE wildcards. You should require this procedure input to be a complete country name.