Cursors with parameters

DECLARE cursor_name CURSOR FOR select_statement;

OPEN cursor_name;

DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

OPEN cursor_name [expression[,...]];

cursor_formal_parameter:

    name type [collate clause]

DROP PROCEDURE IF EXISTS p1;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT, b VARCHAR(10));

INSERT INTO t1 VALUES (1,'old'),(2,'old'),(3,'old'),(4,'old'),(5,'old');

DELIMITER $$

CREATE PROCEDURE p1(min INT,max INT)

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE va INT;

  DECLARE cur CURSOR(pmin INT, pmax INT) FOR SELECT a FROM t1 WHERE a BETWEEN pmin AND pmax;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

  OPEN cur(min,max);

  read_loop: LOOP

    FETCH cur INTO va;

    IF done THEN

      LEAVE read_loop;

    END IF;

    INSERT INTO t1 VALUES (va,'new');

  END LOOP;

  CLOSE cur; 

END;

$$

DELIMITER ;

CALL p1(2,4);

SELECT * FROM t1;

+------+------+

| a    | b    |

+------+------+

|    1 | old  |

|    2 | old  |

|    3 | old  |

|    4 | old  |

|    5 | old  |

|    2 | new  |

|    3 | new  |

|    4 | new  |

+------+------+