mysql 存儲過程 更新-關於mysql存儲過程的更新語句問題

關於mysql存儲過程的更新語句問題

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

以上是我的存儲過程，在"更新積分信息表"位置出現問題：條件不好使，全量更新表，而不是符合條件的記錄。存儲過程能執行。

請各位大神幫小弟看看 是不是更新語句有問題？還是其他問題？跪謝

最佳回答：

  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;