4:在在数据库A创建统计SQL执行时间的存储过程
create or replace procedure pro_test_insert_clxsg
is
v_string varchar2(100);
v_misecond number;
v_second number;
v_minute number;
v_starttime TIMESTAMP;
v_endtime TIMESTAMP;
v_excute_time number;
cursor c_source is select clgjid from clxsg ;
begin
for r_source in c_source loop
select systimestamp into v_starttime from dual;
insert into sa.clxsg@to_orcl131 select * from clxsg where clgjid=r_source.clgjid;
commit;
select systimestamp into v_endtime from dual; --zxx_excute_time
v_string := to_char(v_endtime-v_starttime);
v_misecond := to_number(SUBSTR(v_string,INSTR(v_string,' ')+10,3));
v_second := to_number(SUBSTR(v_string,INSTR(v_string,' ')+7,2));
v_minute := to_number(SUBSTR(v_string,INSTR(v_string,' ')+4,2));
v_excute_time := v_minute*60*1000+v_second*1000+v_misecond;
insert into sa.zxx_excute_time
values('CLXSGJ',v_excute_time,to_char(v_starttime,'yyyy-mm-dd hh24:mi:ssxff'),to_char(v_endtime,'yyyy-mm-dd hh24:mi:ssxff'));
commit;
end loop ;
end;
分别创建CLXSG 、CLWFX、 GJX表的插入存储过程。
pro_test_insert_clwfx
pro_test_insert_gjx