我用这个存储过程的实现的功能是,将两个查询语句的查询结果进行判断整合到一起:查询一个结果集,如果这
个结果集的某一个符合条件:sysdate - etime>4/144 and astatus = 1,需要修改这条结果集的其中两个字段值
create or replace procedure queue_info_proc
(
startpage in number(5);
endpage in number(5);
etime out bomc.tfa_alarm_his.event_time%type;
astatus out bomc.tfa_alarm_his.active_status%type;
asid out bomc.iptfa_alarm_hnvoice_queue.alarm_sid%type;
rdate out bomc.iptfa_alarm_hnvoice_queue.read_date%type;
sdate out bomc.iptfa_alarm_hnvoice_queue.send_date%type;
aip out iptfa_alarm_hnvoice_queue.alarm_ne_ip%type;
tel1 out iptfa_alarm_hnvoice_queue.telno1%type;
tel2 out iptfa_alarm_hnvoice_queue.telno2%type;
tel3 out iptfa_alarm_hnvoice_queue.telno3%type;
sstaus out iptfa_alarm_hnvoice_queue.send_status%type;
)as
--变量
begin
--此SQL查询语句是分页查询出所有信息
select * from
(select a.*, rownum r from(
select
his.alarm_title,his.alarm_text,q.alarm_ne_ip,q.alarm_sid,his.event_time,q.telno1,q.telno2,q.telno3,q.read_date,q.send_status,q.send_date
from Iptfa_alarm_hnvoice_queue q ,bomc.tfa_alarm_his his where q.Alarm_orgfp=(his.fp0||'_'||his.fp1||'_'||his.fp2||'_'||his.fp3) order by Call_id desc
) a
where rownum <= startpage) b where r >endpage
into aip,asid,etime,tel1,tel2,tel3,rdate,sstaus,sdate,astatus
-- exception
-- WHEN NO_DATA_FOUND THEN
-- dbms_output.put_line('没有找到告警队列表信息!');
--上面SQL查出的是可能有N条的结果集,我要判断其中每一条结果集是否符合下列条件
--如果下列条件的要替换read_date,send_date这两个值
--下面的是胡写的,不知道该怎么写了,请高人指点
if sysdate - etime>4/144 and astatus = 1
then
--call_id是iptfa_alarm_hnvoice_queue表的自增主键
select read_date,send_date into rdate,sdate from iptfa_alarm_hnvoice_queue
where call_id = (select max(call_id) from iptfa_alarm_hnvoice_queue where alarm_sid=asid)
exception
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('没有找到最新的告警发生和结束时间!');
end queue_info_proc;
------解决方案--------------------
select * from
(select a.*, rownum r from(
select
his.alarm_title,his.alarm_text,q.alarm_ne_ip,q.alarm_sid,his.event_time,q.telno1,q.telno2,q.telno3,q.read_date,q.send_status,q.send_date
from Iptfa_alarm_hnvoice_queue q ,bomc.tfa_alarm_his his where q.Alarm_orgfp=(his.fp0
------解决方案--------------------
'_'
------解决方案--------------------
his.fp1
------解决方案--------------------
'_'
------解决方案--------------------