我写了个存储过程,想验证游标使用方法的,代码如下
DELIMITER //
CREATE PROCEDURE jinsl()
BEGIN
DECLARE P VARCHAR(100);
-- 判断游标到达最后的情况
DECLARE _STOP INT DEFAULT 0;
-- 声明游标
DECLARE CUR CURSOR FOR SELECT P FROM JINSL_NET_TYPE;
-- 设置游标的终止条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _STOP=1;
-- 打开游标
OPEN CUR;
FETCH CUR INTO P;
WHILE _STOP<>1 DO
INSERT INTO JINSL_RESULT(net_type,product_type) VALUES(p, 0);
FETCH CUR INTO P;
END WHILE;
CLOSE CUR;
END
//
DELIMITER ;执行后报如题的错误,并且插入到表的记录的空的,条数是对的,这个小弟没明白是怎么回事,感觉应该是游标用的有问题,希望大神赐教,谢谢!
DELIMITER //
CREATE PROCEDURE jinsl()
BEGIN
DECLARE P VARCHAR(100);
-- 判断游标到达最后的情况
DECLARE _STOP INT DEFAULT 0;
-- 声明游标
DECLARE CUR CURSOR FOR SELECT P FROM JINSL_NET_TYPE;
-- 设置游标的终止条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _STOP=1;
-- 打开游标
OPEN CUR;
FETCH CUR INTO P;
WHILE _STOP<>1 DO
INSERT INTO JINSL_RESULT(net_type,product_type) VALUES(p, 0);
FETCH CUR INTO P;
END WHILE;
CLOSE CUR;
END
//
DELIMITER ;执行后报如题的错误,并且插入到表的记录的空的,条数是对的,这个小弟没明白是怎么回事,感觉应该是游标用的有问题,希望大神赐教,谢谢!
CREATE PROCEDURE jinsl()
BEGIN
DECLARE P1 VARCHAR(100);
-- 判断游标到达最后的情况
DECLARE _STOP INT DEFAULT 0;
-- 声明游标
DECLARE CUR CURSOR FOR SELECT P FROM JINSL_NET_TYPE;
-- 设置游标的终止条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _STOP=1;
-- 打开游标
OPEN CUR;
FETCH CUR INTO P1;
WHILE _STOP<>1 DO
INSERT INTO JINSL_RESULT(net_type,product_type) VALUES(p1, 0);
FETCH CUR INTO P1;
END WHILE;
CLOSE CUR;
END
//
DELIMITER ;
这个表中有记录吗?
(0 row(s) affected, 1 warning(s))
Execution Time : 00:00:00:031
Transfer Time : 00:00:00:000
Total Time : 00:00:00:031
Error Code : 1329
No data - zero rows fetched, selected, or processed
另外问大神个问题,如下
DECLARE t_name VARCHAR(50);
SET t_name='t10';
SELECT * FROM t_name; -- 错误的,实际想查询t10表中的数据
我想在一个未知表名的表中查记录怎么写存储过程啊?
明日来测试一下
PREPARE STML FROM @ASQL;
EXECUTE STML;
CREATE PROCEDURE jinsl()
BEGIN
DECLARE P1 VARCHAR(100);
-- 判断游标到达最后的情况
DECLARE _STOP INT DEFAULT 0;
-- 声明游标
DECLARE CUR CURSOR FOR SELECT P FROM JINSL_NET_TYPE;
-- 设置游标的终止条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _STOP=1;
-- 打开游标
OPEN CUR;
FETCH CUR INTO P1;
WHILE _STOP<>1 DO
-- INSERT INTO JINSL_RESULT(net_type,product_type) VALUES(p1, 0);
SELECT P1;
FETCH CUR INTO P1;
END WHILE;
CLOSE CUR;
END
//
DELIMITER ;mysql> SELECT * FROM JINSL_NET_TYPE;
+------+------+
| P | ID |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+------+------+
5 rows in set (0.00 sec)mysql> CALL jinsl
-> ;
+------+
| P1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)+------+
| P1 |
+------+
| 2 |
+------+
1 row in set (0.00 sec)+------+
| P1 |
+------+
| 3 |
+------+
1 row in set (0.00 sec)+------+
| P1 |
+------+
| 4 |
+------+
1 row in set (0.01 sec)+------+
| P1 |
+------+
| 5 |
+------+
1 row in set (0.01 sec)Query OK, 0 rows affected, 1 warning (0.01 sec)mysql>