[sql]declare counter1 number := 0; counter2 number := 0; cursor cur_t is SELECT * from emp where e_name = 'Ptere'; --set cursor, a select statement rec_t emp%rowtype; --defines this is a row from the database begin counter1 := 0; counter2 := 0; --some counters to log the records being changed open cur_t; --open cursor loop --begin loop fetch cur_t into rec_t; --fetches cursor row into the rec_t variable exit when cur_t%notfound; UPDATE emp SET e_name = 'Peter' where e_name = 'Ptere'; counter1 := counter1 + 1; --increment updates insert into update_log (record_id, table_name, description) --useful for ref. purposes values (rec_t.emp_id, 'emp', 'Peter'); counter2 := counter2 + 1; --increment inserts commit; end loop; --row_count := sql%rowcount; --sql counter for update statements dbms_output.put_line('EMP: ' || 'name changed, updated: ' || counter1); dbms_output.put_line('insert into UPDATE_LOG: ' || counter2); close cur_t; --close the cursor end; [/sql]
Note for DBMS output we need to set the command prompt:[sql]set serveroutput on size 1000000 [/sql]format wrapped
Comments