大大们帮小弟看下吧
试验个最简单的mysql存储过程,是在heidisql5.0.0.3272版本上测试的。
CREATE PROCEDURE `test`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare i integer;
set i=1;
while i < 10 do
set i=i+1 ;
select i;
end while;
END
问题是循环无论用while,loop还是repeat,结果都是一样,循环只执行了一次,便跳出循环了,运行结果始终是i=2,一条记录。请问这是什么原因呢?原先在oracle里面没遇到过啊。。
试验个最简单的mysql存储过程,是在heidisql5.0.0.3272版本上测试的。
CREATE PROCEDURE `test`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare i integer;
set i=1;
while i < 10 do
set i=i+1 ;
select i;
end while;
END
问题是循环无论用while,loop还是repeat,结果都是一样,循环只执行了一次,便跳出循环了,运行结果始终是i=2,一条记录。请问这是什么原因呢?原先在oracle里面没遇到过啊。。
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE `test`()
-> LANGUAGE SQL
-> NOT DETERMINISTIC
-> CONTAINS SQL
-> SQL SECURITY DEFINER
-> COMMENT ''
-> BEGIN
-> declare i integer;
-> set i=1;
-> while i < 10 do
-> set i=i+1 ;
-> select i;
-> end while;
-> END
-> //
Query OK, 0 rows affected (0.12 sec)mysql>
mysql> delimiter ;
mysql>
mysql> call test();
+------+
| i |
+------+
| 2 |
+------+
1 row in set (0.03 sec)+------+
| i |
+------+
| 3 |
+------+
1 row in set (0.05 sec)+------+
| i |
+------+
| 4 |
+------+
1 row in set (0.06 sec)+------+
| i |
+------+
| 5 |
+------+
1 row in set (0.08 sec)+------+
| i |
+------+
| 6 |
+------+
1 row in set (0.08 sec)+------+
| i |
+------+
| 7 |
+------+
1 row in set (0.09 sec)+------+
| i |
+------+
| 8 |
+------+
1 row in set (0.11 sec)+------+
| i |
+------+
| 9 |
+------+
1 row in set (0.13 sec)+------+
| i |
+------+
| 10 |
+------+
1 row in set (0.13 sec)Query OK, 0 rows affected (0.14 sec)mysql>
mysql> CREATE PROCEDURE `test`()
-> BEGIN
-> declare i integer;
-> set i=1;
-> while i < 10 do
-> set i=i+1 ;
-> select i;
-> end while;
-> END
-> //
Query OK, 0 rows affected (0.03 sec)mysql> test
-> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'test' at line 1
mysql> call test()//
+---+
| i |
+---+
| 2 |
+---+
1 row in set (0.00 sec)+---+
| i |
+---+
| 3 |
+---+
1 row in set (0.01 sec)+---+
| i |
+---+
| 4 |
+---+
1 row in set (0.01 sec)+---+
| i |
+---+
| 5 |
+---+
1 row in set (0.01 sec)+---+
| i |
+---+
| 6 |
+---+
1 row in set (0.01 sec)+---+
| i |
+---+
| 7 |
+---+
1 row in set (0.01 sec)+---+
| i |
+---+
| 8 |
+---+
1 row in set (0.01 sec)+---+
| i |
+---+
| 9 |
+---+
1 row in set (0.01 sec)+----+
| i |
+----+
| 10 |
+----+
1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)mysql>