Oracle 12c用TABLE集合表达式查询Associative Array内容丢失问题

问题根因 #

你遇到的输出不一致问题核心来自BULK COLLECT INTO的执行语义：
当使用BULK COLLECT INTO向集合赋值时，Oracle会先清空目标集合的所有内容，再执行后面的SELECT查询，最后将查询结果写入已清空的目标集合。
你的语句SELECT t.StartDate, t.EndDate BULK COLLECT INTO l_tTest FROM TABLE(l_tTest) t中，执行第一步就把l_tTest清空了，后续执行TABLE(l_tTest)自然读不到任何数据，最终得到的集合就是空的。

解决方案 #

临时变量中转实现查询写回 #

如果需要将当前集合的查询结果写回自身，新增一个临时集合中转查询结果，再赋值给原集合即可：

DECLARE
  l_tTest table_test.pt_DateSpanTable;
  l_tTemp table_test.pt_DateSpanTable; -- 新增临时中转集合
  
  PROCEDURE lp_outputAArray (p_aaInput table_test.pt_DateSpanTable) IS
    l_nTableSize INTEGER;
  BEGIN
    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
  -- 初始化数据
  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 t.StartDate, t.EndDate
  BULK COLLECT INTO l_tTemp
  FROM TABLE(l_tTest) t;
  -- 再把临时集合赋值给原集合
  l_tTest := l_tTemp;
  
  lp_outputAArray(l_tTest);
END;
/

执行后两次输出的集合大小都为1，符合预期。

实现UNION ALL追加数据 #

要实现追加而非覆盖的需求，同样使用临时集合中转即可：

DECLARE
  l_tTest table_test.pt_DateSpanTable;
  l_tTemp table_test.pt_DateSpanTable;
BEGIN
  -- 初始化原有数据
  SELECT to_date('01/01/2000', 'MM/DD/YYYY'), to_date('01/01/2010', 'MM/DD/YYYY')
  BULK COLLECT INTO l_tTest
  FROM DUAL;

  -- 合并原有数据和新数据写入临时集合
  SELECT *
  BULK COLLECT INTO l_tTemp
  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
       );
  
  -- 替换原集合，完成追加
  l_tTest := l_tTemp;
  
  -- 验证：最终集合大小为2，包含两条记录
  dbms_output.put_line('最终大小：'||l_tTest.COUNT);
  FOR i IN 1..l_tTest.COUNT LOOP
    dbms_output.put_line(i||': '||to_char(l_tTest(i).StartDate, 'MM/DD/YYYY')||' - '||to_char(l_tTest(i).EndDate, 'MM/DD/YYYY'));
  END LOOP;
END;
/

如果你的场景不需要合并查询，仅需要将新查询结果追加到现有集合，12c还支持BULK COLLECT APPEND语法，无需中转变量直接追加：

SELECT to_date('01/01/2011', 'MM/DD/YYYY'), to_date('01/01/2019', 'MM/DD/YYYY')
BULK COLLECT APPEND INTO l_tTest
FROM DUAL;

内容的提问来源于stack exchange，提问作者Del