forall 与 for loop 案例

create table a_tab(ver number,id number);
create table b_tab(ver number,id number);
set timing on
DECLARE
l_add INTEGER:=0;
BEGIN
FOR i in 1..200000 LOOP
l_add:=l_add+i;
EXECUTE IMMEDIATE 'insert INTO a_tab values(:a,:b)' USING i,l_add;
END LOOP;
COMMIT;
END;
/

DECLARE
v_sql VARCHAR2(4000);
v_tablename VARCHAR2(100) := 'b_tab';
BEGIN
v_sql :='
DECLARE
TYPE r_outtab is RECORD (ver NUMBER,id NUMBER);
TYPE t_outtab is TABLE of r_outtab INDEX by BINARY_INTEGER;
v_outtab t_outtab;
v_query VARCHAR2(30000);
BEGIN
v_query := ''SELECT ver,id from a_tab '';
EXECUTE IMMEDIATE v_query bulk collect INTO v_outtab;
forall i in v_outtab.FIRST .. v_outtab.LAST
INSERT INTO '|| v_tablename ||' VALUES v_outtab(i);
END;';
DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;
COMMIT;
END;
/

DECLARE
TYPE r_outtab is RECORD (
ver NUMBER,
id NUMBER);
TYPE t_outtab is TABLE of r_outtab INDEX by BINARY_INTEGER;
v_outtab t_outtab;
v_tablename VARCHAR2(100);
v_query VARCHAR2(30000);
BEGIN
v_tablename := 'b_tab';
v_query := 'SELECT ver,id from a_tab ';
EXECUTE IMMEDIATE v_query bulk collect INTO v_outtab;
FOR i in v_outtab.FIRST .. v_outtab.LAST LOOP
EXECUTE IMMEDIATE 'INSERT INTO ' || v_tablename || ' VALUES(:a,:b)' USING v_outtab(i).ver,v_outtab(i).id;
END LOOP;
END;
/

---forall 中使用execute immediate动态执行则报错。原因为非sql类型,动态PLSQL,但本人在11.2.0.3中运行并没有报错,记录下。
declare
type r_outtab is record (ver NUMBER,id NUMBER);
type t_outtab is table of r_outtab index by binary_integer;
v_outtab t_outtab;
v_query varchar2(30000);
v_tablename varchar2(100):='a_tab';
begin
v_query :='select ver,id from b_tab ' ;
execute immediate v_query bulk collect into v_outtab;
forall i in v_outtab.first .. v_outtab.last
execute immediate 'insert into '||v_tablename||' values (:a,:b)' using v_outtab(i).ver,v_outtab(i).id ;
end;
/
ERROR at line 12:
ORA-06550: line 12, column 10:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
/*表名动态,后面全要拼凑,因为那个集合是非sql类型的集合,无法拼凑的,用||是不行的,用using当然也不行,using必须要求sql类型*/

上一篇:MongoDB系列五(地理空间索引与查询).


下一篇:thinkphp Widget扩展