当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Return multiple rows from a recursive stored procedure

问题描述:

I would like to return multiple rows as a result from a stored procedure. I am calling this stored procedure recursively, to read all nested values.

This is my current procedure:

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;

Edit

If added an example of the database records. As you can see the MATERIAL 1 is composed of multiple COMPONENT (89, 90, 91). Those components can also be composed of other components, like COMPONENT (90, 5).

My stored procedure read all relationships between the materials and components. I would like to get all nested components in a material.

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 | | | |

+-----------+-----------+--+------------+-----------+

My stored procedure workes fine, it prints all nested relationships between the materials and components.

How could I return the output from DBMS_OUTPUT.PUT_LINE(R.COMPONENT); as result? Please notice, I am not able to change the database structure.

网友答案:

You can simply go with SQL statement to find such relationship, here no need of recursive statement

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

 MAT                                                NUMBER
 COMPONENT                                          NUMBER

I have inserted your sample values the sql statement is like

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;

The output is like below

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.

If you want for other value, you can try by changing value 1 to other value. The above can be embedded in your procedure to return.

分享给朋友:
您可能感兴趣的文章:
随机阅读: