ORACLE 11g 中的表值函数?(参数化视图)

2021-12-06 00:00:00 oracle11g user-defined-functions oracle views parameterized

我过去曾看到过关于此的讨论，例如 此处.但我想知道是否在某个地方，也许是 10g 或 11g(我们正在使用 11g)，ORACLE 引入了对参数化视图"的任何更好的支持，而无需用各种用户定义的类型和/或游标定义或 sys_context 变量.

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.

我希望 ORACLE 可能会根据以下 T-SQL 示例添加对简单有效"的东西的支持:

I'm hoping maybe ORACLE's added support for something that simply "just works", as per the following example in 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

然后就这样选择它:

SELECT * FROM dbo.getSomeData(23)

推荐答案

不需要 SYS_CONTEXT 或游标定义.您确实需要一个类型，以便在解析 SQL 时，它可以确定要返回哪些列.也就是说，您可以轻松编写一个脚本，根据 user_tab_columns 中的数据为一个或多个表生成类型和集合类型定义.

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.

最近的是

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));

相关文章