我用这个存储过程的实现的功能是,将两个查询语句的查询结果进行判断整合到一起:查询一个结果集,如果这
个结果集的某一个符合条件: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;
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;
这一部分是oracle的变量声明
(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里的变量里呢
建议使用游标
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
)
有哪位高人能帮忙把两个SQL查询语句结合起来吗?
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
十楼说的基本都是对的,只是最后一段的select语句需要存到一个结果集中,如某一张表。存储过程中不能直接显示你select的结果。