MySQL 5.0新特性教程 存储过程:第四讲

核心提示： 5. Cursor ExampleCREATE PROCEDURE p25 (OUT return_val INT)BEGINDECLARE a,b INT;DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;DECLARE CONTINUE HANDLER FOR NOT FOUN

5. 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;//

　　过程第一个可执行的语句是OPEN cur_1，它与SELECT s1 FROM t语句是关联的，过程将执行SELECT s1 FROM t，返回一个结果集。


6. 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;//

　　这里第一个FETCH语句会获得一行从SELECT产生的结果集中检索出来的值，然而表t中有多行，因此这个语句会被执行多次，当然这是因为语句在循环块内。


7. 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(和PHP搭配之最佳组合)的FETCH没有获得行时，CONTINUE处理被触发，将变量b赋值为1。