使用游标

1.什么是游标

游标是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被该语句所检索出来的结果集。

2.使用游标

在介绍如何创建游标之前，先说明下如何使用游标。

使用游标涉及几个明确的步骤。

1、在能够使用游标前，必须先定义它。这个过程实际上是没有检索数据的，它只是定义要使用的select语句。

2、一旦你定义了游标后，必须打开游标以供使用。这个过程用前面定义的select语句把数据实际检索出来。即这个步骤之后，我们就可以操作游标中的数据了。

3、对于有数据的游标，根据需要取出各行的数据来进行一定的操作。

4、使用完游标后，一定要关闭游标。

3.创建游标

准备数据:

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test`.`student` (`id`, `name`) VALUES ('1', '张三');
INSERT INTO `test`.`student` (`id`, `name`) VALUES ('2', '李四');
INSERT INTO `test`.`student` (`id`, `name`) VALUES ('3', '王五');

创建一个游标的语法如下：

用declare 来定义，具体如下：

DELIMITER $$
CREATE 
		PROCEDURE test.student()
		BEGIN
			DECLARE id CURSOR
			FOR
			SELECT * FROM student;
		END$$
DELIMITER;

上面是定义游标的一般形式，当我们定义了一个游标之后，我们就可以来打开它、使用它、关闭它。

打开游标：

open cursor_name;

关闭游标：

close cursor_name;

使用游标：
使用游标用fetch来取出数据，例如：fetch cursor_name in variable;//取出游标所指示的数据给局部变量variable

下面这个例子就是演示了创建一个游标，打开游标及关闭游标，但没有对游标所指示的数据进行数据。

DELIMITER $$
CREATE
	PROCEDURE test.p_cursor()
	BEGIN
		DECLARE id CURSOR
		FOR
		SELECT * FROM student;
		OPEN id;
		CLOSE id;
	END$$
DELIMITER;

实例：操作游标中的数据

需求是这样，我们手上有一个student表，表中的结构和数据如下：

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `mathScore` int(11) DEFAULT NULL,
  `englishScore` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`student` (`id`, `name`, `mathScore`, `englishScore`) VALUES ('1', '张三', '89', '45');
INSERT INTO `test`.`student` (`id`, `name`, `mathScore`, `englishScore`) VALUES ('2', '李四', '24', '76');
INSERT INTO `test`.`student` (`id`, `name`, `mathScore`, `englishScore`) VALUES ('3', '王五', '45', '76');
INSERT INTO `test`.`student` (`id`, `name`, `mathScore`, `englishScore`) VALUES ('4', '打打', '43', '45');

现在我们需要将student表中所有学生的的平均成绩(mathScore+englishScore的一半)合成一行，用逗号’,’隔开。

对于这样一个需求，下面我们尝试用游标来实现。

BEGIN
    -- declare some variable，必须在声明游标和句柄之前，而声明句柄必须在声明游标之后。
    DECLARE val DOUBLE DEFAULT 0;
    DECLARE tempRes VARCHAR(10) DEFAULT '';
    DECLARE res VARCHAR(100) DEFAULT '' ;
    -- declare a cursor
    DECLARE cursor_avgScore CURSOR
    FOR
    SELECT (mathScore+englishScore)/2  AS student_avgScore FROM student;
    -- declare a continue handler ,use finish while loop 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'  SET val= -1.0 ;  
    -- open cursor
    OPEN cursor_avgScore ;
    FETCH cursor_avgScore INTO val;
    -- fetch cursor
    WHILE val!=-1 DO
        SET tempRes=CONCAT(val,', ');
        SET res=CONCAT(res,tempRes);
        FETCH cursor_avgScore INTO val;
    END WHILE;                  
    -- close cursor
    CLOSE cursor_avgScore ;
    -- 显示结果
    SELECT res;
    END

查询：

call procedure_student();

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

DELIMITER $$

CREATE
    PROCEDURE `test`.`procedure_student_v1`() 
    BEGIN
    -- declare some variable，必须在声明游标和句柄之前，而声明句柄必须在声明游标之后。
    DECLARE val DOUBLE DEFAULT 0;
    DECLARE tempRes VARCHAR(10) DEFAULT '';
    DECLARE res VARCHAR(100) DEFAULT '' ;
    -- declare a cursor
    DECLARE cursor_avgScore CURSOR
    FOR
    SELECT (mathScore+englishScore)/2  AS student_avgScore FROM student;
    -- declare a continue handler ,use finish while loop 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'  SET val= -1.0 ;  
    -- open cursor
    OPEN cursor_avgScore ;  
    -- fetch cursor
    REPEAT  
        FETCH cursor_avgScore INTO val;
        IF val!=-1 THEN
            SET tempRes=CONCAT(val,', ');
            SET res=CONCAT(res,tempRes);
        END IF;
    UNTIL val=-1 END REPEAT; -- 居然MySQL中 用 val=-1 来结束循环，原以为应该和java、c类似，用val==-1来结束。                 
    -- close cursor
    CLOSE cursor_avgScore ;
    -- 显示结果
    SELECT res;
    END$$

DELIMITER ;