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

Oracle動態SQL語句