PL / SQL - 将两个结果集缓存到集合中并将它们连接在一起？ [英] PL/SQL - caching two resultsets into collections and joining them together?

问题描述

我有两个非常大的表，我需要从这些表中处理一个小结果集。但是，处理是在几个函数中完成的，函数必须做一些加入，以便以正确的方式格式化数据。

I have two very large tables and I need to process a small resultset from these tables. However, processing is done in several functions each and function must do some joining in order to format the data in proper way.

我肯定需要缓存初始结果集所以它可以被函数重用。我想做的是将第一个结果集放在一个集合中，第二个结果集在另一个集合中，然后通过SQL查询操作这些集合，就好像它们是真实的SQL表。

I would definitely need to cache the initial resultset somehow so it can be reused by the functions. What I would like to do is put the first result set in one collection, the second resultset in another collection, and then manipulate these collections through SQL queries as if they were real SQL tables.

您可以建议如何做到这一点吗？

Can you suggest how this can be done?

推荐答案

您需要创建模式级别类型一个包）作为嵌套表。您可以填充它们，然后您可以使用table（）语句在正常表中使用它们。

You need to create an schema-level type(not inside a package) as a nested table. You can populate them, and then you may use them in you queries as normal tables using the "table()" statement.

此链接说明您需要什么。一个快速示例

This link explains what you need. A quick example

create type foo as table of number;-- or a record type, data%rowtype, whatever
...
myfoo1 foo := foo (1,2,3);
myfoo2 foo := foo(3,4,5)

select column_value
into bar
from table(foo1) join table(foo2) using (column_value)

这篇关于PL / SQL - 将两个结果集缓存到集合中并将它们连接在一起？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！