MySQL游标异常：每次迭代仅取最后一行薪资，全表等级被统一更新

问题背景 #

需求为根据指定薪资区间为员工划分薪资等级：

• sal<1000 对应等级'D'
• 1000≤sal≤3000 对应'C'
• 3000<sal≤5000 对应'B'
• sal>5000 对应'A'

现有employee表数据如下：

+-------+---------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME   | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+---------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH   | CLERK     | 7902 | 1980-12-17 | 1040.00 |    NULL |     20 |
|  7499 | ALLEN   | SALESMAN  | 7698 | 1981-02-20 | 3440.00 |  300.00 |     30 |
|  7521 | WARD    | SALESMAN  | 7698 | 1981-02-22 | 2687.50 |  500.00 |     30 |
|  7566 | JONES   | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  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 | 1982-12-09 | 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 |
|  7902 | FORD    | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER  | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  1501 | swapnil | MANAGER   | NULL | 1989-05-22 | 5050.00 |    1000 |     20 |
+-------+---------+-----------+------+------------+---------+---------+--------+

创建的grading_sal存储过程如下：

delimiter $$
create procedure grading_sal()
begin
    declare psal decimal(9,2) default 0;
    declare pgrade varchar(2); 
    declare v_stop int default 0;
    declare pempno int default 0;
    declare empcur cursor for select sal
    from employee;
    declare continue handler for NOT FOUND
    set v_stop=1;
    alter table employee
    add column grade varchar(2);
open empcur;
lable1: loop
    fetch empcur into psal, pempno;
if(v_stop=1)
then
    leave lable1;
end if;
if psal<1000 
then
    set pgrade = 'D';
elseif psal<2999
then
    set pgrade = 'C';
elseif psal<4999
then
    set pgrade = 'B';
else
    set pgrade = 'A';
end if;
    update employee
    set grade = pgrade;
end loop;
    select empno,ename,hiredate,sal,grade,deptno
    from employee
    where deptno=10;
close empcur;
end $$
delimiter ;

执行后出现异常：所有员工的grade字段被统一设置为最后一次计算的等级，错误输出如下：

mysql> call grading_sal()
+-------+--------+------------+---------+-------+--------+
| empno | ename  | hiredate   | sal     | grade | deptno |
+-------+--------+------------+---------+-------+--------+
|  7782 | CLARK  | 1981-06-09 | 2450.00 | A     |     10 |
|  7839 | KING   | 1981-11-17 | 5000.00 | A     |     10 |
|  7934 | MILLER | 1982-01-23 | 1300.00 | A     |     10 |
+-------+--------+------------+---------+-------+--------+
3 rows in set (0.08 sec)

错误原因分析 #

1. 游标查询字段不完整：游标仅查询了sal字段，但fetch语句却试图将结果赋值给psal和pempno，导致pempno始终为初始默认值0，无法定位到具体员工。
2. Update语句无过滤条件：每次循环执行update employee set grade = pgrade时，没有指定where empno = pempno，会将所有员工的grade字段更新为当前循环计算的pgrade，最终所有员工的等级都会被覆盖为最后一条记录的计算结果。
3. 薪资区间判断逻辑错误：需求中1000-3000对应等级'C'，但代码中判断条件为psal<2999，会漏掉2999到3000的区间；3000-5000对应'B'，代码中psal<4999同样漏掉了4999到5000的区间，与需求不符。
4. 重复添加字段风险：每次调用存储过程都会执行alter table add column grade，第二次调用时会因字段已存在而报错。

解决方法 #

方法一：修复存储过程 #

修正上述所有问题后的存储过程代码：

delimiter $$
create procedure grading_sal()
begin
    declare psal decimal(9,2) default 0;
    declare pgrade varchar(2); 
    declare v_stop int default 0;
    declare pempno int default 0;
    -- 游标同时查询empno和sal，用于定位员工
    declare empcur cursor for select empno, sal from employee;
    declare continue handler for NOT FOUND set v_stop=1;
    
    -- 先判断grade字段是否存在，不存在才添加
    if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='employee' and column_name='grade') then
        alter table employee add column grade varchar(2);
    end if;

open empcur;
lable1: loop
    fetch empcur into pempno, psal; -- 对应游标查询的字段顺序
    if(v_stop=1) then
        leave lable1;
    end if;
    
    -- 修正薪资区间判断逻辑，匹配需求
    if psal < 1000 then
        set pgrade = 'D';
    elseif psal <= 3000 then
        set pgrade = 'C';
    elseif psal <= 5000 then
        set pgrade = 'B';
    else
        set pgrade = 'A';
    end if;
    
    -- 添加where条件，仅更新当前员工的grade
    update employee set grade = pgrade where empno = pempno;
end loop;

    select empno,ename,hiredate,sal,grade,deptno from employee where deptno=10;
close empcur;
end $$
delimiter ;

方法二：使用更高效的无游标方案（推荐） #

MySQL中游标效率较低，直接使用UPDATE结合CASE WHEN语句即可完成需求，无需存储过程：

-- 先判断并添加grade字段
if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='employee' and column_name='grade') then
    alter table employee add column grade varchar(2);
end if;

-- 批量更新薪资等级
update employee
set grade = case
    when sal < 1000 then 'D'
    when sal <= 3000 then 'C'
    when sal <= 5000 then 'B'
    else 'A'
end;

-- 查询验证结果
select empno,ename,hiredate,sal,grade,deptno from employee where deptno=10;

内容的提问来源于stack exchange，提问作者Swapnil Gadave