create procedure pro_nogoodsother(goodsId int,memberId int,consumerId int,p int, oncePoint int,comAmout int ,canPoint int )
begin
declare id int ;
declare lnumber int ;
declare stop int default 0;
declare cnt int default 0;
declare tempId int default 0;
declare cur cursor for select goodsPointId from nogoodspointother order by goodsPointId ASC ;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
select loopNumber into lnumber from tbl_loop;
open cur;
fetch cur into id ;
--
while stop <> 1 do
-- select count(*) into cnt from nogoodspointother where goodsPointId= id+1
if cnt=0 and id>tempId then
begin
-- insert into nogoodspointOther(goodsPointId,goodsId,memberId,consumerId,canPoints,totalPoints)
-- values (id+1,goodsId,goodsId,memberId,consumerId,canPoints,oncePoint);
-- set p=p-oncePoint;
-- set comAmout=comAmout-canPoint;
-- set tempId=tempId+ lnumber;
end
end if ;
fetch cur into id;
end while;
close cur;
end; 好像是while里不能用if,那我要用if怎么操作呢?
解决方案 »
- 关于sphinx安装配置完不能搜到数据的问题
- 遍历游标的时候,不能执行动态sql(prepate stmt from @v_Sql)吗?
- 数据库监控程序 如何判断mysql等协议包
- user表中,root用户的 host字段,localhost与127.0.0.1有区别吗?
- MYSQL如果实现存储过程加密
- 在日文系统下,将txt文件导入数据库,txt中有中文,日文,英文,怎么才能不出现乱码
- Postgres的permission deniey问题
- 简单问题
- 新人求助!一个mysql语句的写法
- 求教一个海量数据的存储设计方案
- 解析字符串问题,100100000101011111010101解析出111
- 求助, mysql update 的语句
begin
declare id int ;
declare lnumber int ;
declare stop int default 0;
declare cnt int default 0;
declare tempId int default 0;
declare cur cursor for select goodsPointId from nogoodspointother order by goodsPointId ASC ;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
select loopNumber into lnumber from tbl_loop;
open cur;
fetch cur into id ;
--
while stop <> 1 do
select count(*) into cnt from nogoodspointother where goodsPointId= id+1
if cnt=0 and id>tempId then
insert into nogoodspointOther(goodsPointId,goodsId,memberId,consumerId,canPoints,totalPoints)
values (id+1,goodsId,goodsId,memberId,consumerId,canPoints,oncePoint);
set p=p-oncePoint;
set comAmout=comAmout-canPoint;
set tempId=tempId+ lnumber;
end if ;
fetch cur into id;
end while;
close cur;
end;
'截至2011-01-04 22:49:45 用户结帖率0.00%当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖
CREATE PROCEDURE pro_nogoodsother(goodsId INT,memberId INT,consumerId INT,p INT, oncePoint INT,comAmout INT ,canPoint INT )
BEGIN
DECLARE id INT ;
DECLARE lnumber INT ;
DECLARE STOP INT DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
DECLARE tempId INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT goodsPointId FROM nogoodspointother ORDER BY goodsPointId ASC ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
SELECT loopNumber INTO lnumber FROM tbl_loop;
OPEN cur;
FETCH cur INTO id ;
--
WHILE STOP <> 1 DO
-- select count(*) into cnt from nogoodspointother where goodsPointId= id+1
IF cnt=0 AND id>tempId THEN
BEGIN
-- insert into nogoodspointOther(goodsPointId,goodsId,memberId,consumerId,canPoints,totalPoints)
-- values (id+1,goodsId,goodsId,memberId,consumerId,canPoints,oncePoint);
-- set p=p-oncePoint;
-- set comAmout=comAmout-canPoint;
-- set tempId=tempId+ lnumber;
END;
END IF ;
FETCH cur INTO id;
END WHILE;
CLOSE cur;
END; $$DELIMITER ;
这个我得要呀,cnt这个是从这里查出来的,加上这句就不对了呀?
DROP PROCEDURE IF EXISTS pro_nogoodsother$$
CREATE PROCEDURE pro_nogoodsother(goodsId INT,memberId INT,consumerId INT,p INT, oncePoint INT,comAmout INT ,canPoint INT )
BEGIN
DECLARE id INT ;
DECLARE lnumber INT ;
DECLARE STOP INT DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
DECLARE tempId INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT goodsPointId FROM nogoodspointother ORDER BY goodsPointId ASC ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
SELECT loopNumber INTO lnumber FROM tbl_loop;
OPEN cur;
FETCH cur INTO id ;
--
WHILE STOP <> 1 DO
SELECT COUNT(*) INTO cnt FROM nogoodspointother WHERE goodsPointId= id+1;
IF cnt=0 AND id>tempId THEN
BEGIN
INSERT INTO nogoodspointOther(goodsPointId,goodsId,memberId,consumerId,canPoints,totalPoints)
VALUES (id+1,goodsId,goodsId,memberId,consumerId,canPoints,oncePoint);
SET p=p-oncePoint;
SET comAmout=comAmout-canPoint;
SET tempId=tempId+ lnumber;
END;
END IF ;
FETCH cur INTO id;
END WHILE;
CLOSE cur;
END; $$DELIMITER ;