8. Cursor Example
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; <-- SET return_val = a; END;// 到了这一步UNTIL b=1条件就为真,循环结束。在这里我们可以自己编写代码关闭游标,也可以由系统执行,系统会在复合语句结束时自动关闭游标,但是最好不要太依赖系统的自动关闭行为(译注:这可能跟Java的Gc一样,不可信)。
9. Cursor Example
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; <-- END;// 这个例程中我们为输出参数指派了一个局部变量,这样在过程结束后的结果仍能使用。
10. Cursor Example
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;//
mysql> CALL p25(@return_val)// Query OK, 0 rows affected (0.00 sec) mysql> SELECT @return_val// +-------------+ | @return_val | +-------------+ | 5 | +-------------+ 1 row in set (0.00 sec) 上面是过程调用后的结果。可以看到return_val参数获得了数值5,因为这是表t的最后一行。 由此可以知道游标工作正常,出错处理也工作正常。
|