Reuse select query in a procedure in Oracle

Question

How would I store the result of a select statement so I can reuse the results with an in clause for other queries? Here's some pseudo code:

declare
  ids <type?>;
begin
  ids := select id from table_with_ids;
  select * from table1 where id in (ids);
  select * from table2 where id in (ids);
end;

... or will the optimizer do this for me if I simply put the sub-query in both select statements?

EDIT: Here's more information about the structure of my tables.

Basically table1 is a standard table with the id being the primary key. While table2 has a 3-column primary key with id being one of those columns. In my case the id in table2 will appear in three rows.

Answer 1

You could use a SQL table object to store the result of the select and reuse it. It will consume more memory and will probably be efficient only if the first SELECT takes a lot of time.

CREATE TYPE tab_number IS TABLE OF NUMBER;
/

You would use it with a BULK COLLECT INTO clause:

DECLARE
   ids tab_number;
BEGIN
   SELECT id BULK COLLECT INTO ids FROM table_with_ids;
   SELECT * /*into ??*/ 
     FROM table1 
    WHERE id IN (SELECT column_value FROM TABLE(ids));
   SELECT * /*into ??*/ 
     FROM table2
    WHERE id IN (SELECT column_value FROM TABLE(ids));
END;

In version 9i and before you would need to use CAST to query the table:

SELECT * 
  FROM table2 
 WHERE id IN (SELECT column_value FROM CAST (TABLE(ids) AS tab_number));

Alternatively, you could use a GLOBAL TEMPORARY TABLE to store the intermediate result set.