在 Oracle 的 select 语句中使用对象类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1589715/
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 an object type in a select statement in Oracle
提问by Chris B
I have a function that returns an object that has 3 values. Is there a way to call that function from a select statement and have each value be a different column? I could break it into 3 functions, but the values are related so I wanted to keep it as one for performance reasons. (So oracle doesn't have to call 3 very similar complex functions for every row in the query.)
我有一个函数，它返回一个具有 3 个值的对象。有没有办法从 select 语句中调用该函数并使每个值成为不同的列？我可以将它分解为 3 个函数，但这些值是相关的，因此出于性能原因，我想将其保留为一个。（因此 oracle 不必为查询中的每一行调用 3 个非常相似的复杂函数。）
So for:
因此对于：
create type test_obj is object (
a NUMBER,
b NUMBER,
c NUMBER);
create or replace function test_func (
pinput NUMBER)
return test_obj
as
begin
return test_obj(0, 0, 0);
end test_func;
I'd like to be able to call test_func from a select statement, but have a, b, and c be different columns, without calling the function multiple times. I thought maybe something like this, but it doesn't work:
我希望能够从 select 语句中调用 test_func，但是 a、b 和 c 是不同的列，而不需要多次调用该函数。我想也许是这样的，但它不起作用：
select
iv.col1,
iv.col2,
iv.func_data.a,
iv.func_data.b,
iv.func_data.c
from
(select
mt.col1,
mt.col2,
test_func(mt.input) as func_data
from
my_table mt) iv
Is there a way to do anything like this in Oracle 10g, or is there a better way to solve this problem?
有没有办法在 Oracle 10g 中做这样的事情，或者有没有更好的方法来解决这个问题？
回答by Chris B
The select statement in the question will work. It was failing because I didn't include an alias for the inline view.
问题中的 select 语句将起作用。它失败了，因为我没有为内联视图包含别名。
For some reason this will work:
出于某种原因，这将起作用：
select
iv.func_data.a,
iv.func_data.b,
iv.func_data.c
from
(select
test_func(mt.input) as func_data
from
my_table mt) iv
But this won't:
但这不会：
select
func_data.a,
func_data.b,
func_data.c
from
(select
test_func(mt.input) as func_data
from
my_table mt)
回答by Michael
The table alias places the result into a named result set context, which enables the result to work as an object instance. Without the alias, it fails because the casting doesn't handle object type instances without their own explicit reference, which is effectively what the table alias is.
表别名将结果放入命名的结果集上下文中，这使结果能够用作对象实例。如果没有别名，它会失败，因为转换不处理没有自己显式引用的对象类型实例，这实际上就是表别名。
