CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
Sergey
Sergey

Reputation: 128

Oracle procedure changing dates in multiple columns

Trying to create a procedure that changes dates when the end date is less than sysdate, but it changes all the dates, so here is the code:

CREATE OR REPLACE PROCEDURE CHANGE_DATES 
as 

BEGIN
    FOR rec IN (SELECT start_period_date,end_period_date FROM mytable) LOOP
    DBMS_OUTPUT.PUT_LINE('Old Date:' || rec.start_period_date ||' ' || rec.end_period_date);
    IF rec.end_period_date < sysdate THEN
        update mytable set start_period_date = sysdate;
        update mytable set end_period_date = sysdate + 6;
        commit;
        --DBMS_OUTPUT.PUT_LINE('New Date:' || begin_date ||' ' ||end_date);
    end if;
END LOOP;
END;

Upvotes: 0

Views: 39

Answers (1)

MT0
MT0

Reputation: 168416

You need a WHERE clause (and you can do it all in one UPDATE statement):

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE mytable ( start_period_date, end_period_date ) AS
SELECT DATE '2018-01-01', DATE '2018-01-02' FROM DUAL
/

CREATE OR REPLACE PROCEDURE CHANGE_DATES 
AS
  start_dates SYS.ODCIDATELIST;
  end_dates   SYS.ODCIDATELIST;
BEGIN
  UPDATE ( SELECT m.*,
                  ( SELECT m.start_period_date FROM DUAL ) AS old_start_date,
                  ( SELECT m.end_period_date   FROM DUAL ) AS old_end_date
           FROM mytable m )
  SET   start_period_date = SYSDATE,
        end_period_date   = SYSDATE + INTERVAL '6' DAY
  WHERE end_period_date   < SYSDATE
  RETURNING old_start_date, old_end_date
  BULK COLLECT INTO start_dates, end_dates;

  FOR i IN 1 .. start_dates.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( 'Old Date:' || start_dates(i) ||' ' || end_dates(i) );
  END LOOP;

  -- Do not COMMIT in the procedure, COMMIT in the calling scope.
END;
/

Query 1:

BEGIN
  CHANGE_DATES;
  COMMIT;
END;

Query 2:

SELECT * FROM mytable

Results:

|    START_PERIOD_DATE |      END_PERIOD_DATE |
|----------------------|----------------------|
| 2018-05-21T09:06:05Z | 2018-05-27T09:06:05Z |

Upvotes: 1

Related Questions