MySQL 存储过程示例

DROP PROCEDURE IF EXISTS order_summary_month;
DELIMITER //
CREATE PROCEDURE order_summary_month()
BEGIN
  DECLARE m datetime DEFAULT '2019-01-01';
  WHILE ( DATE_FORMAT(m,'%Y-%m') <= DATE_FORMAT(NOW(),'%Y-%m') )
    DO
    IF NOT EXISTS
      (
        SELECT insertdate
        FROM app.order_summary
        WHERE statdate=DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1'
      )
      THEN
        INSERT INTO app.order_summary
          (
            statdate,
            time_unit,
            type,
            cnt_order,
            sum_price,
            cnt_weight,
            insertdate
          )
        VALUES
          (
            DATE_FORMAT(m,'%Y-%m'),
            3,
            1,
            ( 
              SELECT COUNT(order_sn) AS cnt_order
              FROM app.wx_order
              WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                    AND type='1'
            ),
            ( 
              SELECT SUM(price) AS sum_price
              FROM app.wx_order
              WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                    AND type='1'
                    AND gains_type='1'
            ),
            ( 
              SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight
              FROM app.wx_order O
              LEFT JOIN app.wx_order_goods G on O.id=G.order_id
              LEFT JOIN app.wx_goods_class C on G.goods_id=C.id
              WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                    AND O.type='1'
            ),
            NOW()
          );
-- /*      --不更新旧数据则可以跳过
        ELSE
          UPDATE app.wx_order_summary
            SET
              time_unit = 3,
              type = 1,
              cnt_order =
              ( 
                SELECT COUNT(order_sn) AS cnt_order
                FROM app.wx_order
                WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                      AND type='1'
              ),
              sum_price =
              ( 
                SELECT SUM(price) AS sum_price
                FROM app.wx_order
                WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                      AND type='1'
                      AND gains_type='1'
              ),
              cnt_weight =
              ( 
                SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight
                FROM app.wx_order O
                LEFT JOIN app.wx_order_goods G on O.id=G.order_id
                LEFT JOIN app.wx_goods_class C on G.goods_id=C.id
                WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                      AND O.type='1'
              ),
              insertdate = NOW()
            WHERE statdate = DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1';
-- */      -- 不更新旧数据则可以跳过
    END IF;
    COMMIT;
    SET m = DATE_SUB(m, INTERVAL -1 MONTH);
  END WHILE;
END; //
DELIMITER ;
CALL order_summary_month();

DROP PROCEDURE IF EXISTS order_summary_month;

DELIMITER //

CREATE PROCEDURE order_summary_month()

BEGIN

DECLARE m datetime DEFAULT '2019-01-01';

WHILE ( DATE_FORMAT(m,'%Y-%m') <= DATE_FORMAT(NOW(),'%Y-%m') )

IF NOT EXISTS

(

SELECT insertdate

FROM app.order_summary

WHERE statdate=DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1'

)

THEN

INSERT INTO app.order_summary

(

statdate,

time_unit,

type,

cnt_order,

sum_price,

cnt_weight,

insertdate

)

VALUES

(

DATE_FORMAT(m,'%Y-%m'),

(

SELECT COUNT(order_sn) AS cnt_order

FROM app.wx_order

WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

(

SELECT SUM(price) AS sum_price

FROM app.wx_order

WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

AND gains_type='1'

(

SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight

FROM app.wx_order O

LEFT JOIN app.wx_order_goods G on O.id=G.order_id

LEFT JOIN app.wx_goods_class C on G.goods_id=C.id

WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND O.type='1'

NOW()

);

-- /* --不更新旧数据则可以跳过

ELSE

UPDATE app.wx_order_summary

SET

time_unit = 3,

type = 1,

cnt_order =

(

SELECT COUNT(order_sn) AS cnt_order

FROM app.wx_order

WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

sum_price =

(

SELECT SUM(price) AS sum_price

FROM app.wx_order

WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

AND gains_type='1'

cnt_weight =

(

SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight

FROM app.wx_order O

LEFT JOIN app.wx_order_goods G on O.id=G.order_id

LEFT JOIN app.wx_goods_class C on G.goods_id=C.id

WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND O.type='1'

insertdate = NOW()

WHERE statdate = DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1';

-- */ -- 不更新旧数据则可以跳过

END IF;

COMMIT;

SET m = DATE_SUB(m, INTERVAL -1 MONTH);

END WHILE;

END; //

DELIMITER ;

CALL order_summary_month();

文章出自: 本站技术文章均为原创，版权归 "Desen往事 - 个人博客" 所有；部分图片来源于 Yandex ，转载本站文章请注明来源。

本文标题：MySQL 存储过程示例

本文链接：https://www.desenpast.com/databases/mysql-5283.html

Desen往事

简单生活，知足常乐

MySQL 存储过程示例

发表评论取消回复

相关文章

发表评论 取消回复

发表评论取消回复