将BULK COLLECT插入对象表中

Question

当尝试使用BULK COLLECT语句时，我收到了错误信息ORA-00947: not enough values。

以下是一个示例脚本：

CREATE OR REPLACE 
TYPE company_t AS OBJECT ( 
   Company          VARCHAR2(30),
   ClientCnt            INTEGER   );
/

CREATE OR REPLACE 
TYPE company_set AS TABLE OF company_t;    
/

CREATE OR REPLACE 
FUNCTION piped_set (
  v_DateBegin IN DATE,
  v_DateEnd IN DATE
)
return NUMBER /*company_set pipelined*/ as
  v_buf company_t := company_t( NULL, NULL);
  atReport company_set;
  sql_stmt VARCHAR2(500) := '';
begin

select * BULK COLLECT INTO atReport
from (
   SELECT 'Descr1', 1 from dual
   UNION
   SELECT 'Descr2', 2 from dual ) ;

  return 1;
end;

错误发生在这一行：select * BULK COLLECT INTO atReport。

顺畅的PL/SQL处理方式没问题（所以不需要将其作为解决方案提及）。问题是如何使用BULK COLLECT将数据批量插入到用户表类型中。

- xacinay

Answer 1

你的company_set是一个对象表格，而你正在选择值，而不是由这些值组成的对象。以下代码可以编译通过：

select * BULK COLLECT INTO atReport
from (
   SELECT company_t('Descr1', 1) from dual
   UNION
   SELECT company_t('Descr2', 2) from dual ) ;

...但是运行时会抛出ORA-22950: cannot ORDER objects without MAP or ORDER method，因为union隐式地进行排序以识别和删除重复项，所以请改用union all：

select * BULK COLLECT INTO atReport
from (
   SELECT company_t('Descr1', 1) from dual
   UNION ALL
   SELECT company_t('Descr2', 2) from dual ) ;

- Alex Poole

---

谢谢，@Alex，你的代码真的很有帮助！作为问题的延续，是否可以将批量插入的结果作为管道函数的结果传递？目前，我将结果获取到atReport中，然后执行“FOR .. LOOP ( pipe_row (atReportRow) ) END LOOP;”。也许这部分也可以简化？ - xacinay

@xacinay - 不要使用 bulk collect，您可以使用游标循环代替 (for rec in (select company_t() as comp ...) loop pipe row rec.comp; end loop; 或类似语句)。我想性能会相似。如果您尝试并无法使其正常工作，则应该提出新问题。 - Alex Poole

这就是它的工作方式：for x in (select ..) pipe row ( company_t( x.Company, x.ClientCnt) ) )。我只是想知道，它是否可以更简单一些。无论如何，当前的结果已经很好了，谢谢！ - xacinay