CODEWITHSUNDEEP
sqloracle-databasestored-proceduresreturn-value
Andre Hofmeister
Andre Hofmeister

Reputation: 3416

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.

Upvotes: 1

Views: 764

Answers (1)

Exhausted
Exhausted

Reputation: 1885

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.

Upvotes: 2

Related Questions