有三张表
create table `zqbwactiveinfo` (
`activeid` int(11) not null auto_increment,
`begintime` datetime,
`endtime` datetime,
`bz` varchar(200) default null,
primary key(`activeid`)
) engine=myisam default charset=utf8;
create table `zqbwgiftinfo` (
`giftsn` bigint(20) not null auto_increment,
`giftid` int(11),
`beginmoney` int(11),
`endmoney` int(11),
`giftmoney` int(11),
`addtime` datetime,
primary key(`giftsn`)
) engine=myisam default charset=utf8;create table `zqbwgoodsinfo` (
`goodsn` bigint(20) not null auto_increment,
`id` int(11),
`goodsgift` smallint,
`goodsnum` smallint,
`addtime` datetime,
primary key(`goodsn`)
) engine=myisam default charset=utf8;建立存储过程
delimiter $$
create procedure `zqbw_pay_v1` (
in p_Money int,
in p_LoginName varchar(32)
)
begin
declare act_id int;
declare gift_id int;
declare bmoney int;
declare emoney int;
declare gmoney int;
declare gid int;
declare ggift int;
declare record_Num INT;
declare item_Num INT;
DECLARE user_ID INT;
declare done int default 0;
declare done2 int default 0;
if (p_Money > 0) then
update alluser set Money=ifnull(money,0)+p_Money where LoginName = p_LoginName;
end if;
insert into rechargerecord
( SeverName,
LoginName,
Money,
Gift,
Times)
values
( p_SeverName,
p_LoginName,
p_Money,
0,
sysdate());
select activeid into act_id from zqbwactiveinfo order by activeid desc limit 1;
BEGIN
declare cur1 cursor for select giftid from zqbwgiftinfo where giftid=act_id;
declare continue handler for not found set done=1;
open cur1;
REPEAT
fetch cur1 into gift_id;
BEGIN
select beginmoney,endmoney,giftmoney into bmoney,emoney,gmoney from zqbwgiftinfo where giftid=gift_id;
if p_Money>=bmoney and p_Money<emoney then
update alluser set GIFTCERTIFICATE=GIFTCERTIFICATE+gmoney where LoginName = p_LoginName;
end if;
UNTIL done=1
END REPEAT;
close cur1;
END;BEGIN
delcare cur2 cursor for select id from zqbwgoodsinfo where id=act_id;
declare continue handler for not found set done2=1;
open cur2;
fetch cur2 into gid;
select goodsgift into ggift from zqbwgoodsinfo where id=gid;
SELECT USERID INTO user_ID FROM ALLUSER WHERE LOGINNAME=p_LoginName;
SELECT COUNT(*) INTO record_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
IF record_Num=0 THEN
INSERT INTO PLAYERBAGGOODSINFO (USERID, GOODSID, NUM, GIFTCERTIFICATEBUYNUM) VALUES(user_ID, ggift, FLOOR(p_Money/500), 0);
ELSE
SELECT NUM INTO item_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
UPDATE PLAYERBAGGOODSINFO SET NUM=item_Num+FLOOR(p_Money/500) WHERE USERID=user_ID AND GOODSID=ggift;
END IF;
UNTIL done2=1;
END REPEAT;
close cur2;
END;
END $$
delimiter ;
简单介绍下存储过程的目的,如果你充值p_Money,然后根据最后的活动ID取zqbwactiveinfo的ID,然后匹配zqbwgiftinfo,zqbwgoodsinfo两张表,一张表代表一个活动。我在存储过程里写了两个游标去遍历这两帐表,但是报错,下面是错误信息:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'done=1
END REPEAT;
close cur1;
END;BEGIN
delcare cur2 cursor for select id fro' at line 45还请高手们帮我查查,或者两个游标在SP里面如何使用才不会出错?
create table `zqbwactiveinfo` (
`activeid` int(11) not null auto_increment,
`begintime` datetime,
`endtime` datetime,
`bz` varchar(200) default null,
primary key(`activeid`)
) engine=myisam default charset=utf8;
create table `zqbwgiftinfo` (
`giftsn` bigint(20) not null auto_increment,
`giftid` int(11),
`beginmoney` int(11),
`endmoney` int(11),
`giftmoney` int(11),
`addtime` datetime,
primary key(`giftsn`)
) engine=myisam default charset=utf8;create table `zqbwgoodsinfo` (
`goodsn` bigint(20) not null auto_increment,
`id` int(11),
`goodsgift` smallint,
`goodsnum` smallint,
`addtime` datetime,
primary key(`goodsn`)
) engine=myisam default charset=utf8;建立存储过程
delimiter $$
create procedure `zqbw_pay_v1` (
in p_Money int,
in p_LoginName varchar(32)
)
begin
declare act_id int;
declare gift_id int;
declare bmoney int;
declare emoney int;
declare gmoney int;
declare gid int;
declare ggift int;
declare record_Num INT;
declare item_Num INT;
DECLARE user_ID INT;
declare done int default 0;
declare done2 int default 0;
if (p_Money > 0) then
update alluser set Money=ifnull(money,0)+p_Money where LoginName = p_LoginName;
end if;
insert into rechargerecord
( SeverName,
LoginName,
Money,
Gift,
Times)
values
( p_SeverName,
p_LoginName,
p_Money,
0,
sysdate());
select activeid into act_id from zqbwactiveinfo order by activeid desc limit 1;
BEGIN
declare cur1 cursor for select giftid from zqbwgiftinfo where giftid=act_id;
declare continue handler for not found set done=1;
open cur1;
REPEAT
fetch cur1 into gift_id;
BEGIN
select beginmoney,endmoney,giftmoney into bmoney,emoney,gmoney from zqbwgiftinfo where giftid=gift_id;
if p_Money>=bmoney and p_Money<emoney then
update alluser set GIFTCERTIFICATE=GIFTCERTIFICATE+gmoney where LoginName = p_LoginName;
end if;
UNTIL done=1
END REPEAT;
close cur1;
END;BEGIN
delcare cur2 cursor for select id from zqbwgoodsinfo where id=act_id;
declare continue handler for not found set done2=1;
open cur2;
fetch cur2 into gid;
select goodsgift into ggift from zqbwgoodsinfo where id=gid;
SELECT USERID INTO user_ID FROM ALLUSER WHERE LOGINNAME=p_LoginName;
SELECT COUNT(*) INTO record_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
IF record_Num=0 THEN
INSERT INTO PLAYERBAGGOODSINFO (USERID, GOODSID, NUM, GIFTCERTIFICATEBUYNUM) VALUES(user_ID, ggift, FLOOR(p_Money/500), 0);
ELSE
SELECT NUM INTO item_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
UPDATE PLAYERBAGGOODSINFO SET NUM=item_Num+FLOOR(p_Money/500) WHERE USERID=user_ID AND GOODSID=ggift;
END IF;
UNTIL done2=1;
END REPEAT;
close cur2;
END;
END $$
delimiter ;
简单介绍下存储过程的目的,如果你充值p_Money,然后根据最后的活动ID取zqbwactiveinfo的ID,然后匹配zqbwgiftinfo,zqbwgoodsinfo两张表,一张表代表一个活动。我在存储过程里写了两个游标去遍历这两帐表,但是报错,下面是错误信息:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'done=1
END REPEAT;
close cur1;
END;BEGIN
delcare cur2 cursor for select id fro' at line 45还请高手们帮我查查,或者两个游标在SP里面如何使用才不会出错?
mysql> drop procedure zqbw_pay_v1;
Query OK, 0 rows affected (0.00 sec)mysql> delimiter $$
mysql> create procedure `zqbw_pay_v1` (
-> in p_Money int,
-> in p_LoginName varchar(32)
-> )
-> BEGIN
-> declare act_id int;
-> declare gift_id int;
-> declare bmoney int;
-> declare emoney int;
-> declare gmoney int;
-> declare gid int;
-> declare ggift int;
-> declare record_Num INT;
-> declare item_Num INT;
-> DECLARE user_ID INT;
-> declare done int default 0;
-> declare done2 int default 0;
-> if (p_Money > 0) then
-> update alluser set Money=ifnull(money,0)+p_Money where LoginName = p_LoginName;
-> end if;
-> insert into rechargerecord
-> (SeverName,
-> LoginName,
-> Money,
-> Gift,
-> Times)
-> values
-> (p_SeverName,
-> p_LoginName,
-> p_Money,
-> 0,
-> sysdate());
-> select activeid into act_id from zqbwactiveinfo order by activeid desc limit 1;
-> BEGIN
-> declare cur1 cursor for select giftid from zqbwgiftinfo where giftid=act_id;
-> declare continue handler for not found set done=1;
-> open cur1;
-> REPEAT
-> fetch cur1 into gift_id;
-> select beginmoney,endmoney,giftmoney into bmoney,emoney,gmoney from zqbwgiftinfo where giftid=gift_id;
-> if p_Money>=bmoney and p_Money<emoney then
-> update alluser set GIFTCERTIFICATE=GIFTCERTIFICATE+gmoney where LoginName = p_LoginName;
-> end if;
-> UNTIL done=1
-> END REPEAT;
-> close cur1;
-> END;
->
-> outer_block:BEGIN
-> delcare cur2 cursor for select id from zqbwgoodsinfo id=act_id;
-> declare continue handler for not found set done2=1;
-> open cur2;
-> fetch cur2 into gid;
-> select goodsgift into ggift from zqbwgoodsinfo where id=gid;
-> SELECT USERID INTO user_ID FROM ALLUSER WHERE LOGINNAME=p_LoginName;
-> SELECT COUNT(*) INTO record_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
-> IF record_Num=0 THEN
-> INSERT INTO PLAYERBAGGOODSINFO (USERID, GOODSID, NUM, GIFTCERTIFICATEBUYNUM) VALUES(user_ID, ggift, FLOOR(p_Money/500), 0);
-> ELSE
-> SELECT NUM INTO item_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
-> UPDATE PLAYERBAGGOODSINFO SET NUM=item_Num+FLOOR(p_Money/500) WHERE USERID=user_ID AND GOODSID=ggift;
-> END IF;
-> UNTIL done2=1;
-> END REPEAT;
-> close cur2;
-> END outer_block;
-> END $$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cur2 cursor for select id from zqbwgoodsinfo id=act_id;
declare continue handler' at line 50
mysql> delimiter ;
-> outer_block:BEGIN
-> delcare cur2 cursor for select id from zqbwgoodsinfo id=act_id;
-> declare continue handler for not found set done2=1;
-> open cur2;
-> fetch cur2 into gid;
-> select goodsgift into ggift from zqbwgoodsinfo where id=gid;
-> SELECT USERID INTO user_ID FROM ALLUSER WHERE LOGINNAME=p_LoginName;
-> SELECT COUNT(*) INTO record_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
-> IF record_Num=0 THEN
-> INSERT INTO PLAYERBAGGOODSINFO (USERID, GOODSID, NUM, GIFTCERTIFICATEBUYNUM) VALUES(user_ID, ggift, FLOOR(p_Money/500), 0);
-> ELSE
-> SELECT NUM INTO item_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
-> UPDATE PLAYERBAGGOODSINFO SET NUM=item_Num+FLOOR(p_Money/500) WHERE USERID=user_ID AND GOODSID=ggift;
-> END IF;
-> UNTIL done2=1;
-> END REPEAT;
-> close cur2;
-> END outer_block;
-> END $$改成---------------------------》
-> BEGIN
-> delcare cur2 cursor for select id from zqbwgoodsinfo id=act_id;
-> declare continue handler for not found set done2=1;
-> open cur2;
repeat
-> fetch cur2 into gid;
-> select goodsgift into ggift from zqbwgoodsinfo where id=gid;
-> SELECT USERID INTO user_ID FROM ALLUSER WHERE LOGINNAME=p_LoginName;
-> SELECT COUNT(*) INTO record_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
-> IF record_Num=0 THEN
-> INSERT INTO PLAYERBAGGOODSINFO (USERID, GOODSID, NUM, GIFTCERTIFICATEBUYNUM) VALUES(user_ID, ggift, FLOOR(p_Money/500), 0);
-> ELSE
-> SELECT NUM INTO item_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
-> UPDATE PLAYERBAGGOODSINFO SET NUM=item_Num+FLOOR(p_Money/500) WHERE USERID=user_ID AND GOODSID=ggift;
-> END IF;
-> UNTIL done2=1;
-> END REPEAT;
-> close cur2;
-> END;
-> END $$
create procedure `zqbw_pay_v1` (
in p_Money int,
in p_LoginName varchar(32)
)
begin
declare act_id int;
declare gift_id int;
declare bmoney int;
declare emoney int; declare gmoney int;
declare gid int;
declare ggift int;
declare record_Num INT;
declare item_Num INT;
DECLARE user_ID INT;
declare done int default 0;
declare done2 int default 0; declare cur1 cursor for select giftid from zqbwgiftinfo where giftid=act_id;
declare cur2 cursor for select id from zqbwgoodsinfo where id=act_id;
declare continue handler for not found set done=1; if (p_Money > 0) then
update alluser set Money=ifnull(money,0)+p_Money where LoginName = p_LoginName;
end if;
insert into rechargerecord
( SeverName,
LoginName,
Money,
Gift,
Times)
values
( p_SeverName,
p_LoginName,
p_Money,
0,
sysdate());
select activeid into act_id from zqbwactiveinfo order by activeid desc limit 1; open cur1;
REPEAT
fetch cur1 into gift_id;
select beginmoney,endmoney,giftmoney into bmoney,emoney,gmoney from zqbwgiftinfo where giftid=gift_id;
if p_Money>=bmoney and p_Money <emoney then
update alluser set GIFTCERTIFICATE=GIFTCERTIFICATE+gmoney where LoginName = p_LoginName;
end if;
UNTIL done=1 END REPEAT;
close cur1;
set done=0;
open cur2;
REPEAT
fetch cur2 into gid;
select goodsgift into ggift from zqbwgoodsinfo where id=gid;
SELECT USERID INTO user_ID FROM ALLUSER WHERE LOGINNAME=p_LoginName;
SELECT COUNT(*) INTO record_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
IF record_Num=0 THEN
INSERT INTO PLAYERBAGGOODSINFO (USERID, GOODSID, NUM, GIFTCERTIFICATEBUYNUM) VALUES(user_ID, ggift, FLOOR(p_Money/500), 0);
ELSE
SELECT NUM INTO item_Num FROM PLAYERBAGGOODSINFO WHERE USERID=user_ID AND GOODSID=ggift;
UPDATE PLAYERBAGGOODSINFO SET NUM=item_Num+FLOOR(p_Money/500) WHERE USERID=user_ID AND GOODSID=ggift;
END IF;
UNTIL done=1 END REPEAT;
close cur2;
END $$
delimiter ;
过多的语法错误,你直接复制上面的语句,不要再去改你自己的语句了。