declare cursor o isselect distinct to_char(report_date,'yyyymmdd') report_date from My_test_DAY_REPORT where report_date >=to_date(20130501,'yyyymmdd') order by 1; riqi o%rowtype;begin open o; loop fetch o into riqi; exit when o%notfound;dbms_output.put_line(riqi.report_date); delete from tmp_no_rijie where run_date=to_date(riqi.report_date, 'yyyymmdd'); commit; insert into tmp_no_rijie select t1.org_code , t1.org_name , t1.org_type_id , t1.org_type_name , t1.sup_org_code , t2.org_name , t1.usable , to_date(riqi.report_date, 'yyyymmdd') from sys_org t1, (select org_name, org_code from sys_org) t2 where org_type_id = 18 and usable = 1 and t1.sup_org_code = t2.org_code and t1.org_code in (130002, 150002, 150057, 150059, 150058, 140023, 140001, 110001, 170006, 170002, 180003, 140024) and t1.org_code not in (select org_code from My_test_DAY_REPORT WHERE report_date = to_date(riqi.report_date, 'yyyymmdd')); commit;end loop;close o;end;/
Select run_date as date, org_code as organization, org_name as organization name, 'day not completed 'from tmp_no_rijie order by 1;
Oracle pl_ SQL uses a cursor to retrieve numbers cyclically