oracle 将元素从 PL/SQL 变量一次添加到集合变量中？

Add elements one at a time from PL/SQL variable into collection variable?

提问by Robert N

I have a routine written in T-SQL for SQL Server. We are migrating to Oracle so I am trying to port it to PL/SQL. Here is the T-SQL routine (simplified); note the use of the table-valued variable which, in Oracle, will become a "nested table" type PL/SQL variable. The main thrust of my question is on the best ways of working with such "collection" objects within PL/SQL. Several operations in the ported code (second code sample, below) are quite awkward, where they seemed a lot easier in the SQL Server original:

我有一个用 T-SQL 编写的用于 SQL Server 的例程。我们正在迁移到 Oracle，因此我正在尝试将其移植到 PL/SQL。这是T-SQL例程（简化）；注意表值变量的使用，它在 Oracle 中将成为“嵌套表”类型的 PL/SQL 变量。我的问题的主要重点是在 PL/SQL 中使用此类“集合”对象的最佳方法。移植代码（下面的第二个代码示例）中的几个操作非常笨拙，在 SQL Server 原始代码中它们似乎容易得多：

DECLARE @MyValueCollection TABLE( value VARCHAR(4000) );
DECLARE @valueForThisRow VARCHAR(4000);

DECLARE @dataItem1Val INT, @dataItem2Val INT, @dataItem3Val INT, @dataItem4Val INT;
DECLARE theCursor CURSOR FAST_FORWARD FOR
  SELECT DataItem1, DataItem2, DataItem3, DataItem4 FROM DataTable;

OPEN theCursor;

FETCH NEXT FROM theCursor INTO @dataItem1Val, @dataItem2Val, @dataItem3Val, @dataItem4Val;

WHILE @@FETCH_STATUS = 0
BEGIN
  -- About 50 lines of logic that evaluates @dataItem1Val, @dataItem2Val, @dataItem3Val, @dataItem4Val and constructs @valueForThisRow
  SET @valueForThisRow = 'whatever';

  -- !!! This is the row that seems to have no natural Oracle equivalent
  INSERT INTO @MyValueCollection VALUES(@valueForThisRow);  

  FETCH NEXT FROM theCursor INTO @dataItem1Val, @dataItem2Val, @dataItem3Val, @dataItem4Val;
END;

CLOSE theCursor;
DEALLOCATE theCursor;

-- !!! output all the results; this also seems harder than it needs to be in Oracle
SELECT * FROM @MyValueCollection;

I have been able to port pretty much everything, but in two places (see comments in the code), the logic is a lot more complex than the old SQL Server way, and I wonder if there might be, in Oracle, some more graceful way that is eluding me:

我已经能够移植几乎所有东西，但在两个地方（见代码中的注释），逻辑比旧的 SQL Server 方式复杂得多，我想知道在 Oracle 中是否可能有一些更优雅的逃避我的方式：

set serveroutput on; -- needed for DBMS_OUTPUT; see below

DECLARE
  TYPE StringList IS TABLE OF VARCHAR2(4000);
  myValueCollection StringList;
  dummyTempCollection StringList; -- needed for my kludge; see below
  valueForThisRow VARCHAR2(4000);
BEGIN
  -- build all the sql statements
  FOR c IN (
    SELECT DataItem1, DataItem2, DataItem3, DataItem4 FROM DataTable;
  )
  LOOP
    -- About 50 lines of logic that evaluates c.DataItem1, c.DataItem2, c.DataItem3, c.DataItem4 and constructs valueForThisRow
    valueForThisRow := 'whatever';

    -- This seems way harder than it should be; I would rather not need an extra dummy collection
    SELECT valueForThisRow BULK COLLECT INTO dummyTempCollection FROM dual;    -- overwrites content of dummy temp
    myValueCollection := myValueCollection MULTISET UNION dummyTempCollection; -- merges into main collection

  END LOOP;

  -- output all the results... again, there's no shorter/easier/more-compact/single-line equivalent?
  IF myValueCollection.COUNT > 0
  THEN
    FOR indx IN myValueCollection.FIRST .. myValueCollection.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE(myValueCollection(indx));
    END LOOP;
  END IF;
END;
/

Thanks in advance for any help!

在此先感谢您的帮助！

回答by Justin Cave

Personally, I'd take the "50 lines of logic", move it into a function that you call in your SQL statement, and then do a simple BULK COLLECTto load the data into your local collection.

就我个人而言，我会采用“50 行逻辑”，将其移动到您在 SQL 语句中调用的函数中，然后执行简单的BULK COLLECT操作将数据加载到您的本地集合中。

Assuming that you really want to load data element-by-element into the collection, you can simplify the code that loads the collection

假设你真的想将数据逐个元素加载到集合中，你可以简化加载集合的代码

DECLARE
  TYPE StringList IS TABLE OF VARCHAR2(4000);
  myValueCollection StringList := StringList();
  valueForThisRow VARCHAR2(4000);
BEGIN
  -- build all the sql statements
  FOR c IN (
    SELECT DataItem1, DataItem2, DataItem3, DataItem4 FROM DataTable;
  )
  LOOP
    -- About 50 lines of logic that evaluates c.DataItem1, c.DataItem2, c.DataItem3, c.DataItem4 and constructs valueForThisRow
    valueForThisRow := 'whatever';

    myValueCollection.extend();
    myValueCollection( myValueCollection.count ) := valueForThisRow;
  END LOOP;

  -- output all the results... again, there's no shorter/easier/more-compact/single-line equivalent?
  IF myValueCollection.COUNT > 0
  THEN
    FOR indx IN myValueCollection.FIRST .. myValueCollection.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE(myValueCollection(indx));
    END LOOP;
  END IF;
END;
/

If you declare the collection as an associative array, you could avoid calling extendto increase the size of the collection. If you know the number of elements that you are going to load into the collection, you could pass that to a single extendcall outside the loop. Potentially, you can also eliminate the valueForThisRowlocal variable and just operate on elements in the collection.

如果将集合声明为关联数组，则可以避免调用extend以增加集合的大小。如果您知道要加载到集合中的元素数量，则可以将其传递给extend循环外的单个调用。潜在地，您还可以消除valueForThisRow局部变量并仅对集合中的元素进行操作。

As for the code that processes the collection, what is it that you are really trying to do? It would be highly unusual for production code to write to dbms_outputand expect that anyone will see the output during normal processing. That will influence the way that you would write that code. Assuming that your intention is really to just call dbms_output, knowing that will generally send the data into the ether

至于处理集合的代码，你真正想要做什么？生产代码写入dbms_output并期望任何人都会在正常处理期间看到输出是非常不寻常的。这将影响您编写该代码的方式。假设您的意图真的只是调用dbms_output，知道通常会将数据发送到以太坊

FOR indx IN 1 .. myValueCollection.count
LOOP
  dbms_output.put_line( myValueCollection(indx) );
END LOOP;

This works when you have a dense collection (all indexes between 1 and the countof the collection exist and have values). If you might have a sparse collection, you would want to use FIRST, NEXT, and LASTin a loop but that's a bit more code.

当您有一个密集的集合（集合的 1 和 之间的所有索引count都存在并具有值）时，这会起作用。如果你可能有一个稀疏集合，你会想使用FIRST，NEXT以及LAST在一个循环，但是这是一个有点更多的代码。