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

Question

我有一个用 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;

我已经能够移植几乎所有的东西，但是在两个地方(见代码中的注释)，逻辑比旧的 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;
/

在此先感谢您的帮助!

Answer 1

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

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

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;
/

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

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

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

当你有一个密集的集合(1 和集合的 count 之间的所有索引都存在并具有值)时，这会起作用。如果您可能有一个稀疏集合，您可能希望在一个循环中使用 FIRST、NEXT 和 LAST，但这需要更多代码。