How would I store the result of a select statement so that I can reuse the results to join to different tables?

Question

How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor.

Below is some pseudo code, in this example I have kept the Select statement simple but in real life it is a long query with multiple joins, I have to use the identical SQL twice to join to 2 different tables and as it is quite long and can be changed in the future hence I want to be able reuse it.

I have tried creating a view and storing the results of the select statement in it but it seems I can't create a view inside the cursor loop, when I tried I am getting "Encountered the symbol "CREATE"" error.

DECLARE TYPE cur_type IS REF CURSOR;
CURSOR PT_Cursor IS

    SELECT * FROM Table1

    PT_Cursor_Row   PT_Cursor%ROWTYPE;

BEGIN
 OPEN PT_Cursor;

 LOOP
    FETCH PT_Cursor INTO PT_Cursor_Row;
    EXIT WHEN PT_Cursor%NOTFOUND;

    Select ID From Table2 --this is actually a long complext query
        INNER JOIN Table3 ON Table2.ID = Table3.ID
    WHERE Table2.ID = PT_Cursor_Row.ID

    Select * From Table2 --this is actually a long complext query
        LEFT JOIN Table4 ON Table2.ID = Table4.ID
    WHERE Table2.ID = PT_Cursor_Row.ID                               

END LOOP;

 CLOSE PT_Cursor;
END;

Because we need to perform the same process for each row of a table. — 03Usr, May 31 '13 at 08:50
Is this process something that occurs within the database, or outside it? — , May 31 '13 at 08:51
So why not perform this processing in a select, rather than looping through a cursor? — , May 31 '13 at 08:57
All of the SQL's here are pseudo, the actual ones are complex queries with 100's of lines and working against over 1million rows, we have tried using select but decided to use cursor in the end. — 03Usr, May 31 '13 at 08:59

score 3 · Accepted Answer · edited May 23 '17 at 12:15

3

One way to save the results from a query is via a temporary table - there's a short answer to this question that describes how to create them, while there is a longer answer here that discusses how to use them, with possible alternatives.

edited May 23 '17 at 12:15

Community

1
1

answered May 31 '13 at 09:00

EvilTeach · Answer 2 · 2013-05-31T14:45:56.490

Temp tables certainly are a viable option. One can also use the with statement to 'reuse' results sets.

WITH 
PEOPLE AS 
(
    SELECT 'FRED'   NAME, 12 SHOE_SIZE FROM DUAL UNION ALL
    SELECT 'WILMA'  NAME,  4 SHOE_SIZE FROM DUAL UNION ALL
    SELECT 'BARNEY' NAME, 10 SHOE_SIZE FROM DUAL UNION ALL
    SELECT 'BETTY'  NAME,  3 SHOE_SIZE FROM DUAL
),
WOMAN AS
(
    SELECT 'BETTY'  NAME FROM DUAL UNION ALL
    SELECT 'WILMA'  NAME FROM DUAL
)
SELECT 'WOMANS ', PEOPLE.NAME, PEOPLE.SHOE_SIZE
FROM PEOPLE, WOMAN
WHERE PEOPLE.NAME = WOMAN.NAME

UNION ALL

SELECT 'MENS   ', PEOPLE.NAME, PEOPLE.SHOE_SIZE
FROM PEOPLE, WOMAN
WHERE PEOPLE.NAME = WOMAN.NAME(+) 
  AND WOMAN.NAME IS NULL

How would I store the result of a select statement so that I can reuse the results to join to different tables?

2 Answers2