MySQL 5.0新特性教程 存储过程:第四讲
2005-10-31 16:42:19 来源:WEB开发网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。
更多精彩
赞助商链接