Reusing a query in a procedure in Oracle

Question

How to save the result of a select statement so that I can reuse the results with a sentence infor other queries? Here are a few pseudo codes:

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 just put a subquery in both select statements?

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

Basically, table1 is a standard table with an identifier that is a primary key. Although table2 has a primary key with three columns, the identifier is one of these columns. In my case, the identifier in table2 will be displayed in three lines.

Answer 1

SQL . , , , SELECT .

CREATE TYPE tab_number IS TABLE OF NUMBER;
/

BULK COLLECT INTO:

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;

9i , CAST :

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

GLOBAL TEMPORARY TABLE .