PROCEDURE 存储过程

最新推荐文章于 2023-12-21 16:24:41 发布

醉面韦陀

最新推荐文章于 2023-12-21 16:24:41 发布

阅读量101

点赞数

分类专栏：心得体会 mysql

本文链接：https://blog.csdn.net/sundful/article/details/84471512

版权

心得体会 mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

DELIMITER $$

USE `paymgr` $$

DROP PROCEDURE IF EXISTS `proc_everday` $$

CREATE DEFINER = `root` @`%` PROCEDURE `proc_everday` () 
BEGIN
  DECLARE suffix CHAR(6) DEFAULT DATE_FORMAT(
    DATE_SUB(NOW(), INTERVAL 1 DAY),
    '%Y%m'
  ) ;
  DECLARE statdate DATE ;
  DECLARE tbl_name VARCHAR (50) ;
  DELETE 
  FROM
    `ccg_charge_day` 
  WHERE TO_DAYS(NOW()) - TO_DAYS(`stat_date`) = 1 ;
  SET tbl_name = CONCAT('ccg_charge_', suffix) ;
  SET statdate = DATE_FORMAT(
    DATE_SUB(NOW(), INTERVAL 1 DAY),
    '%Y-%m-%d'
  ) ;
  SET @stmt := CONCAT(
    "insert into `ccg_charge_day` (`total_count`, `success_count`, `fail_count`, `total_money`, `success_money`, `fail_money`, `stat_date`) SELECT c.total_count,cc.success_count,ccc.fail_count,c.total_money ,cc.success_money,ccc.fail_money,c.stat_date FROM (SELECT DATE_FORMAT(`updatetime`, '%Y-%m-%d') AS 'stat_date', COUNT(`id`) AS 'total_count',SUM(`amount`) AS 'total_money' FROM ",
    tbl_name,
    " WHERE DATE_FORMAT(`updatetime`, '%Y-%m-%d')='",
    statdate,
    "') c,(SELECT COUNT(`id`) AS 'success_count',SUM(`amount`) AS 'success_money' FROM ",
    tbl_name,
    " WHERE `status`=0 AND DATE_FORMAT(`updatetime`, '%Y-%m-%d')='",
    statdate,
    "') cc,(SELECT COUNT(`id`) AS 'fail_count',SUM(`amount`) AS 'fail_money' FROM ",
    tbl_name,
    " WHERE `status`<>0 AND DATE_FORMAT(`updatetime`, '%Y-%m-%d')='",
    statdate,
    "') ccc;"
  ) ;
  PREPARE stmt FROM @stmt ;
  EXECUTE stmt ;
  DEALLOCATE PREPARE stmt ;
END $$

DELIMITER ;