動態SQL返回游標：
create or replace procedure proc_ValidityDueQuery(
p_regioncode in number,
p_pscode in number,
p_outputcode in number,
p_pollutantType in number,
p_psclasscode in varchar2,
p_attencode in varchar2,
p_checkstatus in number,
p_auditstatus in number,
p_cursor out curdata
)
as
begin
open p_cursor for
'select bs.regioncode,
bs.regionname,
bs.pscode,
bs.psname,
bs.outputcode,
bs.outputname,
bs.ptype,
bd.chkname,
bd.approvename,
bd.pollutantname,
case when ((bd.maxvalidtill - sysdate) > 0 and (bd.maxvalidtill - sysdate) <= 7) then ''yellow''
when (sysdate - bd.maxvalidtill) > 0 then ''red''
end color,
case when bs.ptype = 1 then ''水'' when bs.ptype = 2 then ''氣'' end pstype,
bd.auddate,
to_char(bd.maxvalidtill, ''yyyy-MM-dd HH24:mi'') validtill
from (select vb.regioncode,
vb.regionname,
vb.pscode,
vb.psname,
vo.outputcode,
vo.outputname,
vo.ptype
from (select oregioncode regioncode, oregionname regionname, pscode, psname
from v_baseinfo
where 1 = 1
'|| case when p_regioncode >0 then 'and oregioncode='|| p_regioncode else '' end ||'
'|| case when p_pscode>0 then 'and pscode='|| p_pscode else '' end ||'
'|| case when p_psclasscode<>'0' then 'and psclasscodexc in ('|| p_psclasscode ||')' else '' end ||'
'|| case when p_attencode<>'0' then 'and attentiondegreecode in ('|| p_attencode ||')' else '' end ||'
) vb,
(select pscode, outputcode, outputname, inout, psstatus, ptstatus, ptype
from v_output t
where psstatus = 0
and ismonitor = 1
and ptstatus = 0
'|| case when p_pscode>0 then 'and pscode='|| p_pscode else '' end ||'
'|| case when p_outputcode>0 then 'and outputcode='|| p_outputcode else '' end ||'
'|| case when p_checkstatus=1 then 'and isgjkh=1 or isskkh=1'
when p_checkstatus=0 then 'and isgjkh=0 or isskkh=0'
else ''
end || '
and ptype ='|| p_pollutantType ||'
'|| case when p_psclasscode<>'0' then 'and psclasscodexc in ('|| p_psclasscode ||')' else '' end ||'
'|| case when p_attencode<>'0' then 'and attentiondegreecode in ('|| p_attencode ||')' else '' end ||'
) vo
where vb.pscode = vo.pscode) bs,
(select pscode, outputcode, pollutantname, pollutantcode,
auddate,maxvalidtill, approvename, chkname
from v_scenecommonite
where checkresult=''1''
'|| case when p_pscode>0 then 'and pscode='|| p_pscode else '' end ||'
'|| case when p_outputcode>0 then 'and outputcode='|| p_outputcode else '' end ||'
'|| case when p_auditstatus=1 then 'and ((maxvalidtill - sysdate) > 0 and (maxvalidtill - sysdate) <= 7)'
when p_auditstatus=2 then 'and maxvalidtill >= sysdate-90'
when p_auditstatus=3 then 'and sysdate>maxvalidtill'
else ''
end ||'
) bd
Where bs.pscode=bd.pscode
and bs.outputcode=bd.outputcode
order by bd.maxvalidtill desc, bs.regioncode, bs.pscode, bs.outputcode';
end proc_ValidityDueQuery;
2.動態創建臨時表
--創建臨時表
--判斷是否存在
select count(*)
into v_num
from user_tables
where table_name=upper('zstemptable');
--如果不存在則創建
if v_num=0 then
execute immediate 'create global temporary table zstemptable(id number(1),reviseddata number(20,6))
on commit preserve rows';
end if;
--插入數據
execute immediate 'insert into zstemptable
select 1,RevisedStrength
from (select RevisedStrength
from t_mod_gasfachourdata
where pscode = '|| p_pscode ||'
and outputcode = '|| p_outputcode ||'
and pollutantCode = '''|| p_pollutantcode ||'''
and monitorTime < to_date('''|| p_recordTime ||''',''yyyy-MM-dd HH24:mi:ss'')
and availableStatus = 0
order by MonitorTime desc)
where rownum = 1';
--查詢數據
execute immediate 'select count(*) from zstemptable where id=1' into v_hisstrength;
--清除表信息
execute immediate 'truncate table zstemptable';
