关于mysql存储过程的更新语句问题

Question

BEGIN
/*
accid --普通账户id- '58b0495500ec58092'
money!--充值金额- '10'
cardid--卡id- '000005'
ad_seri--序列号- '43b1515500031af023'
ad_seris--序列号- null
u_id--用户id- 'userId2'
idg--用户活动关系表id null
accids --活动账户id null
integ--赠送积分! null
pre_num!--赠送多少钱 null
trans_code--交易码 '000015'
cmp_id--商户id '001'
inter_sert--接口流水号 'JKLSH'
act_id -- 活动id null
adcsub--借方id '823'
addsub--贷方id '999'
userId--柜员号
out
'466d6d55009aa0041','10','100121','e3d28355001d8d00','e3d28355001d8d01','王五',null,null,'1.000','0','000011','001','',null,'823','999','test05'
*/

declare accbla decimal;
declare accblaf decimal;
declare accod decimal;
declare money decimal(15,3);
declare integ decimal(15,3) default 0;
declare pre_num decimal(15,3) default 0;
declare schid varchar(20);
declare schna varchar(100);
declare abC varchar(6);#卡类型ab账
declare abA varchar(6);#账户类型ab账
declare ab varchar(6);#新的ab账
#declare result varchar(1) default '0';#只用于测试

#如果出现sql异常，则将t_error设置为1后继续执行后面的操作
declare continue handler for sqlexception set result="1";
#设置事务顺序执行
#SET transaction isolation level SERIALIZABLE;
#手动启动事务，即不自动commit
START TRANSACTION;

set money = CAST(moneyS AS decimal(15,3));

select QZT into abC from sdw_ref_usr_card sruc,sdw_bus_cardtype sbc where CARD_ID=cardId and sruc.CARD_TYPE=sbc.CARD_TYPE;

select sss.SCH_ID,sss.SCH_NA into schid,schna from sdw_ref_sch_cmp srsc,sdw_sys_schinfo sss where srsc.CMP_ID=cmp_id and sss.SCH_ID=srsc.SCH_ID;

#select ACC_BLA,ACC_OD into accbla,accod from sdw_bus_account where ACC_ID=accid;
select a.ACC_BLA,a.ACC_OD,b.SPEC into accbla,accod,abA from sdw_bus_account a,sdw_bus_acctype b where a.ACC_TYPE = b.ACC_TYPE and a.ACC_ID=accid;

#更新普通账户余额
update sdw_bus_account set ACC_BLA=ACC_BLA+money,REC_NUM=REC_NUM+1 where ACC_ID=accid;
#判断透支停卡用户的余额
if accod+accbla+money>0 then
#更新所有卡状态为启用
update sdw_ref_usr_card set U_ST='sys01' where U_ID=u_id and U_ST='sys06';
end if;

if integS is not null then
set integ = CAST(integS AS decimal(15,3));
select integ;
#更新积分信息表
update sdw_bus_inteinfo set INTE=INTE+integ,TOTAL_TOPUP=TOTAL_TOPUP+money,TOPUP=TOPUP+integ where U_ID=u_id;
select result;
end if;

#确定ab账
if abA='qztb' or abC='qztb' THEN
    set ab = 'qztb';
else 
    set ab = 'qzta';
end if;

#临时流水表

insert into sdw_tmp_sch_traninfo
(ACC_ID,CARD_ID,U_STAT,DJ_MARK,B_ANCT,AD_ANCT,E_ANCT,SA_ACCTP,AD_BUSINESS,AD_ACBK,
AD_TELL,AD_AUTHN,AD_SERI,AD_ST,TRANS_CODE,TRTP_ID,AD_DATE,AD_ZD,AD_CSUB,AD_CMON,
AD_DSUB,AD_DMON,P_ACC,AD_POST,AD_REC,AD_WRITROFF,KVAL,SE_NOTE,GOU_STATE,INTER_SERI,
SCH_ID,SCH_NA,MCT_ID,MC_ID,PRO_ID,NUM,UNIT_P,AB,DJJ,DJJ_ID,INTE,SZ)
values(accid,cardid,'ust01','decr01',accbla,money,accbla+money,'acct01',cmp_id,cmp_id,
userId,null,ad_seri,'trst02',trans_code,'trt01',NOW(),null,adcsub,money,
addsub,money,null,'sys00','sys00','sys00',null,null,'ust02',inter_sert,
schid,schna,null,null,null,null,null,ab,null,null,integ,FLOOR(RAND()*10));

