求大神帮忙看看，这个存储存在运行的时候很慢，希望得到解决

mj845573397 2017-08-04 05:30:59

create or replace procedure pro_license is
num number;
v_sql varchar2(100);
v_unid license_jh.xk_id%type;
cursor temp_cursor is
select xk_id from license_jh;
begin
open temp_cursor;
loop
fetch temp_cursor
into v_unid;
exit when temp_cursor%notfound;
select count(*) into num from license where unid = '' || v_unid || '';

if num >= 1 then
update license a
set (a.AREACODE,
a.XZXKWS_CODE,
a.XZXKWS_NAME,
a.BXZXK_NAME,
a.BXZXK_LEGALMAN,
a.XZXK_DATE,
a.XZXK_JZQ,
a.CTIME,
a.DEPT,
a.PARENTDEPT,
a.PTIME,
a.XZXK_NR,
a.XZXK_JG,
a.DATAVERSION,
a.XZXK_GKLX,
a.XZXK_STATE,
a.DELETEFLAG,
a.STATUS,
a.XZXK_SPLB,
a.CNAME,
a.PNAME,
a.XZXK_REGINNAME,
a.XZXK_ORGNAME,
a.XZXK_ORGCODE,
a.BXZXK_TYPE) =

(select b.AREACODE,
'空',
b.xk_xmmc,
b.xk_xdr,
b.xk_fr,
to_char(to_date(xk_jdrq,'yyyy-Mm-dd HH24:Mi:SS'),'yyyy-mm-dd'),
b.xk_jzq,
b.sjc,
b.xzxk_orgcode,
substr(b.xzxk_orgcode, 1, 6),
b.sjc,
b.xk_nr,
b.xzxk_orgname,
b.bbh,
0,
0,
0,
1,
'普通',
b.n || 'admin',
b.n || 'admin',
e.name,
b.name,
b.n,
(case
when length(b.xk_xdr) <= 5 then
1
when length(b.xk_xdr) > 5 then
2
end)
from sys_department e,
(select c.parentid,
d.*,
c.name,
c.areacode || c.deptcode as n
from license_jh d, sys_department c
where d.xzxk_orgcode = c.id
and bbh = (select max(bbh)
from license_jh f
where f.xk_id = d.xk_id)) b
where e.id = b.parentid
and a.unid = b.xk_id)
where exists (select 1
from sys_department e,
(select c.parentid,
d.*,
c.name,
c.areacode || c.deptcode as n
from license_jh d, sys_department c
where d.xzxk_orgcode = c.id
and bbh = (select max(bbh)
from license_jh f
where f.xk_id = d.xk_id)) b
where e.id = b.parentid
and a.unid = b.xk_id);
commit;
else
insert into license a
(a.unid,
a.AREACODE,
a.XZXKWS_CODE,
a.XZXKWS_NAME,
a.BXZXK_NAME,
a.BXZXK_LEGALMAN,
a.XZXK_DATE,
a.XZXK_JZQ,
a.CTIME,
a.DEPT,
a.PARENTDEPT,
a.PTIME,
a.XZXK_NR,
a.XZXK_JG,
a.DATAVERSION,
a.XZXK_GKLX,
a.XZXK_STATE,
a.DELETEFLAG,
a.STATUS,
a.XZXK_SPLB,
a.CNAME,
a.PNAME,
a.XZXK_REGINNAME,
a.XZXK_ORGNAME,
a.XZXK_ORGCODE,
a.BXZXK_TYPE)

select b.xk_id,
b.AREACODE,
'空',
b.xk_xmmc,
b.xk_xdr,
b.xk_fr,
to_char(to_date(xk_jdrq,'yyyy-Mm-dd HH24:Mi:SS'),'yyyy-mm-dd'),
b.xk_jzq,
b.sjc,
b.xzxk_orgcode,
substr(b.xzxk_orgcode, 1, 6),
b.sjc,
b.xk_nr,
b.xzxk_orgname,
b.bbh,
0,
0,
0,
1,
'普通',
b.n || 'admin',
b.n || 'admin',
e.name,
b.name,
b.n,
(case
when length(b.xk_xdr) <= 5 then
1
when length(b.xk_xdr) > 5 then
2
end)
from sys_department e,
(select c.parentid,
d.*,
c.name,
c.areacode || c.deptcode as n
from license_jh d, sys_department c
where d.xzxk_orgcode = c.id
and bbh = (select max(bbh)
from license_jh f
where f.xk_id = d.xk_id)) b
where e.id = b.parentid
and b.xk_id = v_unid;
commit;
end if;
end loop;
close temp_cursor;
end;

把单个update和insert拿出来运行，速度还是挺快的，在这歌存储里面裕兴就很慢，而且会产生大量的归档日志，以至于归档空间不够用，希望大神们帮忙看看，怎样优化才能加快运行速度，以及减少归档日志的产生

...全文