ORACLE 11g 中的表值函数？（参数化视图）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2059299/
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
Table-Valued Functions in ORACLE 11g ? ( parameterized views )
提问by eidylon
I've seen discussions about this in the past, such as here. But I'm wondering if somewhere along the line, maybe 10g or 11g (we are using 11g), ORACLE has introduced any better support for "parameterized views", without needing to litter the database with all sorts of user-defined types and/or cursor definitions or sys_context variables all over.
我过去曾看到过有关此问题的讨论，例如这里。但我想知道是否在某个地方，也许是 10g 或 11g（我们正在使用 11g），ORACLE 引入了对“参数化视图”的任何更好的支持，而无需用各种用户定义的类型和/或游标定义或 sys_context 变量。
I'm hoping maybe ORACLE's added support for something that simply "just works", as per the following example in T-SQL:
我希望 ORACLE 可能会根据 T-SQL 中的以下示例添加对简单“有效”的东西的支持：
CREATE FUNCTION [dbo].[getSomeData] (@PRODID ROWID)
RETURNS TABLE AS
RETURN SELECT PRODID, A, B, C, D, E
FROM MY_TABLE
WHERE PRODID = @PRODID
Then just selecting it as so:
然后只需选择它：
SELECT * FROM dbo.getSomeData(23)
回答by Gary Myers
No need for SYS_CONTEXT or cursor definitions. You do need a type so that, when the SQL is parsed, it can determine which columns are going to be returned. That said, you can easily write a script that will generate type and collection type definitions for one or more tables based on the data in user_tab_columns.
不需要 SYS_CONTEXT 或游标定义。您确实需要一个类型，以便在解析 SQL 时，它可以确定将要返回哪些列。也就是说，您可以轻松编写一个脚本，该脚本将根据 user_tab_columns 中的数据为一个或多个表生成类型和集合类型定义。
The closest is
最接近的是
create table my_table
(prodid number, a varchar2(1), b varchar2(1),
c varchar2(1), d varchar2(1), e varchar2(1));
create type my_tab_type is object
(prodid number, a varchar2(1), b varchar2(1),
c varchar2(1), d varchar2(1), e varchar2(1))
.
/
create type my_tab_type_coll is table of my_tab_type;
/
create or replace function get_some_data (p_val in number)
return my_tab_type_coll pipelined is
begin
FOR i in (select * from my_table where prodid=p_val) loop
pipe row(my_tab_type(i.prodid,i.a,i.b,i.c,i.d,i.e));
end loop;
return;
end;
/
SELECT * FROM table(get_Some_Data(3));
回答by Cristi Boboc
It is possible to define a kind of "parametrized" views in Oracle. The steps are:
可以在 Oracle 中定义一种“参数化”视图。步骤是：
- Define a package containing as public members that are in fact the needed parameters (there is no need for functions or procedures in that package),
- Define a view that is based on that package members.
- 定义一个包含公共成员的包，这些成员实际上是所需的参数（该包中不需要函数或过程），
- 定义基于该包成员的视图。
To use this mechanism one user should:
要使用此机制，用户应该：
- open a session,
- assign the desired values to that package members,
SELECTdata from the view,- do other stuff or close the session.
- 打开一个会话，
- 将所需的值分配给该包成员，
SELECT视图中的数据，- 做其他事情或关闭会话。
REMARK: it is essential for the user to do all the three steps in only one session as the package members scope is exactly a session.
备注：用户必须在一个会话中完成所有三个步骤，因为包成员范围恰好是一个会话。
回答by Mahmoud H. Sadat
There are TWO types of table-valued functions in SQL SERVER:
SQL SERVER 中有两种类型的表值函数：
Inline table-valued function: For an inline table-valued function, there is no function body; the table is the result set of a single
SELECTstatement. This type can be named as 'parameterized view' and it has no equivalent in ORACLE as I know.Multistatement table-valued function: For a multistatement table-valued function, the function body, defined in a
BEGIN...ENDblock, contains a series of Transact-SQL statements that build and insert rows into the table that will be returned.
内联表值函数：对于内联表值函数，没有函数体；该表是单个
SELECT语句的结果集。这种类型可以命名为“参数化视图”，据我所知，它在 ORACLE 中没有等价物。多
BEGIN...END语句表值函数：对于多语句表值函数，在块中定义的函数体包含一系列 Transact-SQL 语句，这些语句在将要返回的表中构建和插入行。
The above sample (By Gary Myers) creates a table function of the second type and it is NOT a 'parameterized view'.
上面的示例（作者 Gary Myers）创建了第二种类型的表函数，它不是“参数化视图”。
