CREATE PROCEDURE pro_nogoodsother(num int , varchar(200),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;
SELECT loopNumber INTO lnumber FROM tbl_loop;
while num>0 do
DECLARE cur CURSOR FOR SELECT goodsPointId FROM nogoodspointother ORDER BY goodsPointId ASC ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
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
select id;
select tempId;
INSERT INTO nogoodspointOther(goodsPointId,goodsId,memberId,consumerId,canPoints,totalPoints)
VALUES (id+1,goodsId,memberId,consumerId,canPoints,oncePoint);
SET p=p-oncePoint;
SET comAmout=comAmout-canPoint;
SET tempId=id+ lnumber;
select tempId;
END;
END IF ;
FETCH cur INTO id;
END WHILE;
CLOSE cur;
set num=num-1 ;
end while ;
END;
这上面怎么不对呢?while循环里怎么添加游标?while 里好像有 declare 就报错!
BEGIN
DECLARE id INT ;
DECLARE lnumber INT ;
DECLARE STOP INT DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
DECLARE tempId INT DEFAULT 0;
SELECT loopNumber INTO lnumber FROM tbl_loop;
while num>0 do
DECLARE cur CURSOR FOR SELECT goodsPointId FROM nogoodspointother ORDER BY goodsPointId ASC ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
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
select id;
select tempId;
INSERT INTO nogoodspointOther(goodsPointId,goodsId,memberId,consumerId,canPoints,totalPoints)
VALUES (id+1,goodsId,memberId,consumerId,canPoints,oncePoint);
SET p=p-oncePoint;
SET comAmout=comAmout-canPoint;
SET tempId=id+ lnumber;
select tempId;
END;
END IF ;
FETCH cur INTO id;
END WHILE;
CLOSE cur;
set num=num-1 ;
end while ;
END;
这上面怎么不对呢?while循环里怎么添加游标?while 里好像有 declare 就报错!
解决方案 »
- 如何在postgresql 函数中创建临时表???
- 如何不给SLAVE上的账号ALTER TALBE的权限?
- group by 后,除了sum,还可以使用其他函数吗?
- 求助!访问mysql记录的问题
- 高手求救!这应该如何处理。
- MySql 中的 DELETE 真差劲...
- 关于mysql中的日期问题
- 请教通过linux odbc/myodbc 连接mysql 数据库问题!
- 有点奇怪,php+mysql从数据库存取资料时有时会乱码,是php的bug还是mysql的?不会只有我碰见吧?(ipman)
- linux环境下没发现mysql "查询日志"“-log”
- mysql like语句有问题
- 要写一份mysql数据库维护文档,大家给点建议吧,谢谢!
DROP PROCEDURE IF EXISTS pro_nogoodsother $$
CREATE PROCEDURE pro_nogoodsother(num INT , aa VARCHAR(200),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;
WHILE num>0 DO
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
SELECT id;
SELECT tempId;
INSERT INTO nogoodspointOther(goodsPointId,goodsId,memberId,consumerId,canPoints,totalPoints)
VALUES (id+1,goodsId,memberId,consumerId,canPoints,oncePoint);
SET p=p-oncePoint;
SET comAmout=comAmout-canPoint;
SET tempId=id+ lnumber;
SELECT tempId;
END;
END IF ;
FETCH cur INTO id;
END WHILE;
CLOSE cur;
SET num=num-1 ;
END WHILE ;
END; $$
DELIMITER ;
num int , varchar(200) 少1个参数
这不能放到外面,我要这里加条件,条件是循环取出来的值。
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;
while num>0 do
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
select id;
select tempId;
INSERT INTO nogoodspointOther(goodsPointId,goodsId,memberId,consumerId,canPoints,totalPoints)
VALUES (id+1,goodsId,memberId,consumerId,canPoints,oncePoint);
SET p=p-oncePoint;
SET comAmout=comAmout-canPoint;
SET tempId=id+ lnumber;
select tempId;
END;
END IF ;
FETCH cur INTO id;
END WHILE;
CLOSE cur;
set num=num-1 ;
end while ;
END;
CREATE PROCEDURE pro_nogoodsother(num int , varchar(200),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;
SELECT loopNumber INTO lnumber FROM tbl_loop;
while num>0 do DECLARE cur CURSOR FOR SELECT goodsPointId FROM nogoodspointother where goodsPointId >tempId ORDER BY goodsPointId ASC ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
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
select id;
select tempId;
INSERT INTO nogoodspointOther(goodsPointId,goodsId,memberId,consumerId,canPoints,totalPoints)
VALUES (id+1,goodsId,memberId,consumerId,canPoints,oncePoint);
SET p=p-oncePoint;
SET comAmout=comAmout-canPoint;
SET tempId=id+ lnumber;
select tempId;
END;
END IF ;
FETCH cur INTO id;
END WHILE;
CLOSE cur;
set num=num-1 ; end while ;
END;
DECLARE cur CURSOR FOR SELECT goodsPointId FROM nogoodspointother where goodsPointId >tempId ORDER BY goodsPointId ASC ; tempId 是在循环里取的,放到外面值不变了。
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
SET @asql='create table newtt as goodsPointId FROM nogoodspointother where goodsPointId >tempId';
PREPARE stml FROM @asql;
EXECUTE stml;
OPEN cur;
FETCH cur INTO id ;......or在循环中判断
错误
---------------------------
SQL 执行错误 # 1054. 从数据库的响应:Unknown column 'tempId' in 'where clause'
---------------------------
确定
---------------------------
这是怎么回事?执行的时候的错。
PREPARE stml FROM @asql;
EXECUTE stml;