mysql 游标的使用

DELIMITER $$ 2 3 CREATE 4 PROCEDURE `test`.`procedure_student2`() 5 BEGIN 6 -- declare some variable，必须在声明游标和句柄之前，而声明句柄必须在声明游标之后。 7 DECLARE val DOUBLE DEFAULT 0; 8 DECLARE tempRes VARCHAR(10) DEFAULT ‘‘; 9 DECLARE res VARCHAR(100) DEFAULT ‘‘ ; 10 -- declare a cursor 11 DECLARE cursor_avgScore CURSOR 12 FOR 13 SELECT (mathScore+englishScore)/2 AS student_avgScore FROM student2; 14 -- declare a continue handler ,use finish while loop 15 DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET val= -1.0 ; 16 -- open cursor 17 OPEN cursor_avgScore ; 18 FETCH cursor_avgScore INTO val; 19 -- fetch cursor 20 WHILE val!=-1 DO 21 SET tempRes=CONCAT(val,‘, ‘); 22 SET res=CONCAT(res,tempRes); 23 FETCH cursor_avgScore INTO val; 24 END WHILE; 25 -- close cursor 26 CLOSE cursor_avgScore ; 27 -- 显示结果 28 SELECT res; 29 END$$ 30 31 DELIMITER ;

调用此存储过程

CALL procedure_student2();

运行结果如下：

 

上面这个是利用了while循环来一个一个的获取游标中的数据，在MySQL中我们还可以用repeat来做。

 1 DELIMITER $$
 2 
 3 CREATE
 4     PROCEDURE `test`.`procedure_student_v1`() 
 5     BEGIN
 6     -- declare some variable，必须在声明游标和句柄之前，而声明句柄必须在声明游标之后。
 7     DECLARE val DOUBLE DEFAULT 0;
 8     DECLARE tempRes VARCHAR(10) DEFAULT ‘‘;
 9     DECLARE res VARCHAR(100) DEFAULT ‘‘ ;
10     -- declare a cursor
11     DECLARE cursor_avgScore CURSOR
12     FOR
13     SELECT (mathScore+englishScore)/2  AS student_avgScore FROM student2;
14     -- declare a continue handler ,use finish while loop 
15     DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘  SET val= -1.0 ;  
16     -- open cursor
17     OPEN cursor_avgScore ;  
18     -- fetch cursor
19     REPEAT  
20         FETCH cursor_avgScore INTO val;
21         IF val!=-1 THEN
22             SET tempRes=CONCAT(val,‘, ‘);
23             SET res=CONCAT(res,tempRes);
24         END IF;
25     UNTIL val=-1 END REPEAT; -- 居然MySQL中 用 val=-1 来结束循环，原以为应该和java、c类似，用val==-1来结束。                 
26     -- close cursor
27     CLOSE cursor_avgScore ;
28     -- 显示结果
29     SELECT res;
30     END$$
31 
32 DELIMITER ;

转自：http://blog.csdn.net/u010412719/article/details/51125496

mysql 游标的使用

标签：res   声明   显示   concat   分享   title   finish   alt   stat