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;