mysql游标

【1】游标简介
游标，又称光标。实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。游标初始位置在before first，使用的时候是先移到下一行，然后再去数据。

概括来讲，SQL的游标是一种临时的数据库对象，即可以用来存放在数据库表中的数据行副本，也可以指向存储在数据库中的数据行的指针。游标提供了在逐行的基础上操作表中数据的方法。

游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。

大部分程序数据设计语言都能使用游标来检索SQL数据库中的数据，在程序中嵌入游标和在程序中嵌入SQL语句相同

游标(cursor)的特性：

① 只读的，不能更新的。
② 不滚动的
③ 不敏感的，不敏感意为服务器可以或不可以复制它的结果表

游标(cursor)必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。

【2】游标实例
以下为一个存储过程实例，用到了游标和事务及异常处理。

CREATE PROCEDURE `my_cursor1`(out o_result int)

zero_back:BEGIN -- begin 标号 zero_back
    #Routine body goes here...
    DECLARE p_age int; -- 声明变量
    DECLARE p_count int;

    -- 声明游标结束判断变量，默认值为FALSE;
    DECLARE fetchSeqOk boolean DEFAULT FALSE;
    DECLARE my_cursor CURSOR for select age FROM t_user where id < 4; -- 定义游标

    //游标执行结束时将会set fetchSeqOk = true
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
    set fetchSeqOk = true;
    -- 在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，
    -- 所以在上面代码中指定了当引发not found错误时定义一个continue 的事件 

    -- 定义sql异常执行语句
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK; -- 如果有异常，进行事务回滚；如果调用存储过程的service，已经进行了事务处理，那么该存储过程不需要再重复开启事务
        set o_result = -500;
    end;

    select count(*) into p_count from t_user where id <4;

    IF p_count < 1 THEN
        SET o_result = -1;
        LEAVE zero_back; -- 如果表中无数据，直接跳出函数并设置o_result为-1; 
    END IF;

    OPEN my_cursor;-- 打开游标

       START TRANSACTION;-- 开启事务，如果service里面已经开启了事务，那么存储过程中不需要再开事务；事务要在循环外面开启！！！

         loop_cursor:LOOP

                fetch my_cursor into p_age; -- 游标改变位置指向下一行，取下一行数据，初始位置在before first row

                IF fetchSeqOk THEN
                    LEAVE loop_cursor;
                END IF;
            -- fetch my_cursor into p_age; 此处位置不对，将会重复插入游标最后一个值     

                insert into t_user (age) VALUES(p_age);
                -- 现在游标行位置未变

        END LOOP loop_cursor;
        -- 如果给loop增加了标号，那么请使用END LOOP loop_cursor;进行循环关闭。否则直接END LOOP

        COMMIT; -- 正常执行完，commit

        set o_result = 0;

    CLOSE my_cursor;    -- 关闭游标

END

【3】注意事项
上面示例已经演示了游标的定义，打开，读取值和关闭游标，其中用到了loop循环和事务以及异常处理。

这里说明上面示例中需要注意事项：

① 事务开启关闭相对应，缺一不可；如果调用存储过程的service使用了事务，那么该存储过程中不用开启事务；事务尽量尽量在循环外面。

② fetch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。

DECLARE CONTINUE HANDLER FOR NOT FOUND set fetchSeqOk = true;
    -- 在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，
    -- 所以在上面代码中指定了当引发not found错误时定义一个continue 的事件

③ 定义异常处理

DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 定义sql异常
    BEGIN
        ROLLBACK; -- 如果有异常，进行事务回滚；如果调用存储过程的service，已经进行了事务处理，那么该存储过程不需要再重复开启事务
        set o_result = -500;
    end;

④ 标号

标号可以用在begin，repeat，while或者loop语句前，语句标号只能在合法的语句前面使用。

可以跳出循环，使运行指令达到复合语句的最后一步。

⑤ 游标使用必须打开和关闭

⑥ loop的关闭两种形式

loop

...///

end loop;

or

loop_LABLES:loop

...//

end loop loop_LABLES;

⑦ 声明游标

DECLARE cursor_name CURSOR FOR select_statement

这个语句声明一个光标。也可以在子程序中定义多个光标，
但是一个块中的每一个光标必须有唯一的名字。
SELECT语句不能有INTO子句。

⑧ 关闭游标

CLOSE cursor_name

这个语句关闭先前打开的光标。

如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。