mysql之存储过程（二）

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 platxxxxd (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;