Using table variable / global temporary table to code this PL/SQL function

I am using Oracle 11g, and I have a lot of stored procedure code that use the same SELECT statement (but a complex one), just a different input in a where clause:

select ... where ancestor = X

That SELECT statement right now is copied / pasted on hundreds of these, and I need to refactor so they use the same SELECT statement construction. Because all these stored procs already exist, the refactoring must work nicely with the current code, which looks like this:

create or replace procedure Foo
begin
  select quantity, amount from TBRawData, (select ... where ancestor = X) temp, where TBRAWData.StoreID = temp.StoreID;
end;

In a nutshell, I need a PL/SQL means of standardizing a SELECT, but a ref cursor, array types, collections, and such will not work because they a开发者_开发知识库re not treated like a table (thus cannot be inner joined to TBRAWData). Would a global temporary table work here, or something else?

Please help!

View is good answer(thanks to Gary), but there is another possibility.

You can create object type in database schema and table type referenced it:

create or replace type TFooDataRecord as object (
   quantity number,
   amount number
);

create or replace type TFooDataList as table of TFooDataRecord;

then, declare function returning required results:

create or replace function GetFoo(pAncestor in number) return TFooDataList
as 
  vResult TFooDataList;
begin

  select TFooDataRecord(quantity, amount)
  bulk collect into vResult
  from TBRawData, (select ... where ancestor = pAncestor) temp, 
  where TBRAWData.StoreID = temp.StoreID;

  return vResult;

end;

then, you can use function in select statements and joins:

select foo_func.amount 
from 
  table( GetFoo(123) ) foo_func,
  some_another_table   foo2
where 
  foo_func.quantity < foo2.quantity 

Of course, you can place function into the package. But not object and table type declarations.

This solution applicable if number of rows returned by function is not too big (depends on server hardware, but generally not more than 1000-2000 records).

It better than use of view because Oracle would maintain single compiled and cached plan for parametrized query and don't rebuild it for each query as in case of solution with view.

Complicated in what way ?

If you are talking about SELECTed expressions, look at virtual columns Views can be a good way of encapsulating query complexity. I would generally put the selected columns in there with the join predicates and leave filter predicates to the calling query.

For example if I have

SELECT a.col_a1, a.col_a2, b.col_b3
FROM table_a a join table_b b on a.col_a1 = b.col_b1
WHERE b.col_b4 = 'Blue'

I would

CREATE VIEW v_1 AS
SELECT a.col_a1, a.col_a2, b.col_b3, b.col_b4
FROM table_a a join table_b b on a.col_a1 = b.col_b1

and

SELECT v_1.col_a1, v_1.col_a2, v_1.col_b3
FROM v_1
WHERE v_1.col_b4 = 'Blue'

Sometimes views can complicated and confuse the optimizer (and I've had very bad experiences combining views and database links).