我是这样合起来的,但是说有编译错误,请问应该怎么合!!!!
create or replace procedure his_pro
as
declare cursor dm_xs is
select oid,f_xsdm from t_xsdm;
xs_rec dm_xs% rowtype;
begin
for xs_rec in dm_xs loop
insert into dm_wz_his(oid,f_cdxs) values (xs_rec.oid,xs_rec.f_xsdm);
end loop;
end;
as
declare cursor his_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
his_rec his_cur% rowtype;
begin
for his_rec in his_cur loop
update dm_wz_his set f_his_pc=his_rec.f_pc
where dm_wz_his.f_cdxs=his_rec.f_cdxs;
end loop;
end;
as
declare cursor pc_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_wz_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
and dm_dwjd.f_wzyy in ('03','04','05','06','14')
group by dm_dwjd.f_cdxs;
pc_rec pc_cur% rowtype;
begin
for pc_rec in pc_cur loop
update dm_wz_his set f_his_wz_pc=pc_rec.f_wz_pc
where dm_wz_his.f_cdxs=pc_rec.f_cdxs;
end loop;
end;
as
declare cursor near_cur is
select dm_dwjd.f_cdxs,max(dm_dwjd.f_rq) as f_rq_new from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
near_rec near_cur% rowtype;
begin
for near_rec in near_cur loop
update dm_wz_his set f_last_wz_rq=near_rec.f_rq_new
where dm_wz_his.f_cdxs=near_rec.f_cdxs;
end loop;
end;
create or replace procedure his_pro
as
declare cursor dm_xs is
select oid,f_xsdm from t_xsdm;
xs_rec dm_xs% rowtype;
begin
for xs_rec in dm_xs loop
insert into dm_wz_his(oid,f_cdxs) values (xs_rec.oid,xs_rec.f_xsdm);
end loop;
end;
as
declare cursor his_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
his_rec his_cur% rowtype;
begin
for his_rec in his_cur loop
update dm_wz_his set f_his_pc=his_rec.f_pc
where dm_wz_his.f_cdxs=his_rec.f_cdxs;
end loop;
end;
as
declare cursor pc_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_wz_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
and dm_dwjd.f_wzyy in ('03','04','05','06','14')
group by dm_dwjd.f_cdxs;
pc_rec pc_cur% rowtype;
begin
for pc_rec in pc_cur loop
update dm_wz_his set f_his_wz_pc=pc_rec.f_wz_pc
where dm_wz_his.f_cdxs=pc_rec.f_cdxs;
end loop;
end;
as
declare cursor near_cur is
select dm_dwjd.f_cdxs,max(dm_dwjd.f_rq) as f_rq_new from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
near_rec near_cur% rowtype;
begin
for near_rec in near_cur loop
update dm_wz_his set f_last_wz_rq=near_rec.f_rq_new
where dm_wz_his.f_cdxs=near_rec.f_cdxs;
end loop;
end;
begin里的部分也放在一起create or replace procedure his_pro as
cursor dm_xs is
select oid,f_xsdm from t_xsdm;
xs_rec dm_xs% rowtype;cursor his_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
his_rec his_cur% rowtype;cursor pc_cur is select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_wz_pc from dm_dwjd,dm_wz_his where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
and dm_dwjd.f_wzyy in ('03','04','05','06','14')
group by dm_dwjd.f_cdxs;
pc_rec pc_cur% rowtype;cursor near_cur is select dm_dwjd.f_cdxs,max(dm_dwjd.f_rq) as f_rq_new
from dm_dwjd,dm_wz_his where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
near_rec near_cur% rowtype;begin
for xs_rec in dm_xs loop
insert into dm_wz_his(oid,f_cdxs) values (xs_rec.oid,xs_rec.f_xsdm);
end loop;for his_rec in his_cur loop
update dm_wz_his set f_his_pc=his_rec.f_pc
where dm_wz_his.f_cdxs=his_rec.f_cdxs;
end loop;for pc_rec in pc_cur loop
update dm_wz_his set f_his_wz_pc=pc_rec.f_wz_pc
where dm_wz_his.f_cdxs=pc_rec.f_cdxs;
end loop;for near_rec in near_cur loop
update dm_wz_his set f_last_wz_rq=near_rec.f_rq_new
where dm_wz_his.f_cdxs=near_rec.f_cdxs;
end loop;end;
没测试,你试一下吧
as
cursor dm_xs is
select oid,f_xsdm from t_xsdm;cursor his_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
cursor pc_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_wz_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
and dm_dwjd.f_wzyy in ('03','04','05','06','14')
group by dm_dwjd.f_cdxs;
cursor near_cur is
select dm_dwjd.f_cdxs,max(dm_dwjd.f_rq) as f_rq_new from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
begin
for xs_rec in dm_xs loop
insert into dm_wz_his(oid,f_cdxs) values (xs_rec.oid,xs_rec.f_xsdm);
end loop;
for his_rec in his_cur loop
update dm_wz_his set f_his_pc=his_rec.f_pc
where dm_wz_his.f_cdxs=his_rec.f_cdxs;
end loop;
for pc_rec in pc_cur loop
update dm_wz_his set f_his_wz_pc=pc_rec.f_wz_pc
where dm_wz_his.f_cdxs=pc_rec.f_cdxs;
end loop; for near_rec in near_cur loop
update dm_wz_his set f_last_wz_rq=near_rec.f_rq_new
where dm_wz_his.f_cdxs=near_rec.f_cdxs;
end loop;
end;
/
xs_rec dm_xs% rowtype; --所有游标变量不用定义,自动隐式已定义了
as
cursor dm_xs is
select oid,f_xsdm from t_xsdm;
cursor his_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
cursor pc_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.oid) as f_wz_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
and dm_dwjd.f_wzyy is not null
group by dm_dwjd.f_cdxs;
cursor near_cur is
select dm_dwjd.f_cdxs,max(dm_dwjd.f_rq) as f_rq_new from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
group by dm_dwjd.f_cdxs;
cursor zc_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.f_sl) as f_zc_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
and dm_dwjd.f_wzyy is null
group by dm_dwjd.f_cdxs;
cursor day15_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.f_sl) as f_sl_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
and dm_dwjd.f_rq<='25-10月-2003' and dm_dwjd.f_rq>=to_date('2003-10-25','yyyy-mm-dd')-15
group by dm_dwjd.f_cdxs;
cursor day15_wz_cur is
select dm_dwjd.f_cdxs,count(dm_dwjd.f_sl) as f_sl_pc from dm_dwjd,dm_wz_his
where dm_dwjd.f_cdxs=dm_wz_his.f_cdxs
and dm_dwjd.f_rq<='25-10月-2003' and dm_dwjd.f_rq>=to_date('2003-10-25','yyyy-mm-dd')-15
and dm_dwjd.f_wzyy is not null
group by dm_dwjd.f_cdxs;
cursor wq_cur is
select f_cdxs,count(oid) as f_wz from his
where f_wzyy is not null
and oid in (select his.oid from his,dm_wz_his where his.f_cdxs=dm_wz_his.f_cdxs
and rownum<11)
group by his.f_cdxs; begin
for xs_rec in dm_xs loop
insert into dm_wz_his(oid,f_cdxs) values (xs_rec.oid,xs_rec.f_xsdm);
end loop;
for his_rec in his_cur loop
update dm_wz_his set f_his_pc=his_rec.f_pc
where dm_wz_his.f_cdxs=his_rec.f_cdxs;
end loop;
for pc_rec in pc_cur loop
update dm_wz_his set f_his_wz_pc=pc_rec.f_wz_pc
where dm_wz_his.f_cdxs=pc_rec.f_cdxs;
end loop;
for near_rec in near_cur loop
update dm_wz_his set f_last_wz_rq=near_rec.f_rq_new
where dm_wz_his.f_cdxs=near_rec.f_cdxs;
end loop;
for zc_rec in zc_cur loop
update dm_wz_his set f_zc_pc=zc_rec.f_zc_pc
where dm_wz_his.f_cdxs=zc_rec.f_cdxs;
end loop;
for wq_rec in wq_cur loop
update dm_wz_his set f_wz_pc10=wq_rec.f_wz
where dm_wz_his.f_cdxs=wq_rec.f_cdxs;
end loop;
end;