在Oracle中的select语句中使用对象类型

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中做这样的事情,还是有更好的方法来解决这个问题?

2 个解决方案

#1

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)

#2

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.

表别名将结果放入命名结果集上下文中,这使结果可以作为对象实例。如果没有别名,它就会失败,因为如果没有自己的显式引用,转换不会处理对象类型实例,这实际上就是表别名。

#1