mysql> select * from testB;
+----+
| id |
+----+
| 0 |
| 2 |
| 4 |
+----+
3 rows in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure pro_test8(out rows int) //out输出结果的参数
-> begin
-> declare cid int; //声明cid帮忙计数
-> declare found boolean default true; //声明bool变量值,默认为真
-> declare cur_cid cursor for //声明游标
-> select id from testB;
-> declare continue handler for not found
-> set found = false; //定义CONTINUE HANDLER句柄作用是:如果没有返回值就设置为false
-> set rows = 0; //此句之前均为声明
-> open cur_cid;
-> fetch cur_cid into cid;
-> while found do
-> set rows = rows + 1;
-> fetch cur_cid into cid;
-> end while;
-> close cur_cid;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> call pro_test8(@rows) $$
Query OK, 0 rows affected, 1 warning (0.69 sec)
mysql> select @rows $$
+-------+
| @rows |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)
mysql> call pro_test8(@row) $$
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @row $$
+------+
| @row |
+------+
| 3 |
+------+
1 row in set (0.00 sec)