从递归存储过程返回多行

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更改为其他值来尝试。以上内容可以嵌入到您的过程中返回。