求一个存储过程的写法,我写了两个查询语句,但不知道怎么用存储过程连起来,请高手教我
我用这个存储过程的实现的功能是,将两个查询语句的查询结果进行判断整合到一起:查询一个结果集,如果这
个结果集的某一个符合条件: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
[解决办法]
'_'
------解决方案--------------------
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
这一部分也不可以的吧？ 多条结果集怎么可以直接into到 table.colum%type里的变量里呢
建议使用游标
[解决办法]
id 字段在子查询中加入，其余字段可以不查，以及修改的值，自己修改。
update Iptfa_alarm_hnvoice_queue q
set q.read_date=?,q.send_date=?
where exists(
select 1
from (
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)
and his.event_time<sysdate-4/144
and his.active_status=1
order by Call_id desc
) a
where rownum <= startpage
) b
where r >endpage
) a
where q.id = a.id
)
for cur_loop in (select aip,asid,etime,tel1,tel2,tel3,rdate,sstaus,sdate,astatus
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
) loop
if sysdate - cur_loop.etime>4/144 and astatus = 1 then
begin
--这里写后一段select 如... where aip=cur_loop.aip
end
end loop
