Create PROCEDURE Kucun(oid int,sid int)
begin
DECLARE s TINYINT;
DECLARE g TINYINT;
DECLARE n TINYINT;
DECLARE a TINYINT;
DECLARE cursorDone INT DEFAULT 0;
select 'status' INTO s from iwebshop_seller_order where 'id'=oid and 'user_id'=sid;
IF s=3 THEN
update iwebshop_seller_order set status ='4',confirm_time=now() where 'id'=oid and 'user_id'=sid;
DECLARE cur CURSOR FOR select 'goods_id','goods_nums' from iwebshop_seller_order_goods where 'order_id'=oid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorDone = 1;
open cur;
FETCH cur INTO g,n;
select count(*) INTO a from iwebshop_seller_kucun where 'seller_id'=sid and 'gid'=g;
IF a>0 THEN
update iwebshop_seller_kucun set num =num+n where gid=g and seller_id=sid;
ELSE
insert into iwebshop_seller_kucun VALUES ('',sid,g,n);
END IF;
CLOSE cur;
END IF;
end;
begin
DECLARE s TINYINT;
DECLARE g TINYINT;
DECLARE n TINYINT;
DECLARE a TINYINT;
DECLARE cursorDone INT DEFAULT 0;
select 'status' INTO s from iwebshop_seller_order where 'id'=oid and 'user_id'=sid;
IF s=3 THEN
update iwebshop_seller_order set status ='4',confirm_time=now() where 'id'=oid and 'user_id'=sid;
DECLARE cur CURSOR FOR select 'goods_id','goods_nums' from iwebshop_seller_order_goods where 'order_id'=oid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorDone = 1;
open cur;
FETCH cur INTO g,n;
select count(*) INTO a from iwebshop_seller_kucun where 'seller_id'=sid and 'gid'=g;
IF a>0 THEN
update iwebshop_seller_kucun set num =num+n where gid=g and seller_id=sid;
ELSE
insert into iwebshop_seller_kucun VALUES ('',sid,g,n);
END IF;
CLOSE cur;
END IF;
end;
结帖率:0%
Create PROCEDURE Kucun(oid int,sid int)
begin
DECLARE s TINYINT;
DECLARE g TINYINT;
DECLARE n TINYINT;
DECLARE a TINYINT;
DECLARE cursorDone INT DEFAULT 0;
select 'status' INTO s from iwebshop_seller_order where 'id'=oid and 'user_id'=sid;
IF s=3 THEN
update iwebshop_seller_order set status ='4',confirm_time=now() where 'id'=oid and 'user_id'=sid;
DECLARE cur CURSOR FOR select 'goods_id','goods_nums' from iwebshop_seller_order_goods where 'order_id'=oid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorDone = 1;
open cur;
FETCH cur INTO g,n;
select count(*) INTO a from iwebshop_seller_kucun where 'seller_id'=sid and 'gid'=g;
IF a>0 THEN
update iwebshop_seller_kucun set num =num+n where gid=g and seller_id=sid;
ELSE
insert into iwebshop_seller_kucun VALUES ('',sid,g,n);
END IF;
CLOSE cur;
END IF;
end;
//
delimiter ;
你有游标少了repeat循环,你用以下方法测测
e.g.
delimiter ||
Create PROCEDURE Kucun(oid int,sid int)
begin
DECLARE s TINYINT;
DECLARE g TINYINT;
DECLARE n TINYINT;
DECLARE a TINYINT;
DECLARE cursorDone INT DEFAULT 0;
DECLARE cur CURSOR FOR select 'goods_id','goods_nums' from iwebshop_seller_order_goods where 'order_id'=oid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorDone = 1;
select 'status' INTO s from iwebshop_seller_order where 'id'=oid and 'user_id'=sid;IF s=3 THEN
update iwebshop_seller_order set status ='4',confirm_time=now() where 'id'=oid and 'user_id'=sid;
open cur;
repeat
FETCH cur INTO g,n;
select count(*) INTO a from iwebshop_seller_kucun where 'seller_id'=sid and 'gid'=g;
IF a>0 THEN
update iwebshop_seller_kucun set num =num+n where gid=g and seller_id=sid;
ELSE
insert into iwebshop_seller_kucun VALUES ('',sid,g,n);
END IF;
until done end repeat;CLOSE cur;END IF;
end;
||
delimiter ;
e.g.
delimiter ||
Create PROCEDURE Kucun(oid int,sid int)
begin
DECLARE s TINYINT;
DECLARE g TINYINT;
DECLARE n TINYINT;
DECLARE a TINYINT;
DECLARE cursorDone INT DEFAULT 0;
DECLARE cur CURSOR FOR select 'goods_id','goods_nums' from iwebshop_seller_order_goods where 'order_id'=oid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorDone = 1;
select 'status' INTO s from iwebshop_seller_order where 'id'=oid and 'user_id'=sid;IF s=3 THEN
update iwebshop_seller_order set status ='4',confirm_time=now() where 'id'=oid and 'user_id'=sid;
open cur;
repeat
FETCH cur INTO g,n;
IF NOT cursorDone THEN
select count(*) INTO a from iwebshop_seller_kucun where 'seller_id'=sid and 'gid'=g;
IF a>0 THEN
update iwebshop_seller_kucun set num =num+n where gid=g and seller_id=sid;
ELSE
insert into iwebshop_seller_kucun VALUES ('',sid,g,n);
END IF;
end if ;
until cursorDone end repeat;CLOSE cur;END IF;
end;
||
delimiter ;