带参数的游标

  declare
   --定义游标, 因为需要对游标定义的数据进行修改操作, 所以添加了 for update 子句
   cursor sal_cursor(dept_id number) is 
   select salary sal 
   from employees 
   where department_id = dept_id 
   for update;
   
   --定义调整工资的基数变量
   v_temp number(3,2);


begin
   --使用游标 for 循环，dept_id=>80这是赋值，下面只对80号部门操作
   for v_sal_rec in sal_cursor(dept_id=>80) loop
         
         if v_sal_rec.sal <= 5000 then
            v_temp := 0.05;
         elsif v_sal_rec.sal <= 10000 then
            v_temp := 0.03;
         elsif v_sal_rec.sal <= 15000 then
            v_temp := 0.02;   
         else
            v_temp := 0.01;
         end if;
         
         --执行修改操作, 因为定义游标时使用了 for update 子句, 
         --所以修改状态下可以使用 where current of cursor_name 来锁定当前行;
         update employees set salary = salary * (1 + v_temp) where current of sal_cursor;
         
   end loop;
   
end;

==============================================================================================================

declare
cursor emp_cursor is select * from employees;
v_emp_record employees%rowtype;
begin
    open emp_cursor;
    fetch emp_cursor into v_emp_record;
    while emp_cursor%found loop
 --ceil(n) 取大于等于数值n的最小整数
 --decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)  
          update employees set salary =v_emp_record.salary + v_emp_record.salary * decode(ceil(v_emp_record.salary / 5000), 1, 0.05,
                                                                            2, 0.03,
                                                                            3, 0.02,
                                                                            0.01) where employee_id=v_emp_record.employee_id;


          dbms_output.put_line(v_emp_record.employee_id);
          
          fetch emp_cursor into v_emp_record;
    end loop;
    close emp_cursor;
end;