I cannot declare a cursor in Mysql

Question

I have to do a survey of how cursors work to present in college, in all videos I see on youtube people declare this way :

declare cur _nome cursor for select a.* from computador as a;

but for some reason my Workbench won’t let me use it that way, and I can’t find on the Internet which is the right way, everyone uses it that way !!!

Answer 1

Good afternoon. An example of the creation of a PROCEDURE with CURSOR, basically a cake recipe. Do not forget to always pay attention to the change of delimiter before the creation of any TRIGGER, PROCEDURE or FUNCTION.

DROP PROCEDURE IF EXISTS pr_exemplo_cursor;

DELIMITER $

CREATE PROCEDURE pr_exemplo_cursor()
BEGIN

  DECLARE Id INT;
  DECLARE Name VARCHAR(80);
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur1 CURSOR FOR SELECT Id, Name FROM users;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;

    read_loop: LOOP
            IF done THEN

                LEAVE read_loop;

            ELSE

                    SELECT 'DENTRO DO LAÇO DE REPETIÇÃO';

            END IF;

            FETCH cur1 INTO Id, Name;
    END LOOP;
  close cur1;


END$

I recommend you take a look at this course of Mysql, it covers very well most topics.

Answer 2

Your mistake is already warning you why, you can’t use one declare without a begin before. Example:

begin
  DECLARE teste CHAR(16);

--Updating--

Cursors can only be used if they are inside a Procedure, Function or Trigger. Since you are not declaring any of these types the error occurs. Example of how it should be:

CREATE PROCEDURE procedureTeste()
BEGIN
  declare cur _nome cursor for select a.* from computador as a;
END