Balsher Singh Balsher Singh - 2 months ago 4
MySQL Question

What is the correct error for cursor, and what are they for

From my books I am trying to understand what creating a 'CURSOR' does for interaction between stored procedures and databases. I don't have the Database being use in the book hence I am using the world database in MySQL Workbench. I was trying to recreate the example in the book with this world Database to see what 'using a cursor in a stored procedure' really does, I don't understand with the example is trying to FETCH cur1 INTO in the example is for. Just trying to see what is suppose to happen but I recieved the following syntax error:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cur1; count' at line 13

DELIMITER $$

DROP PROCEDURE IF EXISTS cursor_example
$$
CREATE PROCEDURE cursor_example()
READS SQL DATA
BEGIN
DECLARE i_Name CHAR(3);
DECLARE i_SurfaceArea FLOAT(10,2);

DECLARE done INT DEFAULT 0;

DECLARE cur1 CURSOR FOR
SELECT Name, SurfaceArea
FROM country

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN cur1;
country_loop:LOOP
FETCH cur1 INTO i_Name, i_SurfaceArea;
IF done=1 THEN
LEAVE country_loop;
END IF;
END LOOP country_loop;
CLOSE cur1;

END;
$$

DELIMITER ;


Just generally how the whole procedure is suppose to work and what is it for.
Thanks.

Answer

You were missing a semi-colon after FROM country. Use the following pattern:

DROP PROCEDURE IF EXISTS cursor_example;
DELIMITER $$
CREATE PROCEDURE cursor_example()
   READS SQL DATA
BEGIN
   DECLARE i_Name CHAR(3);
   DECLARE i_SurfaceArea FLOAT(10,2);

   DECLARE done     INT DEFAULT 0;

   DECLARE cur1 CURSOR FOR
     SELECT Name, SurfaceArea
      FROM country;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    OPEN cur1;
    country_loop:LOOP
        FETCH cur1 INTO i_Name, i_SurfaceArea;
        IF done=1 THEN
            LEAVE country_loop;
        END IF;

        -- right here is where you do stuff with those variables

    END LOOP country_loop;
    CLOSE cur1;

END;
$$

DELIMITER ;

I don't understand with the example is trying to FETCH cur1 INTO in the example is for?

Remember that the CURSOR is just a select stmt. It can be really complicated with joins, you name it. But in the end it has a select column list. In your case it has 2 columns coming back. So the FETCH, one row at a time, brings the current row into LOCAL VARIABLES (in the respective order from the cursor list to the variables you list). You declared those LOCAL VARIABLES in your DECLAREs.

When you are out of rows, the HANDLER sets done to 1 and you bail out of the loop.

As for the DELIMITER read the last half of this answer of mine Here.

Just generally how the whole procedure is suppose to work and what is it for?

Described above mostly. Cursors are for procedural handling of data returned. Allowing you to inject procedural thinking into solving problems. By the way they are terribly slow and should be avoided whenever possible. They are typically a crutch for devs new to SQL that can't get their head into how to do work with sets and relations. That is, the way high performance RDBMS's excel at.

That said, experienced SQL devs are known to use them for tricky situations.