在 Oracle 的 select 语句中使用对象类型
我有一个函数返回一个具有 3 个值的对象。有没有办法从 select 语句调用该函数并使每个值成为不同的列？我可以将其分成 3 个函数，但这些值是相关的，因此出于性能原因我想将其保留为一个函数。 （因此，oracle 不必为查询中的每一行调用 3 个非常相似的复杂函数。）
因此：
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;
我希望能够从 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
有没有办法在Oracle 10g中做这样的事情，或者有没有更好的方法来解决这个问题？
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.)
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
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
问题中的 select 语句将起作用。它失败了，因为我没有包含内联视图的别名。
出于某种原因，这会起作用：
但这不会：
The select statement in the question will work. It was failing because I didn't include an alias for the inline view.
For some reason this will work:
But this won't:
表别名将结果放入命名结果集上下文中，这使得结果能够作为对象实例工作。如果没有别名，它将失败，因为转换不会处理没有自己的显式引用的对象类型实例，这实际上就是表别名。
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.