游标循环-PL/SQL [英] 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;
/

请您帮我全年在PL/SQL块中运行此游标并进行错误处理(例如:-如果4月1日已有数据，则不应再次将其插入表中，而不会重复)

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)

类似以下内容:-

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;
/

谢谢

推荐答案

您为什么甚至需要pl/sql?

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)
;

但是请注意，在您比较DATE(我已经简单地复制过)时，oracle DATE总是包含一个时间成分，而上面的比较会将您提供的日期截断为午夜.因此，将不会选择b.t_date = to_date('2019-04-02 09:10:11'，'yyyy-mm-dd')的行.

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.

这篇关于游标循环-PL/SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！