t_user 表
mysql> select * from t_user
+------+--------+--------+
| c_id | c_code | c_name |
+------+--------+--------+
| 1 | 1 | lin |
| 2 | 2 | esx |
+------+--------+--------+
2 rows in set (0.00 sec)sde表
mysql> select count(*) from sde;
+----------+
| count(*) |
+----------+
| 26 |
+----------+
1 row in set (0.00 sec)mysql> select a as s1 from sde;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 0 |
| 2 |
| 3 |
| 4 |
| 0 |
| 1 |
| 3 |
| 4 |
| 0 |
| 1 |
| 2 |
| 4 |
| 0 |
| 1 |
| 2 |
| 4 |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
26 rows in set (0.00 sec)过程如下。。create PROCEDURE xht_yb()
BEGIN
DECLARE cz VARCHAR(255);
DECLARE a VARCHAR(255);
DECLARE xh VARCHAR(255) DEFAULT (select count(*) from sde);
DECLARE cursor_1 CURSOR for select a as s1 from sde;
DECLARE CONTINUE HANDLER for not found
set a=0;
open cursor_1;
REPEAT
FETCH cursor_1 into cz;
insert into t_user(c_code) VALUES(cz);
set a=a+1;
UNTIL a>=xh
end REPEAT;
CLOSE cursor_1;
end;
call xht_yb();之后无限循环空格请问这个问题在哪里呢??百思不得其解。。我说下我这个思路主要是不想用DECLARE CONTINUE HANDLER for not found set a=0;触发=0 然后until=1结束。而是想用根据
select count(*) from sde 得出数量然后UNTIL a>=xh 结束。
求教。。
解决方案 »
- centos6 安装Mysql出现的问题
- MYSQL表加索引问题?
- 遇到mysql case when 问题
- mysql子查询中使用limit错误
- mysql 字符集问题,只能求助如此了。
- 首先憎恨mysql.市井公司滩涂便宜带来的愁苦. 怎么从一个表里查出其中一列不包含另一表一列的值的最大值.
- 远程调用Mysql,使用数据导出功能,出错,高手帮忙!
- 求教:如何把acess数据导入mysql数据库
- 请前辈帮助 >>> PHP 链接 MYSQL 时出错。
- 怎样在办公网络通过跳板机获取生产环境的MySQL数据
- mysql中 在一张表中删除父类子类也删除 的sql语句 表已建好
- 求大神指点一个sql语句。
BEGIN
DECLARE cz VARCHAR(255);
DECLARE a INT;
DECLARE DONE INT DEFAULT 0;
DECLARE xh INT DEFAULT (SELECT COUNT(*) FROM `app_makinginfo`);
DECLARE cursor_1 CURSOR FOR SELECT id AS s1 FROM `app_makinginfo`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET a=1;
OPEN cursor_1;
WHILE A<=XH DO
FETCH cursor_1 INTO cz;
INSERT INTO t_user VALUES(cz);
SET a=a+1;
END WHILE ;
CLOSE cursor_1;
END$$DELIMITER ;
DELIMITER $$DROP PROCEDURE IF EXISTS `xht_yb`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `xht_yb`()
BEGIN
DECLARE cz VARCHAR(255);
DECLARE a INT;
DECLARE DONE INT DEFAULT 0;
DECLARE xh INT DEFAULT (SELECT COUNT(*) FROM `sde`);
DECLARE cursor_1 CURSOR FOR SELECT id AS s1 FROM `sde`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET a=1;
OPEN cursor_1;
REPEAT
FETCH cursor_1 INTO cz;
INSERT INTO t_user VALUES(cz);
SET a=a+1;
UNTIL a>xh
END REPEAT;
CLOSE cursor_1;
END$$DELIMITER ;
DECLARE xh INT DEFAULT (SELECT COUNT(*) FROM `app_makinginfo`);xh 赋值了是26UNTIL a>=xh end REPEAT;
a>26次之后停止循环。。
但是没有循环,,都是取得一行数值。。
mysql> select * from t_user
+------+--------+--------+
| c_id | c_code | c_name |
+------+--------+--------+
| 883 | 1 | NULL |
| 884 | 1 | NULL |
| 885 | 1 | NULL |
| 886 | 1 | NULL |
| 887 | 1 | NULL |
| 888 | 1 | NULL |
| 889 | 1 | NULL |
| 890 | 1 | NULL |
| 891 | 1 | NULL |
| 892 | 1 | NULL |
| 893 | 1 | NULL |
| 894 | 1 | NULL |
| 895 | 1 | NULL |
| 896 | 1 | NULL |
| 897 | 1 | NULL |
| 898 | 1 | NULL |
| 899 | 1 | NULL |
| 900 | 1 | NULL |
| 901 | 1 | NULL |
| 902 | 1 | NULL |
| 903 | 1 | NULL |
| 904 | 1 | NULL |
| 905 | 1 | NULL |
| 906 | 1 | NULL |
| 907 | 1 | NULL |
| 908 | 1 | NULL |
+------+--------+--------+
26 rows in set (0.00 sec)
DECLARE cursor_1 CURSOR FOR SELECT id AS s1 FROM `sde`;->
DECLARE cursor_1 CURSOR FOR SELECT a AS s1 FROM `sde`;
mysql> SELECT a AS s1 FROM sde;
+------+
| s1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 0 |
| 2 |
| 3 |
| 4 |
| 0 |
| 1 |
| 3 |
| 4 |
| 0 |
| 1 |
| 2 |
| 4 |
| 0 |
| 1 |
| 2 |
| 4 |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
26 rows in set (0.00 sec)
方法就是用提供的方法。。
DECLARE xh VARCHAR(255);DECLARE cursor_1 CURSOR for select a as s1 from sde;
DECLARE CONTINUE HANDLER for not found set xh=1;
set xh=0;open cursor_1;REPEATFETCH cursor_1 into cz;if xh<>1 THEN
insert into t_user(c_code) VALUES(cz);
end if;
UNTIL xh=1
end REPEAT;
CLOSE cursor_1;
end;
纳闷了这样就可以但是就不喜欢这样结束循环方法。
DECLARE CONTINUE HANDLER for not found set xh=1;
UNTIL xh=1
end REPEAT;这样结束循环
MYSQL语法要求就是这样,除非你不用MYSQL,你的代码
DECLARE CONTINUE HANDLER for not found
本来就是错误的
查了些mysql资料关于 DECLARE CONTINUE HANDLER for not found set xh=1;这块当mysql的 fetch 没有获得行时,CONTINUE 处理被触发 将赋数值,这里我赋的为1.后边UNTIL xh=1 关闭循环。而我只不过是没用 xh=1这个值 关闭循环,用其他方法。个人感觉,应该可以