创建两个表一个是insert语句 一个是需要插入数据的表
create table T
(sql VARCHAR2(200)
);
(
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;
执行:
declarev_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;