MySQL 5.0新特性教程 存储过程:第四讲
2005-10-31 16:42:19 来源:WEB开发网2. 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;//
这个过程开始声明了三个变量。附带说一下,顺序是十分重要的。首先要进行变量声明,然后声明条件,随后声明游标,再后面才是声明错误处理器。如果你没有按顺序声明,系统会提示错误信息。
3. 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;//
程序第二步声明了游标cur_1,如果你使用过嵌入式SQL的话,就知道这和嵌入式SQL差不多。
4. 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;//
最后进行的是错误处理器的声明。这个CONTINUE处理没有引用SQL错误代码和SQLSTATE值。它使用的是NOT FOUND系统返回值,这和SQLSTATE 02000是一样的。
更多精彩
赞助商链接