MySQL的存储过程——游标、句柄-阿里云开发者社区

MySQL的存储过程——游标、句柄

2022-07-07 33

简介： MySQL的存储过程——游标、句柄

+关注继续查看

游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。

光标的使用包括光标的声明、OPEN（open）、FETCH（fetch） 和 CLOSE（close）.

-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name

 
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
 -- 定义局部变量
 declare var_empno varchar(50);
 declare var_ename varchar(50);
 declare var_sal  decimal(7,2);
 
 -- 声明游标
 declare my_cursor cursor for
  select empno , ename, sal 
    from  dept a ,emp b
    where a.deptno = b.deptno and a.dname = in_dname;
    
    -- 打开游标
  open my_cursor;
  -- 通过游标获取每一行数据
  label:loop
        fetch my_cursor into var_empno, var_ename, var_sal;
        select var_empno, var_ename, var_sal;
    end loop label;
    
    -- 关闭游标
    close my_cursor;
end
 
 -- 调用存储过程
 call proc20_cursor('销售部');

游标和循环使用，特别是loop，一般可以不加条件也可以取完值

我们发现，这个可以运行成功，但是却出现了异常的警告，虽然不影响效果实现，作为精益求精的MySQL，怎么可以允许了，于是我们就创造了句柄

MySql存储过程也提供了对异常处理的功能：通过定义HANDLER（handler）来完成异常声明的实现.

官方文档：MySQL :: MySQL 5.7 Reference Manual :: 13.6.7.2 DECLARE ... HANDLER Statement

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
 
handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}
 
condition_value: {
    mysql_error_code
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

特别注意：

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

 
drop procedure if exists proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
  -- 定义局部变量
    declare var_empno int;
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    
    declare flag int default 1; -- ---------------------
    
    -- 声明游标
    declare my_cursor cursor for
        select empno,ename,sal
        from dept a, emp b
        where a.deptno = b.deptno and a.dname = in_dname;
    
    -- 定义句柄，当数据未发现时将标记位设置为0
    declare continue handler for NOT FOUND set flag = 0;   
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno, var_ename,var_sal;
        -- 判断标志位
        if flag = 1 then
            select var_empno, var_ename,var_sal;
        else
            leave label;
        end if;
    end loop label;
    
    -- 关闭游标
    close my_cursor;
end $$;
 
delimiter ;
call proc21_cursor_handler('销售部');

声明游标的固定格式：declare my_cursor cursor for 加mysql查询语句

其实这个代码可以套用，对于游标的应用场景