CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_nogoodsother`(num int , goodsId 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 flag INT DEFAULT 0;
DECLARE asql varchar(500);
DECLARE cur CURSOR FOR SELECT * from newtt ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
SET @asql='create table newtt as select goodsPointId FROM nogoodspointother where goodsPointId >tempId';
PREPARE stml FROM @asql;
EXECUTE stml;
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;
set flag=1;
select tempId;
END;
END IF ;
FETCH cur INTO id;
END WHILE;
CLOSE cur;
set num=num-1 ;
if flag=0 then
begin
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;
end;
end if ;
end while ;
END;
执行报错---------------------------
错误
---------------------------
SQL 执行错误 # 1054. 从数据库的响应:Unknown column 'tempId' in 'where clause'
---------------------------
确定
---------------------------
怎么回事?
SET @asql=concat('create table newtt as select goodsPointId FROM nogoodspointother where goodsPointId >',tempId);
select tempId;
END;