示範如何使用 Cursor For Update

fund_less08_demo_02.sql

/*
  示範如何使用 Cursor For Update
  Topic: Oracle PL/SQL Fund Lesson 8: Explicit Cursor
*/

-- Create the required table
create table emp5 as 
    select employee_id, first_name, last_name, salary 
        from employees;
alter table emp5 add (new_salary number);
        
select * from emp5;

-- Update by cursor-for loop
set serveroutput on
declare
  -- 需要加入 for update 防止其它 session 修改 table, 使得 Active Set 
  -- 和 Table 的資料不一致
  cursor c_emp5 is select * from emp5 for update;
begin
for emp_rec in c_emp5 loop
    -- Update the record
    emp_rec.new_salary := emp_rec.salary * 1.1;
    dbms_output.put_line('New Salary: ' || emp_rec.new_salary);
    -- Update the table by the record for the current row
    update emp5 
    set row = emp_rec
    where current of  c_emp5;  -- 參考目前的 row
end loop;
end;
/