1、批量根据复杂的SQL查询结果插入到新表
DELIMITER &&
CREATE PROCEDURE settlePADTEST()
begin
DECLARE c_statTime VARCHAR(100);
DECLARE c_uid BIGINT(20);
DECLARE c_price DECIMAL(20,2);
DECLARE cur_award CURSOR for
SELECT sum(IFNULL(aa.`comprice`,0)+ IFNULL(aa.`chanprice`,0)) as price,aa.`distid` as userId,date_format(bb.order_time, ‘%Y-%m-%d %H:%i:%s‘) as statTime FROM `commission` aa left join order bb on aa.order_no = bb.order_id where aa.`commission_type` =2 and aa. yn =1 and bb.order_time >="2019-01-01 00:00:00" and bb.order_time <="2019-01-31 23:59:59" group by aa.`distid`,date_format(bb.order_time, ‘%Y%m%d‘);
OPEN cur_award;
LOOP
FETCH cur_award INTO c_price,c_uid,c_statTime;
INSERT INTO platform_award (aid,piid, award_user_real_name, award_user_mobile, creator_nick, creator_id, modified, yn, award_user_id, award_amount,created) VALUES (1001, 2342,‘‘ , ‘‘, ‘lalala‘,115, now(), 1,c_uid, c_price, c_statTime);
END LOOP;
end&&
delimiter;
2、批量根据复杂的SQL查询结果修改或插入到新表
DELIMITER &&
CREATE PROCEDURE settleTESTV2()
begin
DECLARE c_id BIGINT(20);
DECLARE c_aid BIGINT(20);
DECLARE c_pid BIGINT(20);
DECLARE c_uid BIGINT(20);
DECLARE c_price DECIMAL(20,2);
DECLARE cur_award CURSOR for
SELECT id,aid,pid,user_id,amount FROM `plad` where `act_id`=9 and `created` < ‘2019-11-01 00:00:00‘ and yn =1 and state=3;
OPEN cur_award;
LOOP
FETCH cur_award INTO c_id,c_aid,c_pid,c_uid ,c_price;
INSERT into srd VALUES (null,c_pid,c_uid,c_id,c_price,109,83,CONCAT(‘lalala‘,c_aid),now(),now(),1,1);
set @count=(select count(1) from saat where user_id=c_uid and ynm = 1 and ure=1);
SELECT @count ;
if @count >0 then
update saa set atb= atb + c_price,....................... where user_id=c_uid and yn = 1 and ure=1 ;
else
INSERT INTO saa VALUES (null, c_pid, c_uid, c_price, c_price, 01, 1, NOW(), NOW(), 21, 14);
END IF;
END LOOP;
end&&
delimiter;
mysql之存储过程（二）
原文：https://www.cnblogs.com/xuzhujack/p/11958986.html
