一、光标/游标概述
  • 在编写存储过程时，查询可能返回多条记录，如果，数据量非常大，则需要使用光标来逐条读取查询结果集中的记录光标，是一种用于轻松处理多行数据的机制。
  • 光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。
  • 光标只能用于存储过程和函数。
  • 光标的使用步骤：
    • 1.在使用光标前，必须先声明（定义）。
    • 2.声明之后，必须打开游标才可以使用。
    • 3.打开之后，根据需求，使用光标。
    • 4.光标使用完之后，必须关闭光标。
二、光标的声明/创建（declare）

语法格式： 

DECLARE cursor_name CURSOR FOR select_statement;
  • cursor_name：表示光标的名称。
  • select_statement：表示SELECT语句的内容，返回一个用于创建光标的结果集。

演示案例：

  • 在存储过程中使用declare创建了一个名为“ordernumbers”的光标，此光标检索从订单表中检索所有的订单编号。
  • 存储过程处理完成后，光标也就消失了（因为其局限于存储过程）。
delimiter //
create procedure processorders()
begin
    declare ordernumbers cursor for select order_num from orders;
end;
//

 MySQL cursor光标/游标_sql

三、光标的打开与关闭（open、close）

光标的打开（open）

  • 在处理open语句时执行查询，存储检索出的数据以供浏览。
OPEN cursor_name;

光标的关闭（close）

  • 此语句关闭先前打开的光标。
  • 隐含关闭：如果未被明确地关闭光标，那么语句在达到end语句时自动关闭这个光标。
close  cursor_name;

演示案例

  • 重新定义上面的procedure存储过程，在其中使用一个完整的光标。
  • 这个存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没做。
delimiter //
create procedure processorders()
begin
    declare ordernumbers cursor for select order_num from orders;

    open ordernumbers;
    
    close ordernumbers;
end;
//

MySQL cursor光标/游标_数据_02

四、光标的使用（fetch）
  • 在一个光标被打开后，可以使用fetch语句分别访问它的每一行。它还移动光标中的内部行指针，每使用一次fetch光标向后向后移动一行。
  • fetch指定检索什么数据（所需的列），检索出来的数据存储在什么地方。

语法格式：  

FETCH cursor_name INTO var_name [,var_name]……
  • cursor_name：表示参数的名称 。
  • var_name：表示将光标中的SELECT语句查询出来的信息，存入该参数中（var_name必须在声明光标之前就定义好）。

演示案例①：

  • 修改上面的processorders存储过程，将光标查询出的结果（第一行结果）存储在所指的参数中。
delimiter //
create procedure processorders(out o int)
begin
    declare ordernumbers cursor for select order_num from orders;

    open ordernumbers;
    
    fetch ordernumbers into o;

    close ordernumbers;
end;
//
  • 下面是效果图，可以看到光标检索出了第一行的数据，并将其结果存储在参数中。

MySQL cursor光标/游标_sql_03

MySQL cursor光标/游标_sql_04

演示案例②：

  • 我们修改上面的存储过程，使其检索出所有的数据（从第一行到最后一行）。
  • repeat语句：反复执行直到done为真为止（由until done end repeat规定）。为使它起作用，定义了一个默认为0的done变量。
  • “continue handler”语句：它是在条件出现时被执行的代码，这里，它指出sqlstate '02000'出现时，设置done为1（sqlstate '02000'是一个未找到条件，当repeat由于没有更多的行供循环而不能继续时，出现这个条件）。
delimiter //
create procedure processorders()
begin
    declare done boolean default 0;
    declare o int;

    declare ordernumbers cursor for select order_num from orders;

    declare continue handler for sqlstate '02000' set done=1;

    open ordernumbers;
    
    repeat
        fetch ordernumbers into o;
        until done 
    end repeat;

    close ordernumbers;
end;
//

演示案例③：

  • 现在我们修改演示案例②，对其进一步修改。
  • 我们计算每个订单以及订单的总价，在其中调用了另外一个存储过程“ordertotal”(在下面给出了)，然后将结果存放在ordertotals表中。
delimiter //
create procedure ordertotal(in onubmer int,in taxable boolean,out ototal decimal(8,2))
comment 'Obtain order toal,optionally adding tax'
begin
    declare total decimal(8,2);
    declare taxrate int default 6;
 
    select sum(item_price*quantity) from orderitems where order_num=onubmer into total;
    
    if taxable then
        select total+(total/100*taxrate) into total;
     end if;
 
    select total into ototal;
end;
//

delimiter //
create procedure processorders()
begin
    declare done boolean default 0;
    declare o int;
    declare t decimal(8,2);

    declare ordernumbers cursor for select order_num from orders;

    declare continue handler for sqlstate '02000' set done=1;

    create table if not exists ordertotals(
        order_num int,
        total decimal(8,2)
    );

    open ordernumbers;

    repeat
        fetch ordernumbers into o;
        call ordertotal(o,1,t);
        insert into ordertotals(order_num,total) values(o,t);
        until done
    end repeat;

    close ordernumbers;
end;
//

MySQL cursor光标/游标_sql_05

  • 掉后调用该存储过程，并查询ordertotals表的内容。

MySQL cursor光标/游标_cursor_06