CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqlrecursive-query
Nathan Jones
Nathan Jones

Reputation: 5174

How can I turn the output of this stored procedure that uses a temporary table into a materialized view?

I have a stored procedure that produces the correct output, and I'd like to store the results in a materialized view. I'm having trouble conceptualizing how to get this working because the procedure relies on a temporary table that I created called tmp$.

My goal is to re-write the stored procedure so it doesn't need to utilize the temporary table and the results can be stored in a materialized view.

My end goal is to create a data structure that conforms to the requirements of the Modified Preorder Tree Traversal algorithm.

tmp$ temporary table:

CREATE GLOBAL TEMPORARY TABLE tmp$ 
(
    id   VARCHAR2(8),
    l    INT,
    r    INT
) ON COMMIT DELETE ROWS;

Stored procedure:

CREATE OR REPLACE PACKAGE pkg AS
    PROCEDURE p (
        p_id   IN VARCHAR2,
        seq IN OUT INT
    );

END pkg;

CREATE OR REPLACE PACKAGE BODY pkg AS

    PROCEDURE p (
        p_id   IN VARCHAR2,
        seq IN OUT INT
    )
        AS
    BEGIN
        seq := seq + 1;
        INSERT INTO tmp$ (
            id,
            l,
            r
        ) VALUES (
            p_id,
            seq,
            NULL
        );

        FOR i IN (
            SELECT
                id
            FROM
                tab1
            WHERE
                parent_id = p_id
            ORDER BY
                id
        ) LOOP
            p(i.id,seq);
        END LOOP;

        seq := seq + 1;
        UPDATE tmp$
            SET
                r = seq
        WHERE
            id = p_id;

    END;

END pkg;

tab1 data

+-----+-----------+
| id  | parent_id |
+-----+-----------+
| 01  | null      |
+-----+-----------+
| 100 | 01        |
+-----+-----------+
| 901 | 01        |
+-----+-----------+
| 250 | 01        |
+-----+-----------+
| 400 | 01        |
+-----+-----------+
| 001 | 01        |
+-----+-----------+
| 500 | 01        |
+-----+-----------+
| 600 | 01        |
+-----+-----------+
| 010 | 01        |
+-----+-----------+
| 450 | 01        |
+-----+-----------+

Calling the stored procedure:

VAR n NUMBER

EXEC :n := 0;

EXEC pkg.p('01',:n);

select * from tmp$;

The execution of pkg.p populates the tmp$ table with the following data:

+-----+----+----+
| id  | l  | r  |
+-----+----+----+
| 01  | 1  | 20 |
+-----+----+----+
| 001 | 2  | 3  |
+-----+----+----+
| 010 | 4  | 5  |
+-----+----+----+
| 100 | 6  | 7  |
+-----+----+----+
| 250 | 8  | 9  |
+-----+----+----+
| 400 | 10 | 11 |
+-----+----+----+
| 450 | 12 | 13 |
+-----+----+----+
| 500 | 14 | 15 |
+-----+----+----+
| 600 | 16 | 17 |
+-----+----+----+
| 901 | 18 | 19 |
+-----+----+----+

If I try to put those four lines above within a CREATE MATERIALIZED VIEW clause, the materialized view is empty.

I'm not sure how to even start solving this. Should I look at using a collection table? I'm not sure how that will work since p works recursively.

Upvotes: 4

Views: 141

Answers (1)

user681574
user681574

Reputation: 553

In your stored procedure you are creating a nested set model.

Model your materialized view on a query that generates the nested set model from your original data table and this will remove the need for a temporary table.

It is a rather long exercise to implement, so here is an example already available taken from this article: CTEs with Multiple Recursive Members by Itzik Ben-Gan

https://www.itprotoday.com/development-techniques-and-management/ctes-multiple-recursive-members

*Note that this example is written for SQL Server, but the conversion should be trivial as the recursive CTEs also work in Oracle. There may be some data types that need to be modified to fit Oracle's syntax.

Listing 6: Code to Compute Left and Right Values in the Nested Set Model

WITH EmpsRN AS

(

  SELECT *,

ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname, empid) * 2 - 1 AS n

  FROM dbo.Employees

),

C1 AS

(

  -- root's left arm

  SELECT empid, 1 AS arm, CAST(0x01 AS VARBINARY(8000)) AS sortpath

  FROM dbo.Employees

  WHERE mgrid is NULL


  UNION ALL


  -- root's right arm

  SELECT empid, 2 AS arm, CAST(0x02 AS VARBINARY(8000)) AS sortpath

  FROM dbo.Employees

  WHERE mgrid is NULL


  UNION ALL


  SELECT E.empid, 1 AS arm,

CAST(M.sortpath + CAST(E.n AS BINARY(1)) AS VARBINARY(8000)) AS sortpath

  FROM C1 AS M

INNER JOIN EmpsRN AS E

  ON E.mgrid = M.empid

  WHERE M.arm = 1


  UNION ALL


  SELECT E.empid, 2 AS arm,

 CAST(M.sortpath + CAST(E.n + 1 AS BINARY(1)) AS VARBINARY(8000)) AS sortpath

  FROM C1 AS M

INNER JOIN EmpsRN AS E

  ON E.mgrid = M.empid

  WHERE M.arm = 1

),

c2 AS

(

  SELECT empid, ROW_NUMBER() OVER(ORDER BY sortpath) AS sortval

  FROM C1

)

SELECT empid, MIN(sortval) AS lft, MAX(sortval) AS rgt

FROM c2

GROUP BY empid;

Upvotes: 1

Related Questions