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';
是这样子的：
正常的SQL应该是这样：
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME='EMP';
然后V_SQL:='';最外层也是有引号的
当表名是变量，但是我们查的时候是需要加上单引号的，如果最外面的单引号的话，则里面的单引号就需要单引号再加单引号这样来引用的。
所以，如果你测试你的V_SQL写的正常不正常的话，可以用raise_application_error(-20201,V_SQL);查看，因为这样输出的是正常的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