Posted By

java2025 on 03/02/09


Tagged

mysql


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

virendrakryadav


Mysql cursor


 / Published in: MySQL
 

  1. DELIMITER |
  2.  
  3. DROP PROCEDURE IF EXISTS test |
  4. CREATE PROCEDURE test()
  5. BEGIN
  6. DECLARE DONE INT DEFAULT 0;
  7. DECLARE vHASH_KEY BIGINT(20);
  8.  
  9. DECLARE CUR CURSOR FOR SELECT hash_key FROM tmp;
  10.  
  11. -- 最後までフェッチしたときに、DONEを1にセットする
  12. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1;
  13.  
  14. OPEN CUR;
  15. REPEAT
  16. FETCH CUR INTO vHASH_KEY;
  17. IF NOT DONE THEN
  18. UPDATE movieinfo SET deleted=1,last_deleted_chk=NOW() WHERE deleted=0 AND hash_key=vHASH_KEY;
  19. END IF;
  20. UNTIL DONE END REPEAT;
  21. CLOSE CUR;
  22. END;
  23. |
  24.  
  25. DELIMITER ;

Report this snippet  

You need to login to post a comment.