oracle 从 PL/SQL 中的过程返回值数组

Return an array of values from a procedure in PL/SQL

提问by Christian

I am looking for a way to return an array of values from a procedure in PL/SQL.

我正在寻找一种从 PL/SQL 中的过程返回值数组的方法。

The procedure has some input parameter. Based on these inputs, an select query will be created and executed. On the result of this query, some more validation and calculation will be executed. The result of these checks and calculations should be saved in a new array or table array that will then be returned from the procedure. This procedure will be called several times from another PL/SQL script, with different parameters in order to get different values and to different checks on these values.

该过程有一些输入参数。根据这些输入，将创建并执行选择查询。在此查询的结果上，将执行更多的验证和计算。这些检查和计算的结果应该保存在一个新的数组或表数组中，然后从过程中返回。这个过程将被另一个 PL/SQL 脚本调用多次，使用不同的参数以获得不同的值并对这些值进行不同的检查。

Is there a way to return an array from a procedure? I am looking for a solution that avoids unnecessary copying of data and is as fast as possible.

有没有办法从过程中返回一个数组？我正在寻找一种避免不必要的数据复制并尽可能快的解决方案。

I have read about temporary tables, PL/SQL tables, cursors and so on, but I am not sure, which is these is the best approach.

我已经阅读了有关临时表、PL/SQL 表、游标等的内容，但我不确定，这是最好的方法。

回答by Justin Cave

Can a function return an array (procedures have OUTparameters, functions return things)? Sure

函数可以返回数组OUT吗（过程有参数，函数返回东西）？当然

CREATE OR REPLACE PACKAGE some_pkg
AS
  TYPE my_assoc_array_typ IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;

  FUNCTION return_array( p_deptno IN emp.deptno%type )
    RETURN my_assoc_array_typ;
END;

CREATE OR REPLACE PACKAGE BODY some_pkg
AS
  FUNCTION return_array( p_deptno IN emp.deptno%type )
    RETURN my_assoc_array_typ
  IS
    l_emps my_assoc_array_typ;
  BEGIN
    SELECT *
      BULK COLLECT INTO l_emps
      FROM emp
     WHERE deptno = l_deptno;

    RETURN l_emps;
  END;
END;

Now, is that the most efficient approach? It might be, it might not be depending on your environment. If there was a single approach that was always more efficient than the alternatives, Oracle wouldn't bother implementing the alternatives.

现在，这是最有效的方法吗？可能是，也可能不取决于您的环境。如果有一种方法总是比替代方案更有效，Oracle 就不会费心实施替代方案。

Using associative arrays (or any PL/SQL collection type) means that your data is stored in the relatively expensive PGA memory on the server. If you have one session reading a few hundred rows into a collection, that's probably not a major concern. If, on the other hand, you have thousands of sessions simultaneously trying to work with collections that have multiple millions of rows, PL/SQL collections will almost certainly create a bottleneck on the database server where most of the server's RAM is now dedicated to supporting these collections. Of course, you can often work around these problems by fetching smaller numbers of rows into each collection and, presumably, allowing for subsequent calls to fetch subsequent sets of rows, but that means architecture changes.

使用关联数组（或任何 PL/SQL 集合类型）意味着您的数据存储在服务器上相对昂贵的 PGA 内存中。如果您有一个会话将几百行读入一个集合，这可能不是主要问题。另一方面，如果您有数千个会话同时尝试处理具有数百万行的集合，则 PL/SQL 集合几乎肯定会在数据库服务器上造成瓶颈，其中大部分服务器的 RAM 现在专用于支持这些集合。当然，您通常可以通过将较少数量的行提取到每个集合中来解决这些问题，并且大概允许后续调用来提取后续的行集，但这意味着架构发生了变化。

In contrast, data stored in a temporary table, just like data stored in a permanent table, may be in the buffer cache or may be on disk at any point in time. That makes a temporary table more appropriate when you want to manage larger data volumes. But it comes at the cost of performance that you'll likely be doing at least some reads from and writes to physical disk and that your temporary table data will be competing with other data for space in the buffer cache.

相反，存储在临时表中的数据，就像存储在永久表中的数据一样，可能在缓冲区缓存中，也可能在任何时间点在磁盘上。当您想要管理更大的数据量时，这使得临时表更合适。但它是以性能为代价的，您可能至少会对物理磁盘进行一些读取和写入操作，并且您的临时表数据将与其他数据竞争缓冲区缓存中的空间。

A cursor does not store data, it's just a pointer to a program (a compiled SQL statement) that can be executed to fetch the next row of data you want.

游标不存储数据，它只是一个指向程序（编译后的 SQL 语句）的指针，可以执行该程序以获取所需的下一行数据。