我想在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).appid);
EXIT WHEN Rep_cv%NOTFOUND;
end loop;
close rep_cv;
end;**
解决方法
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).appid);
FORALL i IN 1..var_avl_web_details.COUNT
insert into web_user_5min values(ts,var_avl_web_details(i).appid);
