mysql存储过程执行拼装SQL

最新推荐文章于 2022-09-30 15:52:41 发布

lovemelovemycode

最新推荐文章于 2022-09-30 15:52:41 发布

阅读量1.5k

点赞数

分类专栏：存储过程 mysql 拼装SQL 文章标签： mysql 存储 date insert sql class

本文链接：https://blog.csdn.net/hi_1234567/article/details/7022845

版权

mysql 同时被 3 个专栏收录

12 篇文章 0 订阅

订阅专栏

存储过程

1 篇文章 0 订阅

订阅专栏

拼装SQL

1 篇文章 0 订阅

订阅专栏

CREATE PROCEDURE unsky_resource_procedure(IN coopnum INT)
BEGIN
SET @coopNu = coopnum;
SET @yesterday = DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL -1 DAY),'%Y_%m_%d');
SET @ddd = CONCAT('INSERT INTO unsky_t_resource(client_type,res_id,res_name,res_catalog,res_fee,order_fee,order_uv,order_volumes,order_avg_fee,order_avg_vol,created_date)
SELECT \'A\',d.book_id,r.name AS rn, r.content_class, d.fee_id, SUM(d.fee_id), COUNT(DISTINCT d.mobile_number) AS uv, COUNT(d.cmread_chapter_id) AS vols,FORMAT(SUM(d.fee_id)/COUNT(DISTINCT d.mobile_number),2),FORMAT(COUNT(d.cmread_chapter_id)/COUNT(DISTINCT d.mobile_number),2),NOW()FROM day_ordered_',@yesterday,' d, resource r WHERE d.cmread_content_id = r.cmread_content_id AND d.fee_req_msg = 0 AND d.coopnum = ? GROUP BY d.cmread_content_id');
PREPARE ttt FROM @ddd;
EXECUTE ttt USING @coopNu;

END

注解：执行拼装SQL的时候需要Prepare 和Execute这两个命令，平常的DML是不行的。