FORALL 循环中的多条 SQL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9702699/
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
Multiple SQL statements in FORALL loop
提问by Asha Koshti
I want to insert in Different tables with only single FORALL Loop in oracle.but FORALL don't support it.any idea how can i do it??
我想在 oracle 中只用一个 FORALL 循环插入不同的表。但是 FORALL 不支持它。知道我该怎么做吗？
create or replace PROCEDURE test IS
TYPE avl_web_details IS TABLE OF available_web_details%ROWTYPE;
var_avl_web_details avl_web_details := avl_web_details();
UNIONTABLE VARCHAR2(30000);
TYPE RepCurTyp IS REF CURSOR;
Rep_cv RepCurTyp;
BEGIN
UNIONTABLE := '';
execute immediate 'update tbl_used_webuda1 set flag=1';
FOR tbl IN (SELECT tablename FROM tbl_used_webuda1 where flag=1)
LOOP
UNIONTABLE := UNIONTABLE || 'select *' || ' from ' || tbl.tablename || 'union all ';
END LOOP;
IF (LENGTH(UNIONTABLE) > 10) THEN
UNIONTABLE := '( ' || SUBSTR(UNIONTABLE,1, length(UNIONTABLE)-10) || ' ) ';
end if;
OPEN Rep_cv FOR 'select from_unixtime("5mintime") as "5mintime",username,host,src_zone,domain,dst_zone,content,category,url,hits ,bytes,appid ,application,categorytype,usergroup from'|| uniontable;
LOOP
FETCH Rep_cv BULK COLLECT INTO var_avl_web_details LIMIT 200000;
FORALL i IN 1..var_avl_web_details.COUNT
insert into available_web_details values var_avl_web_details(i);
insert into web_ap_ca_co_do_ur_us_5min values(ts ,var_avl_web_details(i).application ,var_avl_web_details(i).category ,var_avl_web_details(i).content ,var_avl_web_details(i).domain ,var_avl_web_details(i).dst_zone ,var_avl_web_details(i).url ,var_avl_web_details(i).username ,var_avl_web_details(i).hits,var_avl_web_details(i).bytes,var_avl_web_details(i).appid);
insert into web_user_5min values(ts,var_avl_web_details(i).username,var_avl_web_details(i).hits,var_avl_web_details(i).bytes,var_avl_web_details(i).appid);
EXIT WHEN Rep_cv%NOTFOUND;
end loop;
close rep_cv;
end;**
回答by Tony Andrews
FORALL is not a "loop command", it is part of the syntax for a bulk insert statement. So the correct solution is to write the FORALL clause with each insert:
FORALL 不是“循环命令”，它是批量插入语句语法的一部分。所以正确的解决方案是在每次插入时编写 FORALL 子句：
FORALL i IN 1..var_avl_web_details.COUNT
insert into available_web_details values var_avl_web_details(i);
FORALL i IN 1..var_avl_web_details.COUNT
insert into web_ap_ca_co_do_ur_us_5min values(ts ,var_avl_web_details(i).application ,var_avl_web_details(i).category ,var_avl_web_details(i).content ,var_avl_web_details(i).domain ,var_avl_web_details(i).dst_zone ,var_avl_web_details(i).url ,var_avl_web_details(i).username ,var_avl_web_details(i).hits,var_avl_web_details(i).bytes,var_avl_web_details(i).appid);
FORALL i IN 1..var_avl_web_details.COUNT
insert into web_user_5min values(ts,var_avl_web_details(i).username,var_avl_web_details(i).hits,var_avl_web_details(i).bytes,var_avl_web_details(i).appid);
回答by niteshade
No need to call FORALL many times. Just use INSERT ALL DML statement.
无需多次调用 FORALL。只需使用 INSERT ALL DML 语句。
forall i in v_list.first..v_list.last
insert all
into t116 (id) values (v_list(i))
into t117 (id) values (v_list(i))
select 1 from dual;
