mysql之存储过程（三）

带参数的存储过程:

特别说明： 在游标中是不支持对形参的判断的，外部可以

查看所有的存储过程：

show procedure status;

查看某个存储过程的创建schema：

show create procedure yyyyy_zzzz.settlexxxxx_common;

调用操作：

call settlexxxxx_common("1970-11",999);

定义如下：

 

DELIMITER&&

CREATE PROCEDURE settlexxxxx_common(IN dateStr varchar(50), IN actId bigint)

Begin

    DECLARE c_id BIGINT(20);

    DECLARE c_act_id BIGINT(20);

    DECLARE c_platform_id BIGINT(20);

    DECLARE c_award_user_id BIGINT(20);

    DECLARE c_award_amount DECIMAL(20,2);

    DECLARE cur_award CURSOR for

      SELECT id,act_id,platform_id,award_user_id,award_amount FROM `platform_xxxxx` where `act_id`=actId and date_format(created, '%Y-%m')  =dateStr and 。。。。。。。。。。。。。。;

   

    OPEN cur_award;

  LOOP

   FETCH cur_award INTO c_id,c_act_id,c_platform_id,c_award_user_id ,c_award_amount;

    INSERT into settlement_xxxxxxx  VALUES (null,c_platform_id,c_award_user_id,c_id,c_award_amount,10,3,CONCAT('啦啦啦啦',c_act_id),now(),now(),1,1);

    set @count=(select count(1) from settlement_xxxx_yyyyyy where user_id=c_award_user_id and yn = 1 and user_source=1);

    SELECT @count ;

    if @count >0 then

      update  settlement_xxxxx_yyyyyy set  account_total_balance= account_total_balance + c_award_amount, account_available_balance = account_available_balance + c_award_amount,modified =NOW()   where  user_id=c_award_user_id and yn = 1 and user_source=1 ;

    else

      INSERT INTO settlement_xxxx_yyyyyy VALUES (null, c_platform_id, c_award_user_id, c_award_amount, c_award_amount, 0, 1, NOW(), NOW(), 1, 1);

    END IF;

  END LOOP;

end&&

delimiter;