动态SQL语句oracle,Oracle动态SQL语句

动态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⑼0'

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';