Debugger for MySQL: Introduce CURSOR Iteration

Index

MySQL Reference Manual

Working with the cursor usually requires you to write a bunch of code: declare variables, declare a NOT FOUND handler, declare cursor with the SELECT statement, choose a loop construction (LOOP, WHILE, REPEAT), correctly handle the “done” variable etc. The Introduce CURSOR iteration command saves you many hours and totally excludes the need of chores. You simply indicate a SELECT statement and the rest is done automatically. The command is available from the code editor context menu.

BEGIN
  DECLARE v_film_id SMALLINT(5) UNSIGNED;
  DECLARE v_title VARCHAR(255) CHARACTER SET utf8;
  DECLARE v_description TEXT CHARACTER SET utf8;
  DECLARE v_release_year YEAR(4);
  DECLARE v_language_id TINYINT(3) UNSIGNED;
  DECLARE v_original_language_id TINYINT(3) UNSIGNED;
  DECLARE v_rental_duration TINYINT(3) UNSIGNED;
  DECLARE v_rental_rate DECIMAL(4,2);
  DECLARE v_length SMALLINT(5) UNSIGNED;
  DECLARE v_replacement_cost DECIMAL(5,2);
  DECLARE v_rating ENUM('G','PG','PG-13','R','NC-17') CHARACTER SET utf8;
  DECLARE v_special_features SET('Trailers','Commentaries','Deleted Scenes',
    'Behind the Scenes') CHARACTER SET utf8;
  DECLARE v_last_update TIMESTAMP;
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE c_film CURSOR FOR
    SELECT * FROM sakila.film;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN c_film;
  c_film_loop: LOOP
    FETCH c_film INTO v_film_id, v_title, v_description, v_release_year, 
      v_language_id, v_original_language_id, v_rental_duration, v_rental_rate, 
      v_length, v_replacement_cost, v_rating, v_special_features, v_last_update;
    IF done THEN LEAVE c_film_loop; END IF;

    -- Insert code here 

  END LOOP;
  CLOSE c_film;
END;