麻烦哪位高手,帮我看看,看如何优化,
create or replace procedure p_insert_qualification_client is
ishasclient number default 0;
ishasqulificlient number default 0;
cid number;
begin
--循环遍历临时表中的资格号码
for af in (select USRMSISDN,quaflag,accflag,bulkid,qualificationid from ESS_QUALIFICATION_IF where quaflag=0)
loop
--查询该号码是否在客户表中存在
select count(*) into ishasclient from ess_client ec where ec.usrmsisdn=af.usrmsisdn;
--若存在,则直接得到该号码的客户编号
if ishasclient>0 then
select c.clientid into cid from ess_client c where c.usrmsisdn=af.usrmsisdn;
end if;
--若不存在,则将该号码添加到客户表中,并记录下客户编号
if ishasclient<1 then
select sq_ess_client_id.nextval into cid from dual;
insert into ess_client(clientid,usrmsisdn,brand,isnotify,status,storetime) values(cid,af.usrmsisdn,(select s.brand from ess_segment s where af.usrmsisdn between s.startno and s.endno and rownum<2),1,1,sysdate);
end if;
--查询该号码在资格信息表中是否存在,是为了防止产生重复
select count(*) into ishasqulificlient from ess_qualification_client eqc where eqc.qualificationid = af.Qualificationid and eqc.usrmsisdn = af.usrmsisdn;
--查询该号码存在 在临时表中设置quaflag=2 (重复号码)
if ishasqulificlient>=1 then
update ESS_QUALIFICATION_IF q set q.quaflag=2 where q.usrmsisdn=af.usrmsisdn and q.qualificationid=af.qualificationid and quaflag=0;
end if;
--如果不存在,则做以下操作
if ishasqulificlient<1 then
--将该客户作为资格号码,记录到资格表中
insert into ess_qualification_client (QUALIFICATIONID,clientid,BULKID,usrmsisdn) values (af.Qualificationid,cid,af.bulkid,af.usrmsisdn);
--修改已处理标示
update ESS_QUALIFICATION_IF q set q.quaflag=1 where q.usrmsisdn=af.usrmsisdn and q.qualificationid=af.qualificationid and quaflag=0;
end if;
end loop;
commit;
end;
create or replace procedure p_insert_qualification_client is
ishasclient number default 0;
ishasqulificlient number default 0;
cid number;
begin
--循环遍历临时表中的资格号码
for af in (select USRMSISDN,quaflag,accflag,bulkid,qualificationid from ESS_QUALIFICATION_IF where quaflag=0)
loop
--查询该号码是否在客户表中存在
select count(*) into ishasclient from ess_client ec where ec.usrmsisdn=af.usrmsisdn;
--若存在,则直接得到该号码的客户编号
if ishasclient>0 then
select c.clientid into cid from ess_client c where c.usrmsisdn=af.usrmsisdn;
end if;
--若不存在,则将该号码添加到客户表中,并记录下客户编号
if ishasclient<1 then
select sq_ess_client_id.nextval into cid from dual;
insert into ess_client(clientid,usrmsisdn,brand,isnotify,status,storetime) values(cid,af.usrmsisdn,(select s.brand from ess_segment s where af.usrmsisdn between s.startno and s.endno and rownum<2),1,1,sysdate);
end if;
--查询该号码在资格信息表中是否存在,是为了防止产生重复
select count(*) into ishasqulificlient from ess_qualification_client eqc where eqc.qualificationid = af.Qualificationid and eqc.usrmsisdn = af.usrmsisdn;
--查询该号码存在 在临时表中设置quaflag=2 (重复号码)
if ishasqulificlient>=1 then
update ESS_QUALIFICATION_IF q set q.quaflag=2 where q.usrmsisdn=af.usrmsisdn and q.qualificationid=af.qualificationid and quaflag=0;
end if;
--如果不存在,则做以下操作
if ishasqulificlient<1 then
--将该客户作为资格号码,记录到资格表中
insert into ess_qualification_client (QUALIFICATIONID,clientid,BULKID,usrmsisdn) values (af.Qualificationid,cid,af.bulkid,af.usrmsisdn);
--修改已处理标示
update ESS_QUALIFICATION_IF q set q.quaflag=1 where q.usrmsisdn=af.usrmsisdn and q.qualificationid=af.qualificationid and quaflag=0;
end if;
end loop;
commit;
end;
update ESS_QUALIFICATION_IF q set q.quaflag=1 where q.usrmsisdn=af.usrmsisdn and q.qualificationid=af.qualificationid and quaflag=0;想这样的sql,如果只有usrmsisdn字段创建了索引,那你应该放到最外层,没记错的话where是从外层开始刷选的update ESS_QUALIFICATION_IF q set q.quaflag=1 where q.qualificationid=af.qualificationid and quaflag=0 and q.usrmsisdn=af.usrmsisdn;
个人建议。如果还是很慢的话,转到oracle版去吧