Advertisement
Guest User

Untitled

a guest
Jan 24th, 2020
107
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.15 KB | None | 0 0
  1. CREATE PROCEDURE `check_rented`(now DATETIME)
  2.             BEGIN
  3.                 DECLARE rent_id INT DEFAULT NULL;            
  4.                 DECLARE game_item_id INT DEFAULT NULL;            
  5.                 DECLARE done TINYINT DEFAULT FALSE;
  6.                 DECLARE rented_cursor
  7.                  CURSOR FOR
  8.                  SELECT
  9.                     t1.id,
  10.                     t1.games_item_id
  11.                    FROM front_user_games_item t1
  12.                   WHERE expired_at < now
  13.                   AND expired = 0;
  14.                  
  15.                 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  16.                 OPEN rented_cursor;          
  17.                 my_loop: LOOP
  18.                   FETCH NEXT FROM rented_cursor INTO rent_id, game_item_id;            
  19.                   IF done THEN
  20.                     LEAVE my_loop;
  21.                   ELSE
  22.                     UPDATE front_user_games_item SET expired = 1 WHERE `id` = rent_id;
  23.                     UPDATE games_items SET loaned = 0 WHERE `id` = game_item_id;
  24.                   END IF;
  25.                 END LOOP;          
  26.                 CLOSE rented_cursor;
  27.             END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement