0

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

1 Answer 1

1

You may store the results you want to reuse into a nested table type. In the following example, I use a nested table of number(22,0). A nested table in PL/SQL is not a real table, it just exists in memory like an array.

-- Creating some test tables and data
create table table1 (row_id number(22,0));
create table table2 (row_id number(22,0));

insert into table1 values(1);
insert into table1 values(2);
insert into table1 values(3);
insert into table2 values(1);
insert into table2 values(2);

---------

-- Create a nested table type
CREATE OR REPLACE TYPE type_table_number AS TABLE OF NUMBER(22);

declare
 v_tab type_table_number;
 cursor c is
   select row_id
   from   table2
   where  row_id in (SELECT row_id FROM TABLE (v_tab));
begin
  -- Select row_ids into the table v_tab
  -- (this is not a real table, the data is stored in memory)
  select row_id
  bulk   collect into v_tab
  from   table1
  WHERE  row_id in (1,2);
  -- Select some data from a table and use the content
  -- of the in memory table (v_tab) as search criteria:
  for v_rec in c loop
    DBMS_OUTPUT.PUT_LINE(v_rec.row_id);
  end loop;
  -- Select some other data from another table and also
  -- use the same content of the in memory table:
  -- ...
END;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.