How to use end loop for cursor loop in mysql

Question

The following code was used for finding the first five highest paid employee from a table named employee7 and add the details to another table named temp using cursor in mysql but I got the error

you have an error in your mysql syntax check the mannual that corresponds to mysql server version for the right syntax to use near 'loop cursorloop; close c7; end; at line 17

DELIMITER //
create procedure cursordemo()
begin 
    declare eno int(8);
    declare ename varchar(15);
    declare esal int(10);
    declare c7 cursor for select empno,empnm,empsal from employee7 order by empsal desc;
    open c7;
    cursorloop:loop
    fetch c7 into eno,ename,esal;
    if c7.rowcount>5 then  
        leave cursorloop;
    insert into temp values (esal,eno,ename);
    end loop cursorloop;
    close c7;
    end //
delimiter ;

You don't a CURSOR for this not even an stored procedure you can replace all with a simple query insert into temp values ([columns]) select [columns] from employee7 order by empsal desc — Raymond Nijland
– Raymond Nijland, Commented Feb 17, 2018 at 10:52
Thank you it was the error ,but after running the program using the command select * from temp shows empty set as output eventhough the table employee7 have 8 rows of data. — SRG
– SRG, Commented Feb 17, 2018 at 11:19
you have mentioned the condition of rowcount>5 then leave the loop,then how come data will be inserted in the temp table? — Kedar Limaye
– Kedar Limaye, Commented Feb 17, 2018 at 12:55

P.Salmon · Accepted Answer · 2018-02-17 12:56:25Z

5

I don't know where you got the idea of using c7.rowcount but that's not valid mysql the more usual way to exit a cursor loop in mysql is to use a handler fro example

DELIMITER //
create procedure cursordemo()
begin 
    declare eno int(8);
    declare ename varchar(15);
    declare esal int(10);
    declare done int default 0;
    declare c7 cursor for select emp_no,last_name,salary from employees order by salary desc;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open c7;
    cursorloop:loop
    if done = true then  
        leave cursorloop;
    end if;
    fetch c7 into eno,ename,esal;

    insert into temp values (esal,eno,ename);
    end loop cursorloop;
    close c7;
    end //
delimiter ;

answered Feb 17, 2018 at 12:56

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SRG Over a year ago

But I want to select only the first five rows.

P.Salmon Over a year ago

Either add a limit clause to your select statement or declare a counter , increment it by 1 each time thru the loop and add a test to the if condition.

SRG Over a year ago

In sql we use cursor%rowcount command is there any similar commands in mysql

P.Salmon Over a year ago

Not that I know of. I assume you are used to plsql which is very different to mysql.

Collectives™ on Stack Overflow

How to use end loop for cursor loop in mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related