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.

Let’s see how this works on the SELECT * FROM sakila.film example query:

Debugger for MySQL: Introduce CURSOR iteration
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;

Latest version

The latest version 1.3.1.1614 has been released on April 21, 2017

Download Debugger for MySQL

Testimonials

"...Aptly named “Debugger for MySQL”, I found this inexpensive tool to be quite easy to get the hang of, since it looks and behaves like most programming debugging environments."

"...Debugger for MySQL is a prime example of a tool that does one thing and does it well."

Partners

MySQL Partner