create or replace procedure gsdz_922 as
begin
update gssjdzb set gsdrsjl=(select count(*) from gsgxgs.SHAR_ENTERPRISEBASEINFOCOLLECT where to_char(if_jlscsj,'yyyy/mm/dd')=to_char(sysdate-1,'yyyy/mm/dd')),gszsjl=(select count(*) from gsgxgs.SHAR_ENTERPRISEBASEINFOCOLLECT)
where sjbm='SHAR_ENTERPRISEBASEINFOCOLLECT'and to_char(tjrq,'yyyy/mm/dd')=to_char(sysdate-5,'yyyy/mm/dd');
commit;
end;
/
begin
update gssjdzb set gsdrsjl=(select count(*) from gsgxgs.SHAR_ENTERPRISEBASEINFOCOLLECT where to_char(if_jlscsj,'yyyy/mm/dd')=to_char(sysdate-1,'yyyy/mm/dd')),gszsjl=(select count(*) from gsgxgs.SHAR_ENTERPRISEBASEINFOCOLLECT)
where sjbm='SHAR_ENTERPRISEBASEINFOCOLLECT'and to_char(tjrq,'yyyy/mm/dd')=to_char(sysdate-5,'yyyy/mm/dd');
commit;
end;
/
建议先把两个count(*)先查出来,放到两个变量里面,
update的时候,set两个字段的值,分别等于这两个变量就行。
create or replace procedure gsdz_922 as
n_gsdrsjl number;
n_gszsjl number;
begin
select count(*)
into n_gsdrsjl
from gsgxgs.SHAR_ENTERPRISEBASEINFOCOLLECT
where to_char(if_jlscsj, 'yyyy/mm/dd') =
to_char(sysdate - 1, 'yyyy/mm/dd');
select count(*)
into n_gszsjl
from gsgxgs.SHAR_ENTERPRISEBASEINFOCOLLECT;
update gssjdzb
set gsdrsjl =n_gsdrsjl,
gszsjl =n_gszsjl
where sjbm = 'SHAR_ENTERPRISEBASEINFOCOLLECT'
and to_char(tjrq, 'yyyy/mm/dd') = to_char(sysdate - 5, 'yyyy/mm/dd');
commit;
end;
/