if accids is not null then
if pre_numS is not null and pre_numS <> '0' then
set pre_num = CAST(pre_numS AS decimal(15,3));
#select ACC_BLA into accblaf from sdw_bus_account where ACC_ID=accids;
select a.ACC_BLA,b.SPEC into accblaf,abA from sdw_bus_account a,sdw_bus_acctype b where a.ACC_TYPE = b.ACC_TYPE and a.ACC_ID=accids;
#更新活动送账户余额
update sdw_bus_account set ACC_BLA=ACC_BLA+pre_num,REC_NUM=REC_NUM+1 where ACC_ID=accids;

            #确定ab账
            if abA='qztb' or abC='qztb' THEN
                set ab = 'qztb';
            else 
                set ab = 'qzta';
            end if;

    #临时流水表
    insert into sdw_tmp_sch_traninfo 
        (ACC_ID,CARD_ID,U_STAT,DJ_MARK,B_ANCT,AD_ANCT,E_ANCT,SA_ACCTP,AD_BUSINESS,AD_ACBK,AD_TELL,AD_AUTHN,AD_SERI,AD_ST,TRANS_CODE,TRTP_ID,AD_DATE,AD_ZD,AD_CSUB,
        AD_CMON,AD_DSUB,AD_DMON,P_ACC,AD_POST,AD_REC,AD_WRITROFF,KVAL,SE_NOTE,GOU_STATE,INTER_SERI,SCH_ID,SCH_NA,MCT_ID,MC_ID,PRO_ID,NUM,UNIT_P,AB,DJJ,DJJ_ID,INTE,SZ)
            values(accids,cardid,'ust01','decr01',accblaf,pre_num,accblaf+pre_num,'acct02',cmp_id,cmp_id,
                    userId,null,ad_seris,'trst02',trans_code,'trt01',NOW(),null,adcsub,pre_num,
                    addsub,pre_num,null,'sys00','sys00','sys00',null,null,'ust02',inter_sert,
                    schid,schna,null,null,null,null,null,ab,null,null,null,FLOOR(RAND()*10)); 
end if;

end if;

if act_id is not null then
insert into sdw_ref_u_act(ACT_ID,U_ID,S_TIME,IDGEN) values(act_id,u_id,now(),idg);

    update sdw_bus_act set J_NUM=J_NUM+1 where ACT_ID=act_id;

end if;

if result <> "0" then

  set result = "1";
ROLLBACK;
ELSE

set result='0';
COMMIT;
END IF;

END

以上是我的存储过程，在"更新积分信息表"位置出现问题：条件不好使，全量更新表，而不是符合条件的记录。存储过程能执行。

请各位大神帮小弟看看 是不是更新语句有问题？还是其他问题？跪谢

Answer 1

  update sdw_bus_inteinfo set INTE=INTE+integ,TOTAL_TOPUP=TOTAL_TOPUP+money,TOPUP=TOPUP+integ where U_ID=u_id;
 你u_id是变量，需要动态SQL更新，不然这语句执行的之后u_id认为是U_ID字段了

 declare my_sqll varchar(500); 
  set my_sqll=CONCAT('update sdw_bus_inteinfo set INTE=INTE+integ,TOTAL_TOPUP=TOTAL_TOPUP+money,TOPUP=TOPUP+integ where U_ID=''',u_id,''''); 
 set @ms=my_sqll; 
 PREPARE s1 from @ms; 
 EXECUTE s1; 
 deallocate prepare s1;