这个语句运行效率低 请各位大神们帮忙改改
create or replace procedure wz.pr_t_342_purchase_insertdata(
ls_purplan_id in varchar2,ls_year_mon in varchar2,ls_plan_type in varchar2)
is
ls_orga_id varchar2(16);
ls_orga_name varchar2(32);
ls_remark varchar2(128);
cursor cursor1 is
select t3.material_id,t3.material_name,t3.specification,t3.measure_id,
sum(t2.req_amount) as req_quantity,
min(t2.need_day) as need_day,
count(*) as dept_num,
max(t2.req_amount) as max_req_amount
from wz.t_wrequirment t1,wz.t_drequirment t2,wz.t_material t3
where t1.status='6' and to_char(t1.apply_day,'yyyymm')>='200710'
and to_char(t1.apply_day,'yyyymm')<=ls_year_mon
and t1.requir_type=ls_plan_type
and t1.requir_id=t2.requir_id and t2.material_id=t3.material_id
and t2.purplan_id is null and t2.material_id is not null
and t2.req_amount>0 AND T2.REQ_AMOUNT IS NOT NULL and nvl(t2.status,'0')<>'B'
group by t3.material_id,t3.material_name,t3.specification,t3.measure_id;
cursor cursor2 is
select t2.material_id,t2.remark from wz.t_wrequirment t1,wz.t_drequirment t2
where t1.status='6' and to_char(t1.apply_day,'yyyymm')>='200710'
and to_char(t1.apply_day,'yyyymm')<=ls_year_mon
and t1.requir_type=ls_plan_type
and t1.requir_id=t2.requir_id
and t2.purplan_id is null and t2.material_id is not null
and t2.req_amount>0 AND T2.REQ_AMOUNT IS NOT NULL and nvl(t2.status,'0')<>'B'
and t2.remark is not null;
begin
for result1 in cursor1 loop
select distinct max(t1.orga_id),max(t1.orga_name) into ls_orga_id,ls_orga_name
from wz.t_wrequirment t1,wz.t_drequirment t2
where t1.status='6' and to_char(t1.apply_day,'yyyymm')>='200710'
and to_char(t1.apply_day,'yyyymm')<=ls_year_mon
and t1.requir_type=ls_plan_type
and t1.requir_id=t2.requir_id AND T2.REQ_AMOUNT IS NOT NULL and nvl(t2.status,'0')<>'B'
and t2.req_amount=result1.max_req_amount
and t2.material_id=result1.material_id
and t2.purplan_id is null;
ls_remark:='';
for result2 in cursor2 loop
if result2.material_id=result1.material_id then
ls_remark:=ls_remark || result2.remark;
end if;
end loop;
insert into wz.t_cg_plan (group_id,plan_type,purplan_id,year_mon,material_id,material_name,
specification,req_quantity,req_day,plan_quantity,remark,dept_num,orga_id,orga_name,measure_id,status)
values('lh',ls_plan_type,ls_purplan_id,ls_year_mon,
result1.material_id,result1.material_name,result1.specification,
result1.req_quantity,result1.need_day,result1.req_quantity,ls_remark,result1.dept_num,
ls_orga_id,ls_orga_name,result1.measure_id,'00');
end loop;
update wz.t_drequirment
set purchase_status='00',status='9',purplan_id=ls_purplan_id
where requir_id in (select requir_id from wz.t_wrequirment
where status='6' and to_char(apply_day,'yyyymm')>='200710'
and to_char(apply_day,'yyyymm')<=ls_year_mon
and requir_type=ls_plan_type)
and purplan_id is null and nvl(status,'0')<>'B' ;
commit;
exception
when others then
raise;
end pr_t_342_purchase_insertdata;
[解决办法]
建议不用游标，用临时表来操作性能会提升
[解决办法]
有select的语句我可以帮你优化一下。。不过你的先把表的把用到的表导出来，，发到我的
heigegaojian@163.com 一部分数据就行（测试）。。
msn:gjhohj@live.cn
