oracle 批量收集到对象表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16610733/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BULK COLLECT into a table of objects
提问by xacinay
When attempting to use a BULK COLLECTstatement I got error ORA-00947: not enough values.
尝试使用BULK COLLECT语句时出现错误ORA-00947: not enough values。
An example script:
一个示例脚本：
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;
The error occurs at the line select * BULK COLLECT INTO atReport.
错误发生在行上select * BULK COLLECT INTO atReport。
Straight PL/SQL works fine by the way (so no need to mention it as a solution). Usage of BULK COLLECTinto a user table type is the question.
顺便说一句，直接 PL/SQL 工作正常（因此无需将其作为解决方案提及）。使用BULK COLLECTinto 用户表类型是个问题。
回答by Alex Poole
Your company_setis a table of objects, and you're selecting values, not objects comprised of those values. This will compile:
您company_set是一个对象表，您正在选择值，而不是由这些值组成的对象。这将编译：
select * BULK COLLECT INTO atReport
from (
SELECT company_t('Descr1', 1) from dual
UNION
SELECT company_t('Descr2', 2) from dual ) ;
... but when run will throw ORA-22950: cannot ORDER objects without MAP or ORDER methodbecause the uniondoes implicit ordering to identify and remove duplicates, so use union allinstead:
...但是当 run 会抛出时，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 ) ;
