oracle 10个表循环10次,Oracle-匿名过程以循环访问多个表(动态)-查询返回多行

只要光标结构相同,就可以使用一些动态引用光标进行循环,例如

SQL> set serverout on

SQL> declare

2 tablist sys.odcivarchar2list :=

3 sys.odcivarchar2list('ALL_OBJECTS','USER_OBJECTS');

4 rc sys_refcursor;

5

6 date_results sys.odcidatelist := sys.odcidatelist();

7 count_results sys.odcinumberlist := sys.odcinumberlist();

8 begin

9 for i in 1 .. tablist.count

10 loop

11 open rc for

12 replace(q'{select trunc(created,'YYYY'), count(*) from @@@ group by trunc(created,'YYYY') order by 1}', '@@@',tablist(i));

13 fetch rc bulk collect into date_results, count_results;

14 close rc;

15

16 dbms_output.put_line(tablist(i));

17 for c in 1 .. date_results.count

18 loop

19 dbms_output.put_line(rpad(date_results(c),20)||lpad(count_results(c),20));

20 end loop;

21 end loop;

22 end;

23 /

ALL_OBJECTS

01-JAN-17 67892

01-JAN-18 6228

USER_OBJECTS

01-JAN-18 1093

PL/SQL procedure successfully completed.