我希望是有办法通过一个语句或者事务执行,诸如select script from checkscript;把语句里面的内容复制粘贴到ORACLE客户端执行的办法不算。
写一个存储过程 ,循环一下 execute immediately 'script ';
用自治事务写个函数 SQL> 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
SQL> 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>