使用表变量/全局临时表来编写此 PL/SQL 函数

Question

我使用的是 Oracle 11g，并且有很多存储过程代码使用相同的 SELECT 语句（但很复杂），只是在 where 子句中使用不同的输入：

select ... where ancestor = X

该 SELECT 语句现在被复制/粘贴到数百个这些语句上，我需要重构，以便它们使用相同的 SELECT 语句构造。因为所有这些存储过程已经存在，所以重构必须与当前代码很好地配合，如下所示：

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

简而言之，我需要一种标准化 SELECT 的 PL/SQL 方法，但需要一个引用游标、数组类型、集合和这样将不起作用，因为它们不被视为表（因此不能内部连接到 TBRAWData）。全局临时表可以在这里工作，还是其他什么？

请帮忙！

Answer 1

View 是一个很好的答案（感谢Gary），但还有另一种可能性。

您可以在数据库模式中创建对象类型并引用它的表类型：

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

create or replace type TFooDataList as table of TFooDataRecord;

然后，声明返回所需结果的函数：

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;

然后，您可以在选择语句和连接中使用函数：

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

当然，您可以将函数放入包中。
但不是对象和表类型声明。

如果函数返回的行数不太大（取决于服务器硬件，但一般不超过1000-2000条记录），则适用此解决方案。

它比使用视图更好，因为 Oracle 将为参数化查询维护单个编译和缓存的计划，并且不会像使用视图的解决方案那样为每个查询重建它。

Answer 2

复杂到什么程度呢？

如果您正在讨论 SELECTed 表达式，请查看虚拟列
视图是封装查询复杂性的好方法。我通常会将选定的列与连接谓词一起放入其中，并将过滤谓词留给调用查询。

例如，如果我有的

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'

话

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

，

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

有时视图会使优化器变得复杂和混乱（并且我在组合视图和数据库链接方面有过非常糟糕的经历）。