CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_nogoodsother`(num int , goodsId varchar(200),memberId INT,consumerId INT,p INT, oncePoint INT,comAmout INT ,canPoints INT )
BEGIN
DECLARE id INT ;
DECLARE lnumber INT ;
DECLARE STOP INT DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
DECLARE tempId INT DEFAULT 0;
DECLARE maxId INT DEFAULT 0;
DECLARE flag INT DEFAULT 0;
DECLARE tId INT DEFAULT 0;
DECLARE asql varchar(500);
DECLARE cur CURSOR FOR SELECT * from newtt ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
SELECT ifnull(max(goodspointId),0) INTO maxId FROM nogoodspointother;
SELECT loopNumber INTO lnumber FROM tbl_loop;
while num=5 do
DROP table IF EXISTS `newtt`;
if tempId<maxId then
begin
SET @asql=concat('create table newtt as select goodsPointId FROM nogoodspointother where goodsPointId >',tempId);
PREPARE stml FROM @asql;
EXECUTE stml;
OPEN cur;
FETCH cur INTO id ;
WHILE STOP <> 1 DO
//略
FETCH cur INTO id;
END WHILE;
CLOSE cur;
end ;
end if ;
set flag=0;
end while ;
END;
执行时报 newtt 不存在,是怎么回事呢?
BEGIN
DECLARE id INT ;
DECLARE lnumber INT ;
DECLARE STOP INT DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
DECLARE tempId INT DEFAULT 0;
DECLARE maxId INT DEFAULT 0;
DECLARE flag INT DEFAULT 0;
DECLARE tId INT DEFAULT 0;
DECLARE asql varchar(500);
DECLARE cur CURSOR FOR SELECT * from newtt ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
SELECT ifnull(max(goodspointId),0) INTO maxId FROM nogoodspointother;
SELECT loopNumber INTO lnumber FROM tbl_loop;
while num=5 do
DROP table IF EXISTS `newtt`;
if tempId<maxId then
begin
SET @asql=concat('create table newtt as select goodsPointId FROM nogoodspointother where goodsPointId >',tempId);
PREPARE stml FROM @asql;
EXECUTE stml;
OPEN cur;
FETCH cur INTO id ;
WHILE STOP <> 1 DO
//略
FETCH cur INTO id;
END WHILE;
CLOSE cur;
end ;
end if ;
set flag=0;
end while ;
END;
执行时报 newtt 不存在,是怎么回事呢?
newtt不存在。
create table newtt as select goodsPointId FROM nogoodspointother where goodsPointId >',tempId分步调试显示TempId、maxId 中的值
直接在MYSQL中运行
create table newtt as select goodsPointId FROM nogoodspointother where goodsPointId>值
能否建表
select @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
OPEN cur;
FETCH cur INTO id ;
加上这句看一下内容是什么。你的表newtt没有被创建。
select *from newtt;
执行这两句就报这样的错。表newtt不存在,怎么回事?
select @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
SELECT sleep(10);
OPEN cur;
FETCH cur INTO id ; 先加个SLEEP试试,如果可以则需要在程序中加上循环以检查表是否创建完毕。
输出这个,但还报错。
select *from newtt;
其实这样试都不好使,那样也是一回事,这样创建怎么也不好使呢?
有多少条记录
在10万条记录中测试
SET @ASQL='CREATE TABLE QQ.NEWTT AS SELECT * FROM JZG1 WHERE BH=\'101068\'';
PREPARE STML FROM @ASQL;
EXECUTE STML;
SELECT * FROM QQ.NEWTT
没有出现你的问题
即使没有满足的条件记录,应该有1个没有记录的空表,
mysql> create table tx as select * from seqno where id<1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql>