Cursors In PL/SQL

• Tweet
• 

        // How to implement a looping cursor in PL/SQL. I used one very much like this one
// recently to fix incorrectly input 4-year dates (the system would show '0008' rather 
// than '2008'

declare
   old_date date;
   new_date date;
   v_event_key events.event_key%TYPE;
   CURSOR c1 IS select event_key, event_date from events 
       where event_date < to_date('01/01/1000','mm/dd/yyyy');
   
begin
   OPEN c1;
   LOOP
       FETCH c1 INTO v_event_key, old_date;
       EXIT WHEN c1%NOTFOUND;
   
       select add_months(old_date,+24000) into new_date from dual;
       DBMS_OUTPUT.PUT_LINE('old_date ' || to_char(old_date, 'mm/dd/yyyy'));
       DBMS_OUTPUT.PUT_LINE('new_date ' || to_char(new_date, 'mm/dd/yyyy'));
       update events set event_date = new_date where event_key = v_event_key;
   END LOOP;
   CLOSE c1;

end;

Tags:
• Oracle
• plsql