CODEWITHSUNDEEP
mysqlstored-procedures
smoczyna
smoczyna

Reputation: 519

MySQL procedure doesn't work properly

I'm trying to write the procedure iterating over all table in the schema to make further change to all of them. however when I call it it returns this message and I can't figure out why:

Error Code: 1329. No data - zero rows fetched, selected, or processed

her is the procedure code itself:

delimiter #
drop procedure if exists myschema.create_triggers #
create procedure myschema.create_triggers()
begin
  declare tname CHAR(255);
  declare result VARCHAR(255);

  declare cTab cursor for select table_name from information_schema.tables where table_schema = 'myschema';
  open cTab;
  tLoop: LOOP
    fetch cTab into tname;
    SET result = CONCAT(result, tname, ',');    
  end LOOP tLoop;

  select result;
end #

cursor query works fine so why this procedure doesn't do what's expected?

Upvotes: 0

Views: 328

Answers (1)

fancyPants
fancyPants

Reputation: 51868

You're missing a CONTINUE HANDLER. The continue handler tells MySQL what to do, when the cursor has processed all rows.

Examples can be found here.

Quote:

Cursor declarations must appear before handler declarations and after variable and condition declarations.

Example:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

More information about handlers in general can be found here.

Upvotes: 1

Related Questions