使用表集合表达式从关联数组中进行选择

Question

我似乎有一个问题，当使用表集合表达式从关联数组选择(注意:我使用Oracle 12c，所以这是允许的:Oracle文档)

以下面的"simple"例子:

首先，创建一个包，声明一条记录和关联数组:

CREATE OR REPLACE PACKAGE table_test
IS
TYPE pt_DateSpan IS RECORD
(
StartDate DATE,
EndDate DATE
);
TYPE pt_DateSpanTable IS TABLE OF pt_DateSpan INDEX BY PLS_INTEGER;
END;
/

然后，我写了下面的匿名块来测试功能:

DECLARE
l_tTest table_test.pt_DateSpanTable;

PROCEDURE lp_outputAArray (p_aaInput table_test.pt_DateSpanTable) IS
l_nTableSize INTEGER;
BEGIN
--I know I can use p_aaInput.COUNT, but I want to select using TABLE() to show that the functionality "works"
SELECT COUNT(*)
INTO l_nTableSize
FROM TABLE(p_aaInput);
dbms_output.put_line('Table Size: '||l_nTableSize);

FOR i IN 1..p_aaInput.COUNT LOOP
dbms_output.put_line(i||': '||to_char(p_aaInput(i).StartDate, 'MM/DD/YYYY')||' - '||to_char(p_aaInput(i).EndDate, 'MM/DD/YYYY'));
END LOOP;
END lp_outputAArray;
BEGIN
--ADD RECORD TO ASSOCIATIVE ARRAY
SELECT to_date('01/01/2000', 'MM/DD/YYYY'), to_date('01/01/2010', 'MM/DD/YYYY')
BULK COLLECT INTO l_tTest
FROM DUAL;

lp_outputAArray(l_tTest);  

--SELECT THE ASSOCIATIVE ARRAY INTO ITSELF
SELECT t.StartDate, t.EndDate
BULK COLLECT INTO l_tTest
FROM TABLE(l_tTest) t;

lp_outputAArray(l_tTest);
END;
/

该块产生如下输出:

Table Size: 1
1: 01/01/2000 - 01/01/2010
Table Size: 0

我的问题是为什么第二个输出与第一个不相同?

另外，我意识到我不需要在这个例子中使用BULK COLLECT，它是我实际代码的简化版本，它从实际表中执行SELECT。

我的最终目标是使用UNION ALL来允许我将值附加到我的关联数组中，而不是在执行一系列SELECT语句时替换它。像这样:

SELECT *
BULK COLLECT INTO l_tTest
FROM (SELECT t.StartDate, t.EndDate
FROM TABLE(l_tTest) t
UNION ALL
SELECT to_date('01/01/2011', 'MM/DD/YYYY'), to_date('01/01/2019', 'MM/DD/YYYY')
FROM DUAL);

我很感激你能提供的任何帮助。

Answer 1

当你使用:

SELECT ...
BULK COLLECT INTO array
FROM   ...

然后发生的第一件事是array和BULK COLLECT INTO被重新初始化为一个空数组。

因此，当您想在表集合表达式中使用它时，它已经是空的，并且不生成任何行。

---

相反，您可以使用非关联数组并在PL/SQL中使用MULTISET操作符:

CREATE OR REPLACE PACKAGE table_test
IS
TYPE range IS RECORD
(
StartDate DATE,
EndDate DATE
);
TYPE range_table IS TABLE OF range
--INDEX BY PLS_INTEGER
;
END;
/
DECLARE
l_ranges  table_test.range_table := table_test.range_table();
l_ranges2 table_test.range_table := table_test.range_table();
PROCEDURE output_ranges(
range_array table_test.range_table
)
IS
idx PLS_INTEGER;
BEGIN
dbms_output.put_line('Table Size: '||range_array.COUNT);

idx := range_array.FIRST;
LOOP
EXIT WHEN idx IS NULL;
dbms_output.put_line(
idx||': '||range_array(idx).StartDate||' - '||range_array(idx).EndDate
);
idx := range_array.NEXT(idx);
END LOOP;
END output_ranges;
BEGIN
l_ranges.EXTEND(2);
l_ranges(1) := table_test.range(DATE '2000-01-01', DATE '2001-01-01');
l_ranges(2) := table_test.range(DATE '2001-01-01', DATE '2002-01-01');
l_ranges2.EXTEND(2);
l_ranges2(1) := table_test.range(DATE '2002-01-01', DATE '2003-01-01');
l_ranges2(2) := table_test.range(DATE '2003-01-01', DATE '2004-01-01');
output_ranges(l_ranges);
output_ranges(l_ranges2);

l_ranges := l_ranges MULTISET UNION ALL l_ranges2;
output_ranges(l_ranges);
END;
/

输出:

Table Size: 2
1: 01-JAN-00 - 01-JAN-01
2: 01-JAN-01 - 01-JAN-02
Table Size: 2
1: 01-JAN-02 - 01-JAN-03
2: 01-JAN-03 - 01-JAN-04
Table Size: 4
1: 01-JAN-00 - 01-JAN-01
2: 01-JAN-01 - 01-JAN-02
3: 01-JAN-02 - 01-JAN-03
4: 01-JAN-03 - 01-JAN-04