Форум пользователей MySQL

DELIMITER //
CREATE
    DEFINER = 'maksim'@'1.1.1.1'
EVENT delete_premium_for_time
    ON SCHEDULE EVERY '30' MINUTE
    STARTS '2011-08-19 15:06:18'
    ON COMPLETION PRESERVE
    DO
BEGIN
  CALL delete_premium_for_time('table1');
  CALL delete_premium_for_time('table2');
END //
DELIMITER ;

BEGIN
  CALL delete_premium_for_time('table1');
  UPDATE table1 SET value=2 WHERE id=3;
  CALL delete_premium_for_time('table2');
END
 

CREATE DEFINER = 'user_name'@'1.1.1.1'
PROCEDURE delete_premium_for_time(IN t_name VARCHAR(255))
BEGIN
  SET @stmt = concat("UPDATE ", t_name," AS o
  LEFT JOIN city AS c
  ON (c.id = o.city_id)
  SET
    o.ispremium = 0
  WHERE
    o.ispremium IN (1, 2)
    AND c.premium_visible = 1
    AND o.pay_date <= current_timestamp - c.premium_live_time * 86400;");
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  BEGIN
    DROP VIEW IF EXISTS delete_premium_for_time;
    SET @stmt_text = concat("CREATE VIEW delete_premium_for_time AS SELECT c.id
         , o.re_type
         , c.offer_premium
         , count(if(o.ispremium = 1, 1, NULL)) AS count_prem
         , count(if(o.ispremium = 2, 1, NULL)) AS count_prem_out
    FROM
      ", t_name, " AS o
    LEFT JOIN city AS c
    ON (c.id = o.city_id)
    WHERE
      c.premium_visible = 1
      AND o.ispremium IN (1, 2)
    GROUP BY
      o.city_id
    , o.re_type");
    PREPARE stmt FROM @stmt_text;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END;

  BEGIN
    DECLARE lim            INT DEFAULT 0;
    DECLARE done           INT DEFAULT 0;
    DECLARE id             INT DEFAULT 0;
    DECLARE re_type        INT DEFAULT 0;
    DECLARE offer_premium  INT DEFAULT 0;
    DECLARE count_prem     INT DEFAULT 0;
    DECLARE count_prem_out INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR SELECT *
                            FROM
                              delete_premium_for_time;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    OPEN cur1;
    WHILE done = 0
    DO
      FETCH cur1 INTO id, re_type, offer_premium, count_prem, count_prem_out;
      IF (count_prem < offer_premium) AND (count_prem_out > 0) THEN
        SET lim = offer_premium - count_prem;
        SET @sql = concat('UPDATE ', t_name, ' SET ispremium = 1 WHERE ispremium = 2 and re_type = ', re_type, ' and city_id = ', id, ' ORDER BY pay_date DESC LIMIT ', lim);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
      END IF;
    END WHILE;
    CLOSE cur1;
  END;
  DROP VIEW delete_premium_for_time;
END

count(if(o.ispremium = 1, 1, NULL)) AS count_prem

CONVERT(count(if(o.ispremium = 1, 1, NULL)),UNSIGNED) AS count_prem