在存储过程和函数中,可以使用光标对结果集进行循环的处理。
光标的使用包括光标的生命、OPEN、fetch、和close,其语法如下:
1、声明光标:
declare 光标名 cursor for select_statement(查询语句)
2、open光标:
open 光标名
3、fetch 光标
fetch 光标名 into 变量名...;
4、close光标
close 光标名
以下例子是一个简单的使用光标的过程,对payment 表按照行进行循环的处理,按照staff_id 值的不同累加 amount的值,
判断循环结束的条件是捕获not found 的条件,当fetch 光标找不到下一条记录的时候,就会关闭光标然后推出过程。
CREATE DEFINER=`root`@`::1` PROCEDURE `payment_stat`()
begin
declare i_staff_id int; //定义变量
declare d_amount decimal(5,2);//定义变量
declare cur_payment cursor for select staff_id,amount from payment;//定义光标的声明
declare exit handler for not found close cur_payment;//处理程序的声明
set @x1=0;
set @x2=0;
open cur_payment;
repeat
fetch cur_payment into i_staff_id,d_amount;
if i_staff_id =2 then
set @x1 = @x1 + d_amount;
else
set @x2 = @x2 + d_amount;
end if;
until 0 end repeat;
close cur_payment;
end
payment中staff_id和amount 的部分值如下,方便理解光标的作用。
1 | 4.99 |
2 | 4.99 |
2 | 0.99 |
2 | 3.99 |
2 | 6.99 |
2 | 0.99 |
1 | 0.99 |
2 | 0.99 |
1 | 5.99 |
1 | 4.99 |
1 | 4.99 |
2 | 0.99 |
2 | 2.99 |
2 | 0.99 |
2 | 2.99 |
1 | 7.99 |
2 | 4.99 |
1 | 1.99 |
2 | 6.99 |
2 | 4.99 |
1 | 6.99 |
1 | 0.99 |
1 | 2.99 |
2 | 9.99 |
2 | 2.99 |
注意: 存储过程中定义变量、条件、处理程序、光标都是通过declare 定义的,它们之间是有先后顺序要求的。
变量和条件必须在最前面声明, 然后才是光标的声明,最后才可以是处理程序的声明。