MySQL游标简介

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL游标，MySQ存储过程和函数与游标结合使用，可以实现复杂的业务逻辑，当然MySQL游标整体性能还有待提高。

语法:

-- 声明游标
DECLARE cursor_name CURSOR FOR select_statement
-- 打开游标
OPEN cursor_name
-- 获取游标
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
-- 关闭游标
CLOSE cursor_name
复制

以一个案例来看MySQL游标的使用方法:
需求:
员工表emp，需要对奖金进行调整
1.奖金为空的，改为500
2.奖金为0的，不做处理
3.奖金大于0的，增加1000

delimiter //

create procedure test1()
begin
    declare l_add_bonus1 int default 1000;
    declare l_add_bonus2 int default 500;
    declare l_empno int;
    declare l_sal,l_bonus decimal(15,2);
    
    -- 游标结束的标志
    declare done int default 0;
    
    -- 声明游标
    declare emp_cur cursor for select empno,sal,comm from emp;
    
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done =1;

    -- 打开游标
    open emp_cur;
    
    -- 开始loopU型内环
    e_loop:loop
    
    -- 根据游标 当前指向一条数据
    fetch emp_cur into l_empno,l_sal,l_bonus;

    -- 当 游标的返回值为 1 时 退出 loop循环 
    if done = 1 then
            leave e_loop;
    end if;

    -- 如果奖金为空加500，如果奖金为0，不加，其它情况下加1000
    if l_bonus is null then
       update emp set comm = l_add_bonus2 where empno = l_empno;
    elseif l_bonus = 0 then
       begin
       end;
    else
        update emp set comm = l_add_bonus1 + comm where empno = l_empno;
    end if;

    end loop;
    -- 关闭游标
    close emp_cur;
 end//

 delimiter ;
复制

执行结果如下:

mysql> delimiter //
mysql>
mysql> create procedure test1()
    -> begin
    ->     declare l_add_bonus1 int default 1000;
    ->     declare l_add_bonus2 int default 500;
    ->     declare l_empno int;
    ->     declare l_sal,l_bonus decimal(15,2);
    ->
    ->     -- 游标结束的标志
    ->     declare done int default 0;
    ->
    ->     -- 声明游标
    ->     declare emp_cur cursor for select empno,sal,comm from emp;
    ->
    ->     -- 指定游标循环结束时的返回值
    ->     declare continue handler for not found set done =1;
    ->
    ->     -- 打开游标
    ->     open emp_cur;
    ->
    ->     -- 开始loopU型内环
    ->     e_loop:loop
    ->
    ->     -- 根据游标 当前指向一条数据
    ->     fetch emp_cur into l_empno,l_sal,l_bonus;
    ->
    ->     -- 当 游标的返回值为 1 时 退出 loop循环
    ->     if done = 1 then
    ->             leave e_loop;
    ->     end if;
    ->
    ->     -- 如果奖金为空加500，如果奖金为0，不加，其它情况下加1000
    ->     if l_bonus is null then
    ->        update emp set comm = l_add_bonus2 where empno = l_empno;
    ->     elseif l_bonus = 0 then
    ->        begin
    ->        end;
    ->     else
    ->         update emp set comm = l_add_bonus1 + comm where empno = l_empno;
    ->     end if;
    ->
    ->     end loop;
    ->     -- 关闭游标
    ->     close emp_cur;
    ->  end//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  delimiter ;
mysql>
mysql>
mysql>
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-06-13 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql>
mysql> call test1();
Query OK, 0 rows affected (0.04 sec)

4 rows in set (0.00 sec)

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |  500.00 |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 | 1300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 | 1500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |  500.00 |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 2400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |  500.00 |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |  500.00 |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-06-13 | 3000.00 |  500.00 |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |  500.00 |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | 1100.00 |  500.00 |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |  500.00 |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |  500.00 |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |  500.00 |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

复制