oracle 批量收集到特定的收集列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10335182/
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 specific columns of collection
提问by S1eth
TYPE t_project_financial_table IS TABLE OF project_financial%ROWTYPE;
g_project_financial_table t_project_financial_table;
The table project_financial has 15 columns. The select statement returns 2 columns. Can I bulk collect into 2 specific columns of the collection g_project_financial_table and leave the other columns null or do I have to bulk collect into 2 varrays (for each column) and loop over those to get the values of the 2 columns into the collection g_project_financial_table.
表 project_financial 有 15 列。select 语句返回 2 列。我是否可以批量收集到集合 g_project_financial_table 的 2 个特定列并将其他列保留为空，或者我是否必须批量收集到 2 个 varrays（对于每列）并循环遍历这些以将 2 列的值放入集合 g_project_financial_table 中。
Something like BULK COLLECT INTO g_project_financial_table.column3, g_project_financial_table.column8 ?
像 BULK COLLECT INTO g_project_financial_table.column3, g_project_financial_table.column8 之类的东西？
SELECT k.tag,
(SELECT pa.available
FROM pers_account pa
WHERE pa.valid_from =
(SELECT MAX(pa2.valid_from)
FROM pers_account pa2
WHERE pa2.valid_from <= k.tag)) AS available
BULK COLLECT INTO g_project_financial_table ???????
FROM kalender k
WHERE k.tag BETWEEN to_date('20120430','YYYYMMDD')
AND to_date('20120504','YYYYMMDD')
AND k.ist_werktag = 1
ORDER BY k.tag;
回答by winkbrace
You know that for large date ranges the performance of this query is becoming an issue, because you execute 2 extra queries for each row, right?
您知道对于大的日期范围，此查询的性能正在成为一个问题，因为您为每一行执行 2 个额外的查询，对吗？
Anyway, my solution would be to collect into 2 collections if you want to bulk insert. But honoustly, this doesn't look like you are going to insert huge numbers of rows, so it might be easiest to just use a normal insert. Here's the bulk insert anyway.
无论如何，如果您想批量插入，我的解决方案是收集到 2 个集合中。但老实说，这看起来不像您要插入大量行，因此使用普通插入可能最简单。无论如何，这是批量插入。
create or replace procedure add_days_to_financial(p_date_from in date, p_date_to in date)
as
cursor cur_kalender
is
SELECT k.tag,
(SELECT pa.available
FROM pers_account pa
WHERE pa.valid_from =
(SELECT MAX(pa2.valid_from)
FROM pers_account pa2
WHERE pa2.valid_from <= k.tag)) AS available
FROM kalender k
WHERE k.tag BETWEEN p_date_from and p_date_to
AND k.ist_werktag = 1
ORDER BY k.tag;
type t_tag is table of kalender.tag%type;
type t_available is table of kalender.available%type;
arr_tag t_tag;
arr_available t_available;
begin
open cur_kalender;
loop
fetch cur_kalender bulk collect into arr_tag, arr_available limit 500;
forall i in arr_tag.first .. arr_tag.last
insert into project_financial
(tag, available)
values
(arr_tag(i), arr_available(i));
commit;
exit when cur_kalender%notfound;
end loop;
close cur_kalender;
commit;
exception
when others then
-- log?
raise;
end;
回答by Justin Cave
You cannot collect data into just 2 columns of a 15 column record. You could, however, add 13 additional NULL columns in the appropriate positions to your SELECTstatement. Or, as you suggested, you could bulk collect the data into two different collections.
您不能仅将数据收集到 15 列记录的 2 列中。但是，您可以在SELECT语句的适当位置添加 13 个额外的 NULL 列。或者，如您所建议的，您可以将数据批量收集到两个不同的集合中。
