Archive for the ‘SQL’ Category

MySQL Stored Procedure Cursor exit prematurely

Posted: January 27, 2018 in Knowledge Base, SQL
0

MySQL cursor is a convenient way to loop through data, but it may exit prematurely sometimes.

Below is one example 

drop procedure if exists a_stored_procedure;

DELIMITER $$

CREATE PROCEDURE a_stored_procedure()
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE user_id, group_id BIGINT;
  DECLARE a_cursor CURSOR FOR select userId from User;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN a_cursor;

  read_loop: LOOP
    FETCH a_cursor INTO user_id;
    IF done THEN
      LEAVE read_loop;
    END IF;

    select groupId into @group_id from UserGroup where userId = user_id limit 1;
    
    -- other statements

    
  END LOOP;

  CLOSE a_cursor;
 
 
END;

In the above stored procedure with cursor, the cursor will terminate after no more users, but actually it will also terminiate when select groupId into @group_id returns no result because it will also trigger Not found handler.

Reference: https://dev.mysql.com/doc/refman/5.6/en/declare-handler.html