动态sql insert

创建两个表一个是insert语句 一个是需要插入数据的表

create table T

(
sql VARCHAR2(200)

);

create table TEST
(
a1 VARCHAR2(100),
a2 VARCHAR2(100),
a3 VARCHAR2(100)
);

将insert 语句插入到T表:
insert into t (SQL)
values ('insert into test (a1,a2,a3) values
(''1001'',''tom''s book'',''oracle'')');
insert into t (SQL)
values ('insert into test (a1,a2,a3) values
(''1002'',''kk''s book'',''mysql'')');
insert into t (SQL)
values ('insert into test (a1,a2,a3) values
(''1003'',''mimi''de book'',''db2'')');
commit;

执行:

declare
  v_sql varchar2(200);
begin
  for cur in (select REGEXP_REPLACE(t.sql,
                      '([[:alpha:]])(\''{1})([[:alpha:]])',
                      '\1''''\3') vsql from t) loop    
    v_sql := cur.vsql;
    dbms_output.put_line(v_sql);
    execute immediate v_sql;
  end loop;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

注:此文是从论坛摘要而来