CODEWITHSUNDEEP
oracle-databaseplsql
RLearner
RLearner

Reputation: 65

Loop for a cursor - PL/SQL

I am working on analyzing huge set of data over a year. The approach is to pick the data one day at a time with the help of a cursor and keep on feeding another table with whole year data :-


declare
  i_start_date  date := date '2019-04-01';
  i_end_date    date := date '2019-04-02';
begin  
  for cur_r in (select a.id, b.status
                from table1 a join table2 b on a.msg_id = b.msg_id
                where b.t_date between i_start_date and i_end_date
               )
  loop
    insert into test_table (id, status)
      values (cur_r.id, cur_r.status);
  end loop;
end;
/

Could you please help me run this cursor in a PL/SQL block for the whole year with error handling (e.g:- if data is already there for Apr 01 it should not be inserted again in the table creating no duplicates)

Something like below:-

declare
  i_start_date  date := date '2019-01-01'; --start date set
  i_end_date    date := date '2019-12-31'; --end date set
begin

for i_start_date<=i_end_date --condition to fetch data & insert
  (for cur_r in (select a.id, b.status
                from table1 a join table2 b on a.msg_id = b.msg_id
                where b.t_date = i_start_date
               )
  loop
    insert into test_table (id, status)
      values (cur_r.id, cur_r.status);
  end loop;)

  i_start_date+1 -- increment start date


end;
/

Thanks,

Upvotes: 0

Views: 499

Answers (3)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

You can directly use insert into <table> select ... statement as

SQL> insert into test_table
     select a.id, b.status
       from table1 a
       join table2 b
         on a.msg_id = b.msg_id
      where b.t_date >= trunc(sysdate) - interval '1' year
        and not exists ( select 0 from test_table t where t.id = a.id );

SQL> commit;

through use of b.t_date >= trunc(sysdate) - interval '1' year starting from the one year before to the current day.

If you need to start with a certain date such as date'2019-04-01' and scan for upcoming one year period,

then use b.t_date between date'2019-04-01' and date'2019-04-01' + interval '1' year - 1

and exclude the already existing data within the test_table through

not exists ( select 0 from test_table t where t.id = a.id ) considering those id columns are unique or primary keys in their respective tables.

Upvotes: 0

Fernando Gonzalez
Fernando Gonzalez

Reputation: 183

If you have a Primary Key with the date value you can handle the exception with dup_val_on_index and then use a return. 

BEGIN
  ...
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
     ...
     RETURN;
END;

Or you can use a MERGE to command when to insert or not.

MERGE INTO TEST_TABLE T
  USING CUR_R C
  ON (C.DATE = T.DATE)
WHEN NOT MATCHED THEN
  INSERT (id, status)
  values (cur_r.id, cur_r.status);

Upvotes: 0

EdStevens
EdStevens

Reputation: 3872

Why do you even need pl/sql?

insert into test_table (id, 
                        status
                        )
      values (select a.id, 
                     b.status
              from table1 a 
              join table2 b on a.msg_id = b.msg_id
              where b.t_date between date '2019-04-01
                                 and date '2019-04-02'
                and b.t_date not in (select t_date
                                     from status)
;

But beware in your comparison of DATEs (which I have simply replicated) that oracle DATE always includes a time component, and the above comparison will truncate your supplied dates to midnight. Thus, a row with b.t_date = to_date('2019-04-02 09:10:11','yyyy-mm-dd') will not be selected.

Upvotes: 2

Related Questions