Oracle PL / SQL交换for动态SQL的for循环,帮助进行语法更改

 收藏

我在这里有一个for循环:

BEGIN

for rws in (

select /*+parallel (4)*/ a.owner,a.table_name, round(sum(b.sum_bytes),2) TOT_OBJECT_SIZE_MB, EST_ONE_ROW_MB
from dba_tables@DB1 a, MV_PRD_SEG_DATA b
where a.table_name = b.segment_name
and a.table_name in
(select table_name from MV_PDU_TABLE where driver_table is null)
and a.owner in (select distinct productionschema from MV_PDU_TABLE c)
group by a.owner,a.table_name
order by a.table_name
)
loop

通过引用rws,将数据按如下所示退出循环。 :

execute immediate' select /*+parallel (4)*/ count(*) from ' ||rws.owner||'.'||rws.table_name || '@' || dblink into TOTAL_ROW_COUNT;

现在,我必须将select语句中的一些硬编码表引用更改为变量。 (我将在代码的前面声明这些。)

据我了解,我现在必须切换到动态SQL。

这样,您可以在此处看到如何将硬编码表称为变量:

BEGIN

v_sql1 := 'select /*+parallel (4)*/ a.owner,a.table_name, round(sum(b.sum_bytes),2) TOT_OBJECT_SIZE_MB, EST_ONE_ROW_MB
from dba_tables@NAB1 a, '|| v_Mv_name ||' b
where a.table_name = b.segment_name
and a.table_name in
(select table_name from '|| v_Mv_name ||' where driver_table is null)
and a.owner in (select distinct productionschema from '|| v_Mv_name ||' c)
group by a.owner,a.table_name
order by a.table_name';

问题是...我现在如何管理将数据从循环中取出?我想我会循环,只使用包含SQL的变量?:

for rws in (v_sql1)

loop

如果是这样,现在该怎么办?

execute immediate' select /*+parallel (4)*/ count(*) from ' <NO_IDEA_HOW_TO_REFERENCE_THE_DATA_IN_THE_LOOP_NOW> ' into TOTAL_ROW_COUNT;
回复
  • 需要ai 回复

    您还可以使用RefCursor:

    declare
       cur SYS_REFCURSOR;
       owner varchar2(30); 
       table_namevarchar2(30); 
       TOT_OBJECT_SIZE_MB number; 
       EST_ONE_ROW_MB number; 
       TOTAL_ROW_COUNT integer;
       v_sql1 varchar2(10000);
    
    begin
    
    v_sql1 := 'select /*+parallel (4)*/ a.owner,a.table_name, round(sum(b.sum_bytes),2) TOT_OBJECT_SIZE_MB, EST_ONE_ROW_MB
    from dba_tables@NAB1 a, '|| v_Mv_name ||' b
    where a.table_name = b.segment_name
    and a.table_name in
    (select table_name from '|| v_Mv_name ||' where driver_table is null)
    and a.owner in (select distinct productionschema from '|| v_Mv_name ||' c)
    group by a.owner,a.table_name
    order by a.table_name';
    
       open cur for v_sql1;
       loop
          fetch cur into owner, table_name, TOT_OBJECT_SIZE_MB, EST_ONE_ROW_MB;
          exit when cur%NOTFOUND;
          execute immediate' select /*+parallel (4)*/ count(*) from '||table_name into TOTAL_ROW_COUNT;
       end loop;
    end;
    

    Usually a BULK COLLECT provides better performance. However, as you run a dedicated query for each record I don't think you will notice any difference in the performance.

  • D_G 回复

    You may do a BULK COLLECT into collection of record you wish to return and then loop through the collection.

    您可能可以在脚本中轻松使用此示例。

    DECLARE
     TYPE sizerec is RECORD ( owner dba_tables.owner%type, 
      table_name dba_tables.table_name%type,
      tot_object_size_mb number);
      TYPE srectab  is TABLE OF  sizerec;
      srec srectab;
      l_tab_name varchar2(40) := 'dba_tables';
    BEGIN
    
    
    EXECUTE IMMEDIATE 
    'SELECT a.owner,a.table_name,round(SUM(b.bytes),2) tot_object_size_mb
      FROM '||l_tab_name||' a
    JOIN dba_segments b ON a.table_name = b.segment_name where rownum < 10
    GROUP BY a.owner,a.table_name ' BULK COLLECT INTO srec;
    
    for i in srec.first..srec.last 
    loop
      dbms_output.put_line(srec(i).owner||','||srec(i).table_name
                                        ||','||srec(i).tot_object_size_mb);
    end loop;
    
    END;
    /
    

    输出量

    PL/SQL procedure successfully completed.
    
    SYS,BOOTSTRAP$,65536
    SYS,FILE$,65536
    SYS,OBJERROR$,65536
    SYS,UNDO$,65536
    SYS,PROXY_DATA$,65536
    SYS,OBJ$,10485760
    SYS,PROXY_ROLE_DATA$,65536
    SYS,OBJAUTH$,2097152
    SYS,CON$,262144