Вернуть несколько строк из рекурсивной хранимой процедуры

Question

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

Это моя текущая процедура:

CREATE OR REPLACE PROCEDURE TEST 
(
  MATERIAL_H IN VARCHAR2,
) AS
BEGIN
  FOR R IN (SELECT COMPONENT FROM TTP10.PSMS WHERE MATERIAL = MATERIAL_H) LOOP
    TEST (R.COMPONENT);
    DBMS_OUTPUT.PUT_LINE(R.COMPONENT); -- Each COMPONENT should be one row in the result
  END LOOP;
END TEST;

редактировать

Если добавлен пример записи базы данных. Как вы можете видеть MATERIAL 1 состоит из нескольких COMPONENT (89, 90, 91), Эти компоненты также могут состоять из других компонентов, таких как COMPONENT (90, 5),

Моя хранимая процедура прочитала все отношения между материалами и компонентами. Я хотел бы получить все вложенные компоненты в материале.

MATERIAL 1: (89, 90, 91, 5, 6, 7, 2, 3, 4)

+-----------+-----------+--+------------+-----------+
| MATERIAL  | COMPONENT |  | MATERIALS  | COMPONENT |
+-----------+-----------+--+------------+-----------+
|     1     |     89    |  |      2     |    NULL   |
+-----------+-----------+--+------------+-----------+
|     1     |     90    |  |      3     |    NULL   |
+-----------+-----------+--+------------+-----------+
|     1     |     91    |  |      4     |    NULL   |
+-----------+-----------+--+------------+-----------+
|     90    |     5     |  |      6     |    NULL   |
+-----------+-----------+--+------------+-----------+
|     90    |     6     |  |      7     |    NULL   |
+-----------+-----------+--+------------+-----------+
|     90    |     7     |  |     91     |    NULL   |
+-----------+-----------+--+------------+-----------+
|     5     |     2     |  |     89     |    NULL   |
+-----------+-----------+--+------------+-----------+
|     5     |     3     |  |            |           |
+-----------+-----------+--+------------+-----------+
|     5     |     4     |  |            |           |
+-----------+-----------+--+------------+-----------+

Моя хранимая процедура работает нормально, она печатает все вложенные отношения между материалами и компонентами.

Как я могу вернуть вывод из DBMS_OUTPUT.PUT_LINE(R.COMPONENT); в результате? Пожалуйста, обратите внимание, я не могу изменить структуру базы данных.

Answer 1

Вы можете просто пойти с оператором SQL, чтобы найти такое отношение, здесь нет необходимости в рекурсивном утверждении

SQL> desc material
 Name                                      Null?    Type
 ----------------------------------------- -------- -------

 MAT                                                NUMBER
 COMPONENT                                          NUMBER

Я вставил ваши примеры значений, как заявление SQL

select 
    distinct a.component
from 
 material a
where a.component is not null
START WITH a.mat = 1
CONNECT BY PRIOR a.component= a.mat;

Выход как ниже

SQL> select
  2     distinct a.component
  3  from
  4   material a
  5  where a.component is not null
  6  START WITH a.mat = 1
  7  CONNECT BY PRIOR a.component= a.mat
  8  ;

 COMPONENT
----------
        89
         6
         7
         5
         2
         3
        91
        90
         4

9 rows selected.

Если вы хотите для другого значения, вы можете попробовать, изменив значение 1 на другое значение. Выше может быть встроено в вашу процедуру возврата.