Oracle动态SQL语句
2023-09-11 14:21:02 时间
动态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 Server 中 EXEC全称execute 与 SP_EXECUTESQL动态执行sql代码语句 的区别
- oracle存储过程报错> ORA-24344: success with compilation error的原因,execute immediate 执行动态SQL
- 【转】Oracle之索引
- Oracle数据库:数据库操纵语言DML,插入insert into where,更新update where,删除delete where
- RDBMS SQL 编辑器 | MySQL、Oracle、MariaDB、SQLsever、SQLite、PostgreSQL
- Oracle ASM 翻译系列第六弹:高级知识 如何映射asmlib管理的盘到它对应的设备名
- oracle 列的归档,Oracle 开启或关闭归档模式
- oracle 12C wmsys.wm_concat()函数
- oracle 启动监听报错TNS-12547: TNS:lost contact
- 【Oracle】ORACLE SQL Developer不支持JAVA版本
- Windos系统用PL/SQL Developer软件进行Oracle数据库备份/迁移
- 《Oracle SQL疑难解析》——1.1 从表中查询数据
- 《Oracle PL/SQL程序设计(第5版)》一一1.2 PL/SQL的起源
- 《Oracle PL/SQL必知必会》——第1章 了解SQL
- 《Oracle PL/SQL必知必会》——2.2 起步
- ORACLE PL/SQL 程序包的创建与应用
- Oracle取查询结果数据的第一条记录SQL:
- oracle函数 nls_charset_id(c1)
- oracle函数 MAX([distinct|all]x)
- oracle一些常见的问题
- Oracle企业管理框架
- sql语言实践之自学SQL网(SQL Lesson12)
- oracle数据库创建表
- Oracle JDBC驱动安装到Maven本地仓库
- 微软狠挖甲骨文墙脚:从Oracle迁移到SQL Server免费!
- 深入内核:从Oracle ASM自动备份头块到ASMFD
- 【快讯】在线体验Oracle Database 12.2 SQL新特性
- [Oracle]如何为数据库设置Event(eg: ORA-00235)
- Oracle的 EXEC SQL CONTEXT学习
- SAP BI vs. Oracle BI
- sql-常见的SQL优化