求大神帮忙添加数据库存储过程条件

[nick849779125]

在已有的存储过程中
create or replace procedure p_sjm_p2
as
date_id varchar(10);
str varchar(1024);
v_cdr number(10);
v_date varchar2(10);

cursor sjm_cur is select distinct substr(switch_id,1,3 ) switchid_name from sjm_to_f1_sett_hq;
cursor date_cur is
SELECT to_char(sysdate-1, 'yyyymmdd') dayname from dual union
SELECT to_char(sysdate-2, 'yyyymmdd') dayname from dual union
SELECT to_char(sysdate-3, 'yyyymmdd') dayname from dual union
SELECT to_char(sysdate-4, 'yyyymmdd') dayname from dual union
SELECT to_char(sysdate-5, 'yyyymmdd') dayname from dual union
SELECT to_char(sysdate-6, 'yyyymmdd') dayname from dual union
SELECT to_char(sysdate-7, 'yyyymmdd') dayname from dual union
SELECT to_char(sysdate-8, 'yyyymmdd') dayname from dual union
SELECT to_char(sysdate-9, 'yyyymmdd') dayname from dual union
SELECT to_char(sysdate-10, 'yyyymmdd') dayname from dual;

begin
date_id:=to_char(sysdate,'yyyymmdd');
delete from sjm_tmp2;
commit;

for SWITCHid in sjm_cur loop

for date_id in date_cur loop

str:='insert into sjm_tmp2(switch_id,date_id,num_cdr)
select '||SWITCHid.Switchid_Name||','||date_id.dayname||',
sum(num_cdr)
from sjm_to_f1_sett_hq where switch_id like '||chr(39)||SWITCHid.Switchid_Name||'%'||chr(39)||' and year_id=substr('||chr(39)||date_id.dayname||chr(39)||',1,4)
and month_id=substr('||chr(39)||date_id.dayname||chr(39)||',5,2) and day_id=substr('||chr(39)||date_id.dayname||chr(39)||',7,2)';
insert into pro_xq (sql) values(str);--test
commit;
begin
EXECUTE IMMEDIATE str;
commit;
exception
when others then
rollback;
end;

END LOOP ;

END LOOP;

for SWITCHid in sjm_cur loop

for date_id in date_cur loop

v_date:=to_char(sysdate-10,'YYYYMMDD');
if date_id.dayname=v_date then
str:='update sjm_tmp2 set cha_cdr=null,chayi_cdr=null where switch_id='||SWITCHid.Switchid_Name||' and date_id='||date_id.dayname;
insert into pro_xq (sql) values(str); --test
commit;
else

select num_cdr into v_cdr from sjm_tmp2 where date_id=to_char(to_date(date_id.dayname,'yyyymmdd')-1,'yyyymmdd')and switch_id=SWITCHid.Switchid_Name;
str:='update sjm_tmp2 set cha_cdr=num_cdr-'||v_cdr||',chayi_cdr=(round((num_cdr-'||v_cdr||')/num_cdr,2) * 100) ||'||chr(39)||'%'||chr(39)||'
where date_id='||date_id.dayname||' and switch_id='||SWITCHid.Switchid_Name;

insert into pro_xq (sql) values(str); --test
commit;
begin
EXECUTE IMMEDIATE str;
commit;
exception
when others then
rollback;
end;
end if;
END LOOP ;
END LOOP;
END;

加一个条件 ，
select day_id,sum(num_cdr) from sjm_to_f1_sett_hq
where called_area_code in(select area_code from zhjs_param.s_area

where prov_code in(select prov_code from zhjs_param.tp_province where prov_name in ('河北','河南','黑龙江','天津','吉林','辽宁','内蒙','山东','山西')))

and calling_area_code=573
and year_id=2015
and month_id=5
group by day_id
order by day_id;