Oracle動態SQL語句，oraclesql語句

Oracle動態SQL語句，oraclesql語句

動態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語句問題

是這樣子的：
正常的SQL應該是這樣：
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME='EMP';
然後V_SQL:='';最外層也是有引號的
當表名是變數，但是我們查的時候是需要加上單引號的，如果最外面的單引號的話，則裡面的單引號就需要單引號再加單引號這樣來引用的。
所以，如果你測試你的V_SQL寫的正常不正常的話，可以用raise_application_error(-20201,V_SQL);查看，因為這樣輸出的是正常的sql的哦。
 
oracle預存程序中怎執行動態SQL語句 詳細??

有時需要在oracle 預存程序中執行動態SQL 陳述式 ，例如表名是動態，或欄位是動態，
或查詢命令是動態，可用下面的方法：
set serveroutput ondeclaren number;sql_stmt varchar2(50);
t varchar2(20);beginexecute immediate 'alter session set nls_date_format=''YYYYMMDD''';
t := 't_' || sysdate;
sql_stmt := 'select count(*) from ' || t;
execute immediate sql_stmt into n;
dbms_output.put_line('The number of rows of ' || t || ' is ' || n);end;
如果動態SQL 陳述式 很長很複雜，則可用包裝.
CREATE OR REPLACE PACKAGE test_pkgISTYPE cur_typ IS REF CURSOR;
PROCEDURE test_proc (v_table VARCHAR2,t_cur OUT cur_typ);END;/
CREATE OR REPLACE PACKAGE BODY test_pkgISPROCEDURE test_proc (v_table VARCHAR2,t_cur OUT cur_typ)ISsqlstr VARCHAR2(2000);BEGINsqlstr := 'SELECT * FROM '||v_table;
OPEN t_cur FOR sqlstr;END;END;/
在oracle 中大量匯入，匯出和刪除表名以某些字元開頭的表
spool c:\a.sql