Oracle PL/SQL Замена цикла for на динамический SQL, помощь в изменении синтаксиса

У меня есть цикл 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;

Вы должны предпочесть синтаксис соединения ANSI, то есть from dba_tables@NAB1 a JOIN '|| v_Mv_name ||' b ON a.table_name = b.segment_name

— 30.05.2019 17:01

Нет причин для DISTINCT в and a.owner in (select distinct ... — просто пропустите DISTINCT, производительность может быть лучше.

— 30.05.2019 17:03

oracle plsql

30.05.2019 08:53

Стоит ли изучать PHP в 2023-2024 годах?

Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...

Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией

В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.

Приемы CSS-макетирования - floats и Flexbox

Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...

Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest

В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...

Концепция локализации и ее применение в приложениях React ⚡️

Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...

Пользовательский скаляр GraphQL

Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...

155

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Вы можете сделать BULK COLLECT в коллекцию записей, которые вы хотите вернуть, а затем прокрутить коллекцию.

Вы можете легко использовать этот пример в своем скрипте.

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

30.05.2019 09:41

Ответ принят как подходящий

Вы также можете использовать 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;

Обычно BULK COLLECT обеспечивает лучшую производительность. Однако, поскольку вы запускаете специальный запрос для каждой записи, я не думаю, что вы заметите какую-либо разницу в производительности.

Фантастический. Большое спасибо!! :-)

— 31.05.2019 06:30

30.05.2019 17:06