oracle 创建过程

create table test1 as select 1 id,'create table test2(id number,name varchar2(200));insert into test2 values(1,''a'');' sqltext from dual
  2  union all select 2,'truncate table test2;' from dual
  3  union all select 3,'insert into test2 select rownum,dbms_random.string(''l'',3) from dual connect by rownum<10;delete from test2 where id between 3 and 5;' from dual;

Table created

SQL>
SQL> create or replace function exc_sql(str in varchar2)return varchar2
  2  as
  3  pragma autonomous_transaction;
  4  v_error varchar2(200);
  5  begin
  6  for i in 1..length(str)-length(replace(str,';')) loop
  7    execute immediate substr(str,instr(';'||str,';',1,i),instr(str,';',1,i)-instr(';'||str,';',1,i));
  8  end loop;
  9  commit;
10  return 'ok';
11  exception
12  when others then
13  v_error:=sqlerrm;
14  return v_error;
15  end;
16  /

Function created

SQL> select test1.*,exc_sql(sqltext) from test1 where id=1;

        ID SQLTEXT                                                                          EXC_SQL(SQLTEXT)
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 create table test2(id number,name varchar2(200));insert into test2 values(1,'a') ok

SQL> select * from test2;

        ID NAME
---------- --------------------------------------------------------------------------------
         1 a

SQL> select test1.*,exc_sql(sqltext) from test1 where id>1;

        ID SQLTEXT                                                                          EXC_SQL(SQLTEXT)
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         2 truncate table test2;                                                            ok
         3 insert into test2 select rownum,dbms_random.string('l',3) from dual connect by r ok

SQL> select * from test2;

        ID NAME
---------- --------------------------------------------------------------------------------
         1 rqk
         2 qfj
         6 ucn
         7 ppa
         8 gwe
         9 dds

6 rows selected

SQL>