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怎么操作呢?
解决方案 »
- 哪位大侠知道:如何用 MySql++ 存取 Blob 字段啊?就是二进制字段,最好给个例子,不胜感激。。。
- 关于两表查询取不重复的记录.
- 求解决多列名次问题
- MySQL 问题
- 字段为DATETIME类型,如何获得写入时间最早的记录?
- mysql字符串哈希比较
- 如何通过MySql提供的JDBC桥直接进行数据库连接?
- 100分求PostgreSql数据库的下载地址!
- 如何将postgreSQL在linux上的SQL文导入到windows下postgreSQL里?
- 请问怎样读取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 ;