PL/SQL: Cursor for Update Example

set feedback off

create table f (a number, b varchar2(10));

insert into f values (5,'five');
insert into f values (6,'six');
insert into f values (7,'seven');
insert into f values (8,'eight');
insert into f values (9,'nine');

commit;

create or replace procedure wco as
  cursor c_f is 
    select a,b from f where length(b) = 5 for update;
    v_a f.a%type;
    v_b f.b%type;
begin
  open c_f;
  loop
    fetch c_f into v_a, v_b;
    exit when c_f%notfound;
    update f set a=v_a*v_a where current of c_f;
  end loop;

  close c_f;
end;
/

exec wco;

select * from f;

drop table f;
drop procedure wco;

create table numbers_en (
  id_num  number        primary key,
  txt_num varchar2(10)
);

insert into numbers_en values (1, 'one'  );
insert into numbers_en values (2, 'two'  );
insert into numbers_en values (3, 'three');
insert into numbers_en values (4, 'four' );
insert into numbers_en values (5, 'five' );
insert into numbers_en values (6, 'six'  );

create table lang (
   id_lang   char(2) primary key,
   txt_lang  varchar2(10)
);

insert into lang values ('de', 'german');
insert into lang values ('fr', 'french');
insert into lang values ('it', 'italian');

create table translations (
  id_num    references numbers_en,
  id_lang   references lang,
  txt_trans varchar2(10) not null
);

insert into translations values (1, 'de', 'eins'   );
insert into translations values (1, 'fr', 'un'     );
insert into translations values (2, 'it', 'duo'    );
insert into translations values (3, 'de', 'drei'   );
insert into translations values (3, 'it', 'tre'    );
insert into translations values (4, 'it', 'quattro');
insert into translations values (6, 'de', 'sechs'  );
insert into translations values (6, 'fr', 'six'    );

declare

  cursor cur is 
      select id_num,
             txt_num,
             id_lang,
             txt_lang,
             txt_trans
        from numbers_en join translations using(id_num)
                   left join lang         using(id_lang)
    for update of translations.txt_trans;


  rec cur%rowtype;

begin

  for rec in cur loop

    dbms_output.put (
      to_char (rec.id_num         , '999') || ' - ' || 
      rpad    (rec.txt_num        ,   10 ) || ' - ' || 
      rpad(nvl(rec.txt_trans, ' '),   10 ) || ' - ' || 
               rec.id_lang                 || ' - ' || 
      rpad    (rec.txt_lang       ,   10 )
    );

    if mod(rec.id_num,2) = 0 then
      update translations set txt_trans = upper(txt_trans) 
       where current of cur;
       dbms_output.put_line(' updated');
    else
      dbms_output.new_line;
    end if;

  end loop;

end;
/

   6 - six        - sechs      - de - german     updated
   3 - three      - drei       - de - german
   1 - one        - eins       - de - german
   6 - six        - six        - fr - french     updated
   1 - one        - un         - fr - french
   4 - four       - quattro    - it - italian    updated
   3 - three      - tre        - it - italian
   2 - two        - duo        - it - italian    updated

select * from translations;

    ID_NUM ID TXT_TRANS
---------- -- ----------
         1 de eins
         1 fr un
         2 it DUO
         3 de drei
         3 it tre
         4 it QUATTRO
         6 de SECHS
         6 fr SIX