游标(cursor)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处理。MySql存储过程也提供了对异常处理的功能:通过定义HANDLER来完成异常声明的实现。

游标

光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE。

-- 声明语法
declare cursor_name cursor for select_statement

-- 打开语法
open cursor_name

-- 取值语法
fetch cursor_name into var_name [, var_name] ...

-- 关闭语法
close cursor_name

注意:下面的操作演示的数据在前面的章节,也可见附录。

-- ------------------游标-------------------
use mydb7_procedure;
-- 需求:输入一个部门名。查询该部门员工的编号、姓名、薪资,将查询的结果集添加到游标
delimiter $$
CREATE PROCEDURE proc19_cursor(in in_name VARCHAR(50))
BEGIN
    -- 定义局部变量
    declare var_empno int;           -- 存储编号
    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_name;

    -- 打开游标
    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;
    -- 这是一个死循环
    -- 退出是因为报错1329 - No data - zero rows fetched, selected, or processed
    -- 而导致退出
    -- 这个报错可在后面的介绍如何解决,使用句柄

    -- 关闭游标
    close my_cursor;

END$$;
delimiter ;

call proc19_cursor('销售部');

报错

异常处理

语法:

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE  -- 继续向下走
  | EXIT      -- 退出
  | UNDO      -- 取消,基本不考虑
  -- 同个句柄里,上面三个只能选一个
}

condition_value: {
    mysql_error_code   -- 错误码
  | condition_name     -- 条件的名字:SQLWARNING、SQLWARNING、SQLEXCEPTION
  | SQLWARNING
  | SQLWARNING
  | SQLEXCEPTION
}

案例:

-- ------------------异常处理-------------------
use mydb7_procedure;
-- 需求:输入一个部门名。查询该部门员工的编号、姓名、薪资,将查询的结果集添加到游标
delimiter $$
CREATE PROCEDURE proc21_cursor_handler(in in_name VARCHAR(50))
BEGIN
    -- 定义局部变量
    declare var_empno int;           -- 存储编号
    declare var_ename varchar(50);   -- 存储姓名
    DECLARE var_sal decimal(7,2);  -- 存储薪资

    -- 定义标记值
    declare flag int default 1;

    -- 定义句柄:定义异常的处理方式
    /*
        1: 异常处理完之后程序该怎么执行
                    continue :继续执行剩余代码、
                    exit     :直接终止程序
                    暂不支持 undo

          2:触发条件
                        条件码:1329、......
                      QLWARNING
                        SQLWARNING
                        SQLEXCEPTION

            3.异常触发最后执行的代码
                设置flag的值 ---》0
    */

    -- 声明游标
    declare my_cursor cursor for 
        select empno,ename,sal
        from dept a,emp b
        where a.deptno = b.deptno
           and a.dname = in_name;

  -- 设置句柄
    /*
        DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
        */
    declare continue handler 
    for 1329
    set flag = 0;


    -- 打开游标
    open my_cursor;

    -- 通过游标取值
    label:loop
        fetch my_cursor into var_empno, var_ename, var_sal;
        -- 判断flag,如果flag的值为1,则执行,否则不执行
        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('销售部');

数据附录

-- 准备数据
create database if not exists mydb6_view;
use mydb6_view;
create table dept(
    deptno int primary key,
  dname varchar(20),
    loc varchar(20)
);
insert into dept values(10, '教研部','北京'),
(20, '学工部','上海'),
(30, '销售部','广州'),
(40, '财务部','武汉');

create table emp(
    empno int primary key,
    ename varchar(20),
    job varchar(20),
    mgr int,
    hiredate date,
    sal numeric(8,2),
    comm numeric(8, 2),
    deptno int,
--  FOREIGN KEY (mgr) REFERENCES emp(empno),
    FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE
);
insert into emp values
(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),
(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),
(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, null, 20),
(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),
(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 30),
(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),
(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, null, 20),
(1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),
(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),
(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, null, 20),
(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),
(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),
(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);

create table salgrade(
    grade int primary key,
    losal int,
    hisal int
);
insert into salgrade values
(1, 7000, 12000),
(2, 12010, 14000),
(3, 14010, 20000),
(4, 20010, 30000),
(5, 30010, 99990);

声明:内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》,博客内容仅作学习参考使用。



版权声明 ▶ 本网站名称:陶小桃Blog
▶ 本文链接:https://www.52txr.cn/2022/mysql22.html
▶ 本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长进行核实删除。
▶ 转载本站文章需要遵守:商业转载请联系站长,非商业转载请注明出处!!
▶ 站长邮箱 [email protected][email protected] ,如不方便留言可邮件联系。

最后修改:2022 年 06 月 05 日
如果觉得我的文章对你有用,请随意赞赏