oracle 使用表变量/全局临时表来编码这个 PL/SQL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4857197/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using table variable / global temporary table to code this PL/SQL function
提问by Echiban
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:
我正在使用 Oracle 11g，我有很多存储过程代码使用相同的 SELECT 语句（但很复杂），只是 where 子句中的输入不同：
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:
那个 SELECT 语句现在被复制/粘贴到数百个上，我需要重构以便它们使用相同的 SELECT 语句构造。因为所有这些存储过程都已经存在，所以重构必须与当前代码很好地配合，如下所示：
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 are not treated like a table (thus cannot be inner joined to TBRAWData). Would a global temporary table work here, or something else?
简而言之，我需要一种 PL/SQL 方法来标准化 SELECT，但引用游标、数组类型、集合等将不起作用，因为它们不被视为表（因此不能内部连接到 TBRAWData）。全局临时表会在这里工作，还是其他什么？
Please help!
请帮忙！
回答by ThinkJet
View is good answer(thanks to Gary), but there is another possibility.
View 是很好的答案（感谢Gary），但还有另一种可能性。
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).
此方案适用于函数返回的行数不太大的情况（取决于服务器硬件，但一般不超过1000-2000条记录）。
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.
它比使用视图更好，因为 Oracle 将为参数化查询维护单个编译和缓存的计划，并且不会像使用视图的解决方案那样为每个查询重建它。
回答by Gary Myers
Complicated in what way ?
复杂到什么程度？
If you are talking about SELECTed expressions, look at virtual columnsViews 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.
如果您正在谈论 SELECTed 表达式，请查看虚拟列视图可以是封装查询复杂性的好方法。我通常会将选定的列与连接谓词放在一起，并将过滤谓词留给调用查询。
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).
有时视图会使优化器复杂化和混淆（我在结合视图和数据库链接时遇到过非常糟糕的经历）。
