Иерархический рекурсивный запрос Oracle останавливается на цикле и продолжается с другим именем пакета

Я немного борюсь в данный момент со следующей проблемой.

Я хочу знать, какой пакет вызывает какие пакеты. Таблица не является иерархической. Это таблица user_dependencies.

Код до сих пор:

CREATE OR REPLACE PACKAGE object_x is
 
  type ObjectRec is record(
    dName      varchar2(250),
    level       number
  );
 
  type ObjectTemp is table of ObjectRec;
 
  function Referenced(dname VARCHAR2, level NUMBER, maxl NUMBER) return ObjectTemp pipelined;
 
end;
/
 
CREATE OR REPLACE PACKAGE BODY object_x is
 
  function Referenced(dname VARCHAR2, level NUMBER, maxl NUMBER) return ObjectTemp pipelined is
    rData  ObjectRec;

  begin
 
    if level >= maxl then
      return;
    end if;
  
    if level = 1 then
      rData.dName := name;
      rData.Level := maxl;
      pipe row(rData);
    end if;
 
    for r in ( 
      select referenced_name
      from user_dependencies
      where name = upper(dname)
      and type = 'PACKAGE BODY'
      and referenced_type = 'PACKAGE'
      and referenced_name != UPPER(dname)
      and referenced_name != name 
    ) 

      loop
      rData.dName := LPAD(' ', 3, ' ') || r.Referenced_name;
      rData.level := level+1;
      pipe row(rData);
      rData.Name := r.Referenced_name;
 
 
     for r2 in (select * from table(Referenced(rData.Name, level + 1, maxl))) loop
        rData.Name := LPAD(' ', 3, ' ') || r2.dName;
        rData.Level := r2.Level;
        pipe row(rData);
        null;
      end loop;
 
    end loop;

РЕЗУЛЬТАТ:

     Level Dname
---------- --------------------------------------------------------------------------------
         1 PAC1
         2    PAC2
         2    PAC3
         2    PAC4
         2    PAC5
         3       PAC6
         3       PAC2
         3       PAC7
         3       PAC8
         4          PAC9
         4          PAC10
         5             PAC6
         5             PAC11
         5             PAC3
         5             PAC9
         5             PAC12
         5             PAC6
         5             PAC3
         5             PAC9
         5             PAC4
         5             PAC8
         3       PAC10
         4          PAC6
         4          PAC11
         4          PAC3
         4          PAC9
         4          PAC12
         4          PAC4

ОЖИДАЕМЫЙ РЕЗУЛЬТАТ:

     Level Dname
---------- --------------------------------------------------------------------------------
         1 PAC1
         2    PAC2
         2    PAC3
         2    PAC4
         2    PAC5
         3       PAC6
         3       PAC2
         3       PAC7
         3       PAC8
         4          PAC9
         4          PAC10
         5             PAC6
         5             PAC11
         5             PAC3
         5             PAC9
         5             PAC12
         5             PAC6
         5             PAC3
         5             PAC9
         5             PAC4
         5             PAC8
         3       PAC10 LOOP!!!!
          -----BREAK------
         CONTINUE WITH OTHER PACKAGES……….. 

Спасибо за любой совет.

«Таблица не является иерархической». Как вы думаете, почему это не так? USER_DEPENDENCIES и ALL_DEPENDENCIES определенно кажутся иерархическими (после того, как вы соедините пакеты с их телами пакетов). См. этот ответ для иерархического запроса, который успешно перемещается по нему, чтобы найти таблицы, на которые есть ссылки из иерархии пакетов.

MT0 09.04.2022 17:37

Спасибо за совет. Я тоже проверю эту версию.

ScentedCandle 10.04.2022 09:44
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
Четыре эффективных способа центрирования блочных элементов в CSS
Четыре эффективных способа центрирования блочных элементов в CSS
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то...
0
2
28
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

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

В конце концов, да, можно делать то, что вы хотите.

Один из способов сделать это — использовать вложенную таблицу для хранения цепочки зависимых пакетов, которую вы создали в текущей цепочке рекурсивных вызовов функций. Вы можете использовать оператор MEMBER OF с этой вложенной таблицей, чтобы проверить, находится ли пакет уже в этой цепочке (и, следовательно, вы нашли петлю). Если нет, вы можете вызвать свою функцию Referenced рекурсивно, но добавив дополнительный элемент в эту таблицу для текущего пакета.

Я внес эти изменения в ваш код и получил следующее:

CREATE OR REPLACE TYPE varchar2_table AS TABLE OF VARCHAR2(250);
/

CREATE OR REPLACE PACKAGE object_x is
 
  type ObjectRec is record(
    dName      varchar2(250),
    dLevel     number
  );
 
  type ObjectTemp is table of ObjectRec;
 
  function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER) return ObjectTemp pipelined;
  function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER, pkgs_so_far IN varchar2_table) return ObjectTemp pipelined;

end;
/
 
CREATE OR REPLACE PACKAGE BODY object_x is

  function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER) return ObjectTemp pipelined is
    rData  ObjectRec;
  begin
    for r2 in (select * from table(Referenced(dname, dlevel, maxl, varchar2_table(dname))))
    loop
      rData.dName := r2.dName;
      rData.dLevel := r2.dLevel;
      pipe row(rData);
    end loop;
  end;
 
  function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER, pkgs_so_far IN varchar2_table) return ObjectTemp pipelined is
    rData  ObjectRec;
    new_pkgs_so_far varchar2_table;
    loop_detected boolean;
  begin
 
    if dlevel >= maxl then
      return;
    end if;
  
    if dlevel = 1 then
      rData.dName := dname;
      rData.dLevel := 1;
      pipe row(rData);
    end if;
 
    for r in ( 
      select referenced_name
      from user_dependencies
      where name = upper(dname)
      and type = 'PACKAGE BODY'
      and referenced_type = 'PACKAGE'
      and referenced_name != UPPER(dname)
      and referenced_name != name 
    ) 

    loop
      loop_detected := r.referenced_name MEMBER OF pkgs_so_far;
    
      rData.dName := LPAD(' ', 3, ' ') || r.Referenced_name;
      if loop_detected then
        rData.dName := rData.dName || ' --- LOOP';
      end if;
      rData.dLevel := dLevel + 1;
      pipe row(rData);
 
      if not loop_detected THEN
        new_pkgs_so_far := pkgs_so_far;
        new_pkgs_so_far.EXTEND(1);
        new_pkgs_so_far(new_pkgs_so_far.COUNT) := r.referenced_name;
        for r2 in (select * from table(Referenced(r.referenced_name, dLevel + 1, maxl, new_pkgs_so_far))) loop
          rData.dName := LPAD(' ', 3, ' ') || r2.dName;
          rData.dLevel := r2.dLevel;
          pipe row(rData);
        end loop;
      end if;
 
    end loop;
  end;
end;
/

Я изменил вашу конвейерную функцию, добавив параметр pkgs_so_far, который содержит таблицу пакетов, которые в настоящее время встречаются. Я добавил еще одну конвейерную функцию с той же сигнатурой, что и ваша исходная функция, и она просто вызывает другую с таблицей пакетов, содержащей только данный пакет.

В измененной функции мы теперь используем оператор MEMBER OF, чтобы проверить, встречались ли мы уже с текущим пакетом. Если у нас есть, мы можем добавить дополнительный маркер --- LOOP, чтобы обозначить это. Если мы не встречали этот пакет раньше, мы добавляем имя пакета в таблицу и продолжаем рекурсивно проверять его ссылки. Обратите внимание, что назначение таблицы в строке new_pkgs_so_far := pkgs_so_far;, по-видимому, устанавливает new_pkgs_so_far в копию таблицы в pkgs_so_far, поэтому мы можем изменить new_pkgs_so_far, не затрагивая при этом pkgs_so_far.

Однако был также ряд других проблем, которые я прояснил с помощью вашего кода:

  • В теле пакета отсутствуют последние две строки end;: одна для завершения процедуры, а другая для завершения самого тела пакета.
  • Ваш код содержит некоторые ссылки на поле name вашей записи, но оно называется dName. Я привел их в порядок, чтобы использовать dName.
  • Похоже, Oracle не понравилось используемое имя level, поэтому я изменил его на dLevel. (LEVEL — это ключевое слово Oracle, оно используется в иерархическом SQL.)
  • Я не уверен, что правильно передавать строку с dLevel, установленным на maxl, в случае, когда переменная dlevel равна 1, поэтому вместо этого я изменил это на 1.
  • При рекурсивном вызове вашей функции References я изменил первый параметр на r.referenced_name, так как тогда нам не нужно назначать rData.dName.
  • Я удалил ненужный оператор null; в цикле, который повторяет рекурсивный вызов вашей процедуры.

Я также заметил, что вы написали LPAD(' ', 3, ' ') дважды, но я не знаю, почему: это многословный способ написания ' ', где в строке три пробела.

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

У меня тоже нет всех ваших пакетов (и я подозреваю, что на самом деле они не названы PAC1, PAC2 и так далее), поэтому я создал три пакета от PAC1 до PAC3. PAC2 и PAC3 ссылаются друг на друга и PAC1, и результат, который я получил, был следующим:

SQL> COLUMN dname FORMAT a30
SQL> select * from table(object_x.referenced('PAC2', 1, 10));

DNAME                              DLEVEL
------------------------------ ----------
PAC2                                    1
   PAC1                                 2
   PAC3                                 2
      PAC1                              3
      PAC2 --- LOOP                     3

Другие вопросы по теме