MySQL (stored procedure) cursor loop works without CONTINUE HANDLER

Question

When I run the following MySQL server stored procedure I would expect that it run forever because there is no CONTINUE HANDLER

BEGIN
   DECLARE p INT(11) DEFAULT 0;
   DECLARE no_more_rows BOOLEAN DEFAULT false;

   DECLARE testA INT;

   DECLARE iCursor CURSOR FOR SELECT test_a FROM Temp;

   OPEN iCursor;
       id_loop: LOOP
           FETCH iCursor INTO testA;
           SET p = p + 1;
           INSERT INTO Temp2 (test1, test2) VALUES (testA, p);
       END LOOP; 
   INSERT INTO Temp3 SET Finished=1;        
   CLOSE iCursor;
END

But it seems that the CURSOR is working perfect and wrote all the values from the Temp table to the Temp2 table (and there are also no more values of the counter p written into the table). The procedure is also not running any more when I check the running processes on the MySQL server.

But the Finished code is not written into the Temp3 table and I therefore suppose the cursor is also not closed properly.

Does anybody know what happens here?

Why is the loop not running endless?

Is it ok to implement a cursor like that (in case there is no code that should be run after the loop)?

wchiquito · Accepted Answer · 2014-03-07 01:03:02Z

1

How are you invoking the stored procedure?

I get:

mysql> CALL sp_test;
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

The error terminates execution of the stored procedure, so do not write on temp3 nor closes the cursor.

SQL Fiddle demo

answered Mar 7, 2014 at 1:03

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

1 Comment

Rigel Over a year ago

It was executed from php before and the error was just ignored. Thank you for the answer that clarified things for me.

Collectives™ on Stack Overflow

MySQL (stored procedure) cursor loop works without CONTINUE HANDLER

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related