create or replace procedure kb_hm_jh_zf is
begin
--转移msq_daily_active
insert into kb_hmzt(HM,HMZT,HMTC,YXQZ,DJRQ)
(select msq_daily_active.BILL_ID,1 as jh,kb_tc.bh,msq_daily_active.DONE_DATE,sysdate from msq_daily_active,kb_tc where rownum<100 and To_char(msq_daily_active.plan_id)=kb_tc.tcbh and NOT EXISTS(select distinct kb_hmzt.HM from kb_hmzt where HMZT=1 and msq_daily_active.bill_id=kb_hmzt.hm));
--转移msq_daily_quit
insert into kb_hmzt(HM,HMZT,YXQZ,DJRQ)
(select msq_daily_quit.BILL_ID,3 as jh,msq_daily_quit.valid_date,sysdate from msq_daily_quit where NOT EXISTS(select distinct kb_hmzt.HM from kb_hmzt where HMZT=1 and msq_daily_quit.bill_id=kb_hmzt.hm));
end kb_hm_jh_zf;
上面时我写的存储过程, 100 条数据都要 1分钟
太慢了
有没有好的方法啊
begin
--转移msq_daily_active
insert into kb_hmzt(HM,HMZT,HMTC,YXQZ,DJRQ)
(select msq_daily_active.BILL_ID,1 as jh,kb_tc.bh,msq_daily_active.DONE_DATE,sysdate from msq_daily_active,kb_tc where rownum<100 and To_char(msq_daily_active.plan_id)=kb_tc.tcbh and NOT EXISTS(select distinct kb_hmzt.HM from kb_hmzt where HMZT=1 and msq_daily_active.bill_id=kb_hmzt.hm));
--转移msq_daily_quit
insert into kb_hmzt(HM,HMZT,YXQZ,DJRQ)
(select msq_daily_quit.BILL_ID,3 as jh,msq_daily_quit.valid_date,sysdate from msq_daily_quit where NOT EXISTS(select distinct kb_hmzt.HM from kb_hmzt where HMZT=1 and msq_daily_quit.bill_id=kb_hmzt.hm));
end kb_hm_jh_zf;
上面时我写的存储过程, 100 条数据都要 1分钟
太慢了
有没有好的方法啊
select msq_daily_active.BILL_ID,1 as jh,kb_tc.bh,msq_daily_active.DONE_DATE,sysdate from msq_daily_active,kb_tc where rownum<100 and To_char(msq_daily_active.plan_id)=kb_tc.tcbh and NOT EXISTS(select distinct kb_hmzt.HM from kb_hmzt where HMZT=1 and msq_daily_active.bill_id=kb_hmzt.hm);
cursor quit_temp is
select msq_daily_quit.BILL_ID,3 as jh,msq_daily_quit.valid_date,sysdate from msq_daily_quit where NOT EXISTS(select distinct kb_hmzt.HM from kb_hmzt where HMZT=1 and msq_daily_quit.bill_id=kb_hmzt.hm);
aid kb_hmzt.hm%type;
ajh kb_hmzt.hmzt%type;
atc kb_hmzt.hmtc%type;
aqz kb_hmzt.yxqz%type;
adt kb_hmzt.djrq%type;begin
--转移msq_daily_active
for a in active_temp loop
aid:=a.bill_id;
ajh:=a.jh;
atc:=a.bh;
aqz:=a.done_date;
adt:=a.sysdate;
insert into kb_hmzt(HM,HMZT,HMTC,YXQZ,DJRQ)
values(aid,ajh,atc,aqz,adt);
end loop;
--转移msq_daily_quit
for q in quit_temp loop
aid:=q.bill_id;
ajh:=q.jh;
aqz:=q.valid_date;
adt:=q.sysdate;
insert into kb_hmzt(HM,HMZT,YXQZ,DJRQ)
values(aid,ajh,aqz,adt);
end loop;
end kb_hm_jh_zf;用游标写 也很慢 大家帮帮忙 急~~
如何分析SQL语句--执行计划的查看
http://community.csdn.net/Expert/topic/5209/5209876.xml?temp=.2004969
别外如果msq_daily_active.plan_id有索引,使用To_char(msq_daily_active.plan_id)=kb_tc.tcbh 索引会失效,可以不用to_char 直接=就行了