Mysql cursor

DELIMITER |
 
DROP PROCEDURE IF EXISTS test |
CREATE PROCEDURE test()
BEGIN
   DECLARE DONE INT DEFAULT 0;
   DECLARE vHASH_KEY          BIGINT(20);
 
   DECLARE CUR CURSOR FOR SELECT hash_key FROM tmp;
 
   -- 最後までフェッチしたときに、DONEを1にセットする
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1;
 
   OPEN CUR;
   REPEAT
      FETCH CUR INTO vHASH_KEY;
        IF NOT DONE THEN
         UPDATE movieinfo SET deleted=1,last_deleted_chk=NOW() WHERE deleted=0 AND hash_key=vHASH_KEY;
      END IF;
   UNTIL DONE END REPEAT;
   CLOSE CUR;
 END;
|
 
DELIMITER ;