CODEWITHSUNDEEP
mysqlstored-procedures
Jarab
Jarab

Reputation: 127

My procedure with cursor doesnt work

I have the next procedure:

BEGIN
  DECLARE retribAn INTEGER DEFAULT 0; 
  DECLARE cPost INTEGER(11);     
  DECLARE done INT DEFAULT 0;
  DECLARE curTipo CURSOR FOR
          SELECT RETRIBUCION_ANUAL*1.05 AS RET_AN
          FROM EMPLEADOS  
          WHERE ID_CPOSTAL%2=0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  OPEN curTipo;
  REPEAT
    FETCH curTipo INTO retribAn, cPost;
    IF NOT done THEN       
      UPDATE EMPLEADOS 
      SET RETRIBUCION_ANUAL=retribAn  
      WHERE ID_CPOSTAL%cPost;  
    END IF;
  UNTIL done END REPEAT;
  CLOSE curTipo;    
END

when I execute it, sql manager outputs:

Incorrect number of FETCH variables

I do not understand why.

I'm a rookie =D in mysql, and sorry for my English.

Thanks

Upvotes: 5

Views: 8345

Answers (1)

rs.
rs.

Reputation: 27427

you are selecting only one column in your cursor query and fetching data into two variables in FETCH

Try this

BEGIN
  DECLARE retribAn INTEGER DEFAULT 0; 
  DECLARE cPost INTEGER(11);     
  DECLARE done INT DEFAULT 0;
  DECLARE curTipo CURSOR FOR
          SELECT RETRIBUCION_ANUAL*1.05 AS RET_AN
          FROM EMPLEADOS  
          WHERE ID_CPOSTAL%2=0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  OPEN curTipo;
  REPEAT
    FETCH curTipo INTO retribAn; --remove cPost here
    IF NOT done THEN       
      UPDATE EMPLEADOS 
      SET RETRIBUCION_ANUAL=retribAn  
      WHERE ID_CPOSTAL%cPost = 0;  --change this to value you want to check
    END IF;
  UNTIL done END REPEAT;
  CLOSE curTipo;    
END

Upvotes: 6

Related Questions