Syntax error for mysql declaration variable-CodePudding

DELIMITER $$
CREATE procedure clean_data() 
BEGIN
    DECLARE today = (SELECT DATE_FORMAT(NOW(), '%Y-%m');
    DECLARE first_while_start  INTEGER DEFAULT 9;
    DECLARE second_while_start INTEGER DEFAULT 0;
    DECLARE first_while_count  INTEGER DEFAULT 14;
    DECLARE second_while_count INTEGER DEFAULT 59;
  
    WHILE first_while_start < first_while_count
    DO
        WHILE second_while_start < second_while_count
        DO
            DECLARE hr1 = (SELECT convert (first_while_start,char);
            DECLARE m1 = (SELECT convert (second_while_start,char);
            DECLARE m2 = (SELECT convert (second_while_start 1,char);
            
            INSERT INTO bidask_test2 
            SELECT *  FROM Bid_ask2 
            WHERE Stock_code ="2330"  AND DateTime  
            BETWEEN (SELECT CONCAT(today,' ',hr, m1)) AND  (SELECT CONCAT(today,' ',hr, m2))
            ORDER by DateTime  LIMIT 1;
            SET second_while_start = second_while_start   1;
        END WHILE;
        SET first_while_start = first_while_start   1;
        /*here comes the important line:*/
        SET second_while_start = 0;  END WHILE; END $$

I get an syntax error:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE hr1 = (SELECT convert (first_while_start,char)' at line 1

But for me, everything seems to be correct. i really don't have any clue! can anybody help?

thanks

CodePudding user response：

DECLARE must be the most first statements in a block.

Local variable cannot be declared and assigned at the same time.

CREATE procedure clean_data() 
BEGIN
    DECLARE today VARCHAR(255); 
    DECLARE first_while_start  INTEGER DEFAULT 9;
    DECLARE second_while_start INTEGER DEFAULT 0;
    DECLARE first_while_count  INTEGER DEFAULT 14;
    DECLARE second_while_count INTEGER DEFAULT 59;
    
    SELECT DATE_FORMAT(CURRENT_DATE, '%Y-%m') INTO today;

    WHILE first_while_start < first_while_count
    DO
        WHILE second_while_start < second_while_count
        DO
            BEGIN
                DECLARE hr1 VARCHAR(255);
                DECLARE m1 VARCHAR(255);
                DECLARE m2 VARCHAR(255);

                SELECT first_while_start, second_while_start, second_while_start 1  
                INTO hr1, m1, m2;
            
                INSERT INTO bidask_test2 
                SELECT *
                FROM Bid_ask2 
                WHERE Stock_code ="2330"  
                  AND `DateTime` BETWEEN CONCAT(today,' ',hr, m1) AND  CONCAT(today,' ',hr, m2)
                ORDER by `DateTime` LIMIT 1;
                SET second_while_start = second_while_start   1;
            END;
        END WHILE;
    SET first_while_start = first_while_start   1;
/*here comes the important line:*/
    SET second_while_start = 0;  
    END WHILE; 
END

CREATE procedure clean_data() 
BEGIN
    DECLARE today VARCHAR(255); 
    DECLARE first_while_start  INTEGER DEFAULT 9;
    DECLARE second_while_start INTEGER DEFAULT 0;
    DECLARE first_while_count  INTEGER DEFAULT 14;
    DECLARE second_while_count INTEGER DEFAULT 59;
    DECLARE hr1 VARCHAR(255);
    DECLARE m1 VARCHAR(255);
    DECLARE m2 VARCHAR(255);
    
    SELECT DATE_FORMAT(CURRENT_DATE, '%Y-%m') INTO today;

    WHILE first_while_start < first_while_count
    DO
        WHILE second_while_start < second_while_count
        DO
            SELECT first_while_start, second_while_start, second_while_start 1  
            INTO hr1, m1, m2;
            
            INSERT INTO bidask_test2 
            SELECT *
            FROM Bid_ask2 
            WHERE Stock_code ="2330"  
              AND `DateTime` BETWEEN CONCAT(today,' ',hr, m1) AND  CONCAT(today,' ',hr, m2)
            ORDER by `DateTime` LIMIT 1;
            SET second_while_start = second_while_start   1;
        END WHILE;
    SET first_while_start = first_while_start   1;
/*here comes the important line:*/
    SET second_while_start = 0;  
    END WHILE; 
END

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=09a8930bd020c42a22168d14b879d773