表名动态,后面全要拼凑,因为那个集合是非sql类型的集合,无法拼凑的,用||是不行的,用using当然也不行,using必须要求sql类型
--方法,动态PLSQL
SQL> select * from t;
VER ID
---------- ----------
1 1
12 2
1 1
12 2
SQL>
SQL> --动态plsql解决,因为拼凑的是动态sql
SQL> declare
2 v_sql varchar2(4000);
3 v_tablename varchar2(100):='t';
4 begin
5 v_sql:='
6 declare
7 type r_outtab is record (ver NUMBER(5,0),id NUMBER(5,0));
8 type t_outtab is table of r_outtab index by binary_integer;
9 v_outtab t_outtab;
10 v_query varchar2(30000);
11 begin
12 v_query :=''select VER,TYPE from tt where rownum<10 '' ;
13 execute immediate v_query bulk collect into v_outtab;
14 forall i in v_outtab.first .. v_outtab.last
15 insert into '||v_tablename||' values v_outtab(i) ;
16 end;';
17 dbms_output.put_line(v_sql);
18 execute immediate v_sql;
19 commit;
20 end;
21 /
PL/SQL procedure successfully completed
SQL> select * from t;
VER ID
---------- ----------
1 1
12 2
1 1
12 2
1 1
12 2
6 rows selected