oracle - 为员工分配等级的 PL/SQL 程序

Question

create or replace procedure p2
as
    CURSOR c1 IS
      SELECT salary
      FROM   employee1
      FOR UPDATE;
BEGIN
FOR employee_rec IN c1 LOOP
exit when c1%notfound;
if salary>20000 then
update employee1 set grade='A' WHERE  CURRENT OF c1;
if salary>15000 then
update employee1 set grade='B' WHERE  CURRENT OF c1;
if salary>10000 then
update employee1 set grade='C' WHERE  CURRENT OF c1;
if salary<10000 then
update employee1 set grade='D' WHERE  CURRENT OF c1;
end if;
end if;
end if;
end if;
END LOOP;
END p2;

未声明薪水时出现错误如何使此代码更好地获得所需的输出？

标签： oraclestored-proceduresplsqlsql-updatedatabase-cursor

Answer 1

为什么不简单地这样：

update employee1 set grade= 
case 
   when salary>20000 then 'A'
   when salary>15000 then 'B'
   when salary>10000 then 'C'
   when salary<10000 then 'D'
   ELSE grade -- keep existing grade value
end;