Im having trouble with a procedure, Im getting error 1064 where im setting a value of a declare variable.

heres the code.

use lab3New;
delimiter $
drop procedure if exists Q4C;
create procedure Q4C()  
BEGIN
    declare bonus decimal(8,2);
    declare count int;
    declare done int;
    declare empSL decimal(8,2);
    declare tmpSL decimal(8,2);
    declare empID int;
    
    set bonus = 100000;
    set count = 0;
    
    declare myCursor cursor for
    select salary, employee_id
    from employees
    order by salary asc;
    
    declare continue handler for not found set done = TRUE;
    open myCursor;
    read_loop:LOOP
        fetch myCursor into empSL, empID;
        if done then leave read_loop; end if;
        set tmpSL = .2 * empSL;
        if tmpSL > bonus then
            update employees
            set salary = salary   bonus
            where employee_id = empID;
            
            set bonus = 0;
            set count = count   1;
            end loop;
        else 
            set bonus = bonus - tmpSL;
            set salary = salary   tmpSL
            where employee_id = empID;
            set count = count   1;
        end if;
    end loop;
    close myCursor;
    
    select count;
    
END$
delimiter ;

and the table

 ---------------- -------------- ------ ----- --------- ------- 
| Field          | Type         | Null | Key | Default | Extra |
 ---------------- -------------- ------ ----- --------- ------- 
| employee_id    | decimal(6,0) | NO   | PRI | NULL    |       |
| first_name     | varchar(20)  | YES  |     | NULL    |       |
| last_name      | varchar(25)  | NO   |     | NULL    |       |
| email          | varchar(25)  | NO   | UNI | NULL    |       |
| phone_INT      | varchar(20)  | YES  |     | NULL    |       |
| hire_date      | date         | NO   |     | NULL    |       |
| job_id         | varchar(10)  | NO   |     | NULL    |       |
| salary         | decimal(8,2) | YES  |     | NULL    |       |
| commission_pct | decimal(2,2) | YES  |     | NULL    |       |
| manager_id     | decimal(6,0) | YES  |     | NULL    |       |
| department_id  | decimal(4,0) | YES  |     | NULL    |       |
 ---------------- -------------- ------ ----- --------- ------- 

this is the error

ERROR 1064 (42000) at line 3: 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 myCursor cursor for
    select salary, employee_id
    from employees
    order b' at line 11

any ideas? Ive tried using default and setting the value but in my loop where I increment count i then get the error 1064 at that line. thank you.

CodePudding user response：

Any DECLARE statements must be placed before any other statement in the code block. Whereas you put DECLARE CURSOR (and DECLARE HANDLER) after SET statements.

MySQL 8.0 Reference Manual / ... / DECLARE Statement

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.