PROCEDURE jpt
IS
v_count number;
v_sort_id o_st_alert.o_st_id%type;
v_level number;
v_level_count number; CURSOR c_sort IS SELECT distinct o_st_id FROM o_st_alert; BEGIN
OPEN c_sort;
LOOP FETCH c_sort INTO v_sort_id;
EXIT WHEN c_sort%NOTFOUND;
v_level:=0;
v_level_count:=0;
v_count:=0;
select count(OPERA_SORT_ID) into v_count from ffm where OPERA_SORT_ID=v_sort_id;
--and sysdate - interval '24' hour<ACCP_DATE;
dbms_output.put_line(v_sort_id); select max(alert_nbr) into v_level from opera_sort_alert
where alert_nbr<v_count and opera_sort_id=v_sort_id;
select count(alert_level) into v_level_count from opera_alert_msg where alert_level<=v_level
and to_char(alert_date,'yyyymmdd')=to_char(sysdate,'yyyymmdd')
and opera_sort_id=v_sort_id;
dbms_output.put_line(v_level_count);
while v_level_count+1<=v_level loop
dbms_output.put_line(v_count);
insert into opera_alert_msg(oid,alert_level,alert_date,state) values(v_sort_id,v_level_count+1,sysdate,'wait');
v_level_count:=v_level_count+1;
end loop; END LOOP;
-- End processing.
CLOSE c_sort;END jpt; -- Procedure
IS
v_count number;
v_sort_id o_st_alert.o_st_id%type;
v_level number;
v_level_count number; CURSOR c_sort IS SELECT distinct o_st_id FROM o_st_alert; BEGIN
OPEN c_sort;
LOOP FETCH c_sort INTO v_sort_id;
EXIT WHEN c_sort%NOTFOUND;
v_level:=0;
v_level_count:=0;
v_count:=0;
select count(OPERA_SORT_ID) into v_count from ffm where OPERA_SORT_ID=v_sort_id;
--and sysdate - interval '24' hour<ACCP_DATE;
dbms_output.put_line(v_sort_id); select max(alert_nbr) into v_level from opera_sort_alert
where alert_nbr<v_count and opera_sort_id=v_sort_id;
select count(alert_level) into v_level_count from opera_alert_msg where alert_level<=v_level
and to_char(alert_date,'yyyymmdd')=to_char(sysdate,'yyyymmdd')
and opera_sort_id=v_sort_id;
dbms_output.put_line(v_level_count);
while v_level_count+1<=v_level loop
dbms_output.put_line(v_count);
insert into opera_alert_msg(oid,alert_level,alert_date,state) values(v_sort_id,v_level_count+1,sysdate,'wait');
v_level_count:=v_level_count+1;
end loop; END LOOP;
-- End processing.
CLOSE c_sort;END jpt; -- Procedure
select count(OPERA_SORT_ID) into v_count from ffm where OPERA_SORT_ID=v_sort_id;
这个语句可以和Cursor的SQl和写成一个。
select count(OPERA_SORT_ID),OPERA_SORT_ID from ffm
where exists(select 1 from o_st_alert where o_st_id = ffm.OPERA_SORT_ID)
group by ffm.OPERA_SORT_ID
这样比在Ucrsor的循环里面多次执行统计语句快点!
监察是否有实效的对象,并且把oracle升级到9.2.0.7
祝你好运!