我在java端循环调用mysql的存储过程,有个怪现象,就是执行到某一步的时候 正常是没有记录可操作的,但是就像是程序写死了一样,每次进存储过程里必走那,而且每次只操作那一条记录,循环多少次就对同一条记录操作多少次。
附存储过程:
CREATE DEFINER=`moster`@`%` PROCEDURE `proc_sellinggoods_del_backend`(
in _sellinggoodsid int,
in _reason varchar(200) character set utf8,
out out_rs int
)
begin
declare sellstatus int default 0;
declare seller int default 0;
declare deposit decimal(10,2) default 0;
declare isstock int default 0;
declare quantity int default 0;
declare bargainid int default 0;
declare bargainuser int default 0;
declare bagainprice int default 0;
declare goodsname varchar(100);
declare otherbuyer int default 0;
declare orderamount int default 0;
declare currentorderid int default 0;
declare goodsstatus int default 1;
declare done int default 0;
declare rs_cursor cursor for select ID from `ORDER` where REF_ID = _sellinggoodsid and STATUS = 1 and TRADE_STATUS < 4;
declare continue handler for not found set done = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
set out_rs=0; start transaction;
select SELLING_STATUS, CREATED_BY, FOREGIFT, position("<STOCK>" IN ATTRS), NAME, STATUS into @sellstatus, @seller, @deposit, @isstock, @goodsname,@goodsstatus from SELLING_GOODS where ID=_sellinggoodsid; if(@sellstatus = 0 and @goodsstatus =1) then /*商品售出状态和商品状态是否满足下架*/
if(@isstock > 0) then /*库存*/
/*如果是库存商品,查看未完成的订单*/
open rs_cursor;
repeat
fetch rs_cursor into currentorderid;
if done = 0 then
/*查看该商品的买家和金额*/
select MEMBER_ID,O.AMOUNT into @otherbuyer,@orderamount from MEMBER_INFO M join `ORDER` O where O.BUYER=M.MEMBER_ID and O.ID=currentorderid;
/*把买家的钱解冻*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY - @orderamount where MEMBER_ID=@otherbuyer;
/*记录订单操作*/
insert into ORDER_AUDIT(ORDER_ID, MEMO, OPERATOR, OPERATION_TYPE, OPERATOR_TYPE, CREATED_DATE, STATUS) values(currentorderid, '商品被下架自动关闭交易', 0, 5, 4, now(), 1);
/*修改买家frozen_money_list*/
update FROZEN_MONEY_LIST set STATUS = 0 where MEMBER_ID = @otherbuyer and TYPE = 1 and REF_ID =currentorderid;
/*记录买家资金变动流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@otherbuyer, 12, 0, @orderamount, currentorderid, '商品下架系统关闭交易', now());
/*改变订单状态*/
update `ORDER` set TRADE_STATUS = 5 where ID = currentorderid;
/*给买家发站内*/
insert into MESSAGE(RECEIVER, CONTENT, CREATED_BY, CREATED_BY_INFO, CREATED_DATE) values (@otherbuyer, concat('你好,你购买的',@goodsname,'因为商品下架已被取消交易。现在押金已打回你的账户,请查收'), 10000, '魔游游', now());
end if;
until done end repeat;
close rs_cursor;
end if;
/*判断是否有议价*/
select ID, CREATED_BY, PRICE into @bagainid, @bagainuser, @bagainprice from BARGAIN_HISTORY where SELLING_GOODS_ID=_sellinggoodsid and STATUS=1;
if(@bagainuser > 0 and @bagainprice > 0) then /*有议价*/
/*取消议价*/
update BARGAIN_HISTORY set STATUS=0 where SELLING_GOODS_ID=_sellinggoodsid and STATUS=1;
/*返还议价者押金*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY-@bagainprice where MEMBER_ID=@bagainuser;
/*修改frozen_money_list*/
update FROZEN_MONEY_LIST set STATUS = 0 where MEMBER_ID = @bagainuser and TYPE = 5 and REF_ID = @bagainid;
/*记录议价者资金变动流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@bagainuser, 6, 0, @bagainprice*(-1), @bagainid, '商品下架系统取消议价', now());
end if;
if(@deposit > 0) then /*商品是否有押金*/
/*押金解冻*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY - @deposit, MONEY=MONEY-@deposit where MEMBER_ID=@seller;
/*记录卖家冻结资金流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@seller, 15, 0, @deposit*(-1), _sellinggoodsid, '商品下架,返还押金', now());
/*记录卖家资金流水,违规下架扣钱*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@seller, 22, @deposit*(-1), 0, _sellinggoodsid, concat('发布违规商品[link=gcs.jw?act=detail&id=',_sellinggoodsid,']',@goodsname,'[/link]'), now());
/*给魔游游闪电账号加钱*/
update MEMBER_INFO set MONEY=MONEY+@deposit where MEMBER_ID=13055;
/*记录魔游游资金流水,违规商品下架的钱*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(13055, 21, @deposit, 0, _sellinggoodsid, '违规商品下架,押金打入魔游游账户', now());
end if;
/*修改商品状态*/
update SELLING_GOODS set STATUS = -1, END_DATE=now() where ID=_sellinggoodsid;
/*给卖家发站内*/
insert into MESSAGE(RECEIVER, CONTENT, CREATED_BY, CREATED_BY_INFO, CREATED_DATE) values (@seller, _reason, 10000, '魔游游', now());
set out_rs = 1;
commit;
else
set out_rs = 0;
end if;
end;其中红色的部分是每次调用必走的那部分。
附存储过程:
CREATE DEFINER=`moster`@`%` PROCEDURE `proc_sellinggoods_del_backend`(
in _sellinggoodsid int,
in _reason varchar(200) character set utf8,
out out_rs int
)
begin
declare sellstatus int default 0;
declare seller int default 0;
declare deposit decimal(10,2) default 0;
declare isstock int default 0;
declare quantity int default 0;
declare bargainid int default 0;
declare bargainuser int default 0;
declare bagainprice int default 0;
declare goodsname varchar(100);
declare otherbuyer int default 0;
declare orderamount int default 0;
declare currentorderid int default 0;
declare goodsstatus int default 1;
declare done int default 0;
declare rs_cursor cursor for select ID from `ORDER` where REF_ID = _sellinggoodsid and STATUS = 1 and TRADE_STATUS < 4;
declare continue handler for not found set done = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
set out_rs=0; start transaction;
select SELLING_STATUS, CREATED_BY, FOREGIFT, position("<STOCK>" IN ATTRS), NAME, STATUS into @sellstatus, @seller, @deposit, @isstock, @goodsname,@goodsstatus from SELLING_GOODS where ID=_sellinggoodsid; if(@sellstatus = 0 and @goodsstatus =1) then /*商品售出状态和商品状态是否满足下架*/
if(@isstock > 0) then /*库存*/
/*如果是库存商品,查看未完成的订单*/
open rs_cursor;
repeat
fetch rs_cursor into currentorderid;
if done = 0 then
/*查看该商品的买家和金额*/
select MEMBER_ID,O.AMOUNT into @otherbuyer,@orderamount from MEMBER_INFO M join `ORDER` O where O.BUYER=M.MEMBER_ID and O.ID=currentorderid;
/*把买家的钱解冻*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY - @orderamount where MEMBER_ID=@otherbuyer;
/*记录订单操作*/
insert into ORDER_AUDIT(ORDER_ID, MEMO, OPERATOR, OPERATION_TYPE, OPERATOR_TYPE, CREATED_DATE, STATUS) values(currentorderid, '商品被下架自动关闭交易', 0, 5, 4, now(), 1);
/*修改买家frozen_money_list*/
update FROZEN_MONEY_LIST set STATUS = 0 where MEMBER_ID = @otherbuyer and TYPE = 1 and REF_ID =currentorderid;
/*记录买家资金变动流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@otherbuyer, 12, 0, @orderamount, currentorderid, '商品下架系统关闭交易', now());
/*改变订单状态*/
update `ORDER` set TRADE_STATUS = 5 where ID = currentorderid;
/*给买家发站内*/
insert into MESSAGE(RECEIVER, CONTENT, CREATED_BY, CREATED_BY_INFO, CREATED_DATE) values (@otherbuyer, concat('你好,你购买的',@goodsname,'因为商品下架已被取消交易。现在押金已打回你的账户,请查收'), 10000, '魔游游', now());
end if;
until done end repeat;
close rs_cursor;
end if;
/*判断是否有议价*/
select ID, CREATED_BY, PRICE into @bagainid, @bagainuser, @bagainprice from BARGAIN_HISTORY where SELLING_GOODS_ID=_sellinggoodsid and STATUS=1;
if(@bagainuser > 0 and @bagainprice > 0) then /*有议价*/
/*取消议价*/
update BARGAIN_HISTORY set STATUS=0 where SELLING_GOODS_ID=_sellinggoodsid and STATUS=1;
/*返还议价者押金*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY-@bagainprice where MEMBER_ID=@bagainuser;
/*修改frozen_money_list*/
update FROZEN_MONEY_LIST set STATUS = 0 where MEMBER_ID = @bagainuser and TYPE = 5 and REF_ID = @bagainid;
/*记录议价者资金变动流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@bagainuser, 6, 0, @bagainprice*(-1), @bagainid, '商品下架系统取消议价', now());
end if;
if(@deposit > 0) then /*商品是否有押金*/
/*押金解冻*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY - @deposit, MONEY=MONEY-@deposit where MEMBER_ID=@seller;
/*记录卖家冻结资金流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@seller, 15, 0, @deposit*(-1), _sellinggoodsid, '商品下架,返还押金', now());
/*记录卖家资金流水,违规下架扣钱*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@seller, 22, @deposit*(-1), 0, _sellinggoodsid, concat('发布违规商品[link=gcs.jw?act=detail&id=',_sellinggoodsid,']',@goodsname,'[/link]'), now());
/*给魔游游闪电账号加钱*/
update MEMBER_INFO set MONEY=MONEY+@deposit where MEMBER_ID=13055;
/*记录魔游游资金流水,违规商品下架的钱*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(13055, 21, @deposit, 0, _sellinggoodsid, '违规商品下架,押金打入魔游游账户', now());
end if;
/*修改商品状态*/
update SELLING_GOODS set STATUS = -1, END_DATE=now() where ID=_sellinggoodsid;
/*给卖家发站内*/
insert into MESSAGE(RECEIVER, CONTENT, CREATED_BY, CREATED_BY_INFO, CREATED_DATE) values (@seller, _reason, 10000, '魔游游', now());
set out_rs = 1;
commit;
else
set out_rs = 0;
end if;
end;其中红色的部分是每次调用必走的那部分。
if(@bagainuser > 0 and @bagainprice > 0) then /*有议价*/
/*取消议价*/
update BARGAIN_HISTORY set STATUS=0 where SELLING_GOODS_ID=_sellinggoodsid and STATUS=1;
/*返还议价者押金*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY-@bagainprice where MEMBER_ID=@bagainuser;
/*修改frozen_money_list*/
update FROZEN_MONEY_LIST set STATUS = 0 where MEMBER_ID = @bagainuser and TYPE = 5 and REF_ID = @bagainid;
/*记录议价者资金变动流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@bagainuser, 6, 0, @bagainprice*(-1), @bagainid, '商品下架系统取消议价', now());
end if;
这段必走,而且select ID, CREATED_BY, PRICE into @bagainid, @bagainuser, @bagainprice from BARGAIN_HISTORY where SELLING_GOODS_ID=_sellinggoodsid and STATUS=1;这个sql正常是查不出来值的,但是总能 对某条记录进行上面的那些操作 什么情况?就像是@bagainuser 、 @bagainprice被赋予了一个固定值
SELECT WHERE 后,加上SET DONE=0,否则会影响游标的循环,
将用到的变量SELECT 出来看看其内容 是什么,SELECT 变量名
in _sellinggoodsid int,
in _reason varchar(200) character set utf8,
out out_rs int
)
begin
declare sellstatus int default 0;
declare seller int default 0;
declare deposit decimal(10,2) default 0;
declare isstock int default 0;
declare quantity int default 0;
declare bargainid int default 0;
declare bargainuser int default 0;
declare bagainprice int default 0;
declare goodsname varchar(100);
declare otherbuyer int default 0;
declare orderamount int default 0;
declare currentorderid int default 0;
declare goodsstatus int default 1;
declare done int default 0;
declare rs_cursor cursor for select ID from `ORDER` where REF_ID = _sellinggoodsid and STATUS = 1 and TRADE_STATUS < 4;
declare continue handler for not found set done = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
set out_rs=0; start transaction;
select SELLING_STATUS, CREATED_BY, FOREGIFT, position("<STOCK>" IN ATTRS), NAME, STATUS into @sellstatus, @seller, @deposit, @isstock, @goodsname,@goodsstatus from SELLING_GOODS where ID=_sellinggoodsid; if(@sellstatus = 0 and @goodsstatus =1) then /*商品售出状态和商品状态是否满足下架*/
if(@isstock > 0) then /*库存*/
/*如果是库存商品,查看未完成的订单*/
open rs_cursor;
repeat
fetch rs_cursor into currentorderid;
if done = 0 then
/*查看该商品的买家和金额*/
select MEMBER_ID,O.AMOUNT into @otherbuyer,@orderamount from MEMBER_INFO M join `ORDER` O where O.BUYER=M.MEMBER_ID and O.ID=currentorderid;
/*把买家的钱解冻*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY - @orderamount where MEMBER_ID=@otherbuyer;
/*记录订单操作*/
insert into ORDER_AUDIT(ORDER_ID, MEMO, OPERATOR, OPERATION_TYPE, OPERATOR_TYPE, CREATED_DATE, STATUS) values(currentorderid, '商品被下架自动关闭交易', 0, 5, 4, now(), 1);
/*修改买家frozen_money_list*/
update FROZEN_MONEY_LIST set STATUS = 0 where MEMBER_ID = @otherbuyer and TYPE = 1 and REF_ID =currentorderid;
/*记录买家资金变动流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@otherbuyer, 12, 0, @orderamount, currentorderid, '商品下架系统关闭交易', now());
/*改变订单状态*/
update `ORDER` set TRADE_STATUS = 5 where ID = currentorderid;
/*给买家发站内*/
insert into MESSAGE(RECEIVER, CONTENT, CREATED_BY, CREATED_BY_INFO, CREATED_DATE) values (@otherbuyer, concat('你好,你购买的',@goodsname,'因为商品下架已被取消交易。现在押金已打回你的账户,请查收'), 10000, '魔游游', now());
set done = 0 ;
end if;
until done end repeat;
close rs_cursor;
end if;
/*判断是否有议价*/
select ID, CREATED_BY, PRICE into @bagainid, @bagainuser, @bagainprice from BARGAIN_HISTORY where SELLING_GOODS_ID=_sellinggoodsid and STATUS=1;
if(@bagainuser > 0 and @bagainprice > 0) then /*有议价*/
/*取消议价*/
update BARGAIN_HISTORY set STATUS=0 where SELLING_GOODS_ID=_sellinggoodsid and STATUS=1;
/*返还议价者押金*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY-@bagainprice where MEMBER_ID=@bagainuser;
/*修改frozen_money_list*/
update FROZEN_MONEY_LIST set STATUS = 0 where MEMBER_ID = @bagainuser and TYPE = 5 and REF_ID = @bagainid;
/*记录议价者资金变动流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@bagainuser, 6, 0, @bagainprice*(-1), @bagainid, '商品下架系统取消议价', now());
end if; if(@deposit > 0) then /*商品是否有押金*/
/*押金解冻*/
update MEMBER_INFO set FROZEN_MONEY=FROZEN_MONEY - @deposit, MONEY=MONEY-@deposit where MEMBER_ID=@seller;
/*记录卖家冻结资金流水*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@seller, 15, 0, @deposit*(-1), _sellinggoodsid, '商品下架,返还押金', now());
/*记录卖家资金流水,违规下架扣钱*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(@seller, 22, @deposit*(-1), 0, _sellinggoodsid, concat('发布违规商品[link=gcs.jw?act=detail&id=',_sellinggoodsid,']',@goodsname,'[/link]'), now());
/*给魔游游闪电账号加钱*/
update MEMBER_INFO set MONEY=MONEY+@deposit where MEMBER_ID=13055;
/*记录魔游游资金流水,违规商品下架的钱*/
insert into MEMBER_ACCOUNT_AUDIT(MEMBER_ID, PURPOSE, MONEY, FROZEN_MONEY, REF_ID, MEMO, CREATED_DATE) values(13055, 21, @deposit, 0, _sellinggoodsid, '违规商品下架,押金打入魔游游账户', now());
end if;
/*修改商品状态*/
update SELLING_GOODS set STATUS = -1, END_DATE=now() where ID=_sellinggoodsid;
/*给卖家发站内*/
insert into MESSAGE(RECEIVER, CONTENT, CREATED_BY, CREATED_BY_INFO, CREATED_DATE) values (@seller, _reason, 10000, '魔游游', now());
set out_rs = 1;
commit;
else
set out_rs = 0;
end if;
end;
主要是检查变量中的内容是否正确