Oracle Dynamic SQL statements

Dynamic SQL returns cursors:

Create or Replace procedure Proc_validityduequery (P_regioncode in number, p_pscode in number, p_o          Utputcode 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_curso R 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) t Hen ' yellow ' when (Sysdate-bd.maxvalidtill) > 0 Then ' Red ' end color, CA SE when bs.ptype = 1 Then ' water ' when bs.ptype = 2 Then ' gas ' end Pstype, Bd.auddate, To_char (bd.ma Xvalidtill, ' 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 where P_regioncode >0 then ' and oregioncode= ' | |                         P_regioncode Else ' End | | ' '|| case where p_pscode>0 then ' and pscode= ' | |                         P_pscode Else ' End | | ' '||                         Case-p_psclasscode<> ' 0 ' then ' and Psclasscodexc in (' | | p_psclasscode | | ') ' Else ' end | | ' '||                      Case-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 ISM Onitor = 1 and ptstatus = 0 ' | | case where p_pscode>0 then ' and pscode= ' | |                         P_pscode Else ' End | | ' '|| case where p_outputcode>0 then ' and outputcode= ' | |                          P_outputcode Else ' End | | ' '|| When the case is 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-p_psclasscode<> ' 0 ' then ' and Psclasscodexc in (' | | p_psclasscode | | ') ' Else ' end | | ' '||                     Case-p_attencode<> ' 0 ' then ' and Attentiondegreecode in (' | | p_attencode | | ') ' Else ' end | | ' ) Vo WherE vb.pscode = Vo.pscode) BS, (select Pscode, Outputcode, Pollutantname, Pollutantcode, AUDDATE,MAXV Alidtill, Approvename, chkname from V_scenecommonite where checkresult= ' 1 ' | | case where p_pscode>0 then ' and pscode= ' | |            P_pscode Else ' End | | ' '|| case where p_outputcode>0 then ' and outputcode= ' | |             P_outputcode Else ' End | | ' '||                      case where 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 Sy         Sdate>maxvalidtill ' Else ' end | | ' ) BD Where Bs.pscode=bd.pscode and Bs.outputcode=bd.outputcode order by Bd.maxvalidtill Desc, Bs.reg   Ioncode, Bs.pscode, Bs.outputcode ';  End Proc_validityduequery;

2. Creating temporary tables dynamically

--Create temporary table--determine if there is a select count (*) into the v_num from User_tables where Table_name=upper (' zstemptable ');--if not present, create if v_num=0 t Hen execute immediate ' create global temporary table zstemptable (ID number (1), Reviseddata number (20,6)) on COM MIT preserve rows '; end if;--Insert Data execute immediate ' insert into zstemptable select 1,revisedstrength from (sele CT 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 '; --Query data execute immediate ' SELECT COUNT (*) from zstemptable where id=1 ' into v_hisstrength; --Clear table information execute immediate ' TRUNCATE TABLE zstemptable ';

Oracle Dynamic SQL statements