Oracle带输入输出参数存储过程(包括sql分页功能)

所属分类: 数据库 / oracle 阅读数: 1593
收藏 0 赞 0 分享

记录一下,免得以后忘记了又要到处去找。

begin /*这里不能直接执行select语句但可以直接执行update、delete、insert语句*/ end里面不能接执行select语句,声明会话级临时表必须有“execute immediate 'TRUNCATE TABLE 表名';”这一句不然其他的session无法drop(TRUNCATE TABLE 表名 必须在 ‘drop table 表名' 这一句前执行)

create or replace procedure p_DevData(p_id IN varchar2(150),pageIndex in NUMBER,pageCount in NUMBER,totalCount in NUMBER,p_cur out sys_refcursor) 
AUTHID CURRENT_USER
as
begin
 declare
 Num_Id number;
 toCount NUMBER;
 strSql varchar2(1000);
 begin
 strSql:='insert into TEMP_ROBOT_Id select id,robot_typeid from ROBOT where ID=:p_id and IS_DEL=0 and STATUS=1';
 execute immediate 'TRUNCATE TABLE TEMP_ROBOT_Id';
 execute immediate 'drop table TEMP_ROBOT_Id';
 execute immediate 'Create Global Temporary Table TEMP_ROBOT_Id(ro_id varchar2(150),robot_typeid number)On Commit Preserve Rows';--创建会话级临时表
execute immediate strSql using p_id;
 execute immediate 'Commit';
 execute immediate 'TRUNCATE TABLE TEMP_Dev_Table';
 execute immediate 'drop table TEMP_Dev_Table ';
 execute immediate 'Create Global Temporary Table TEMP_Dev_Table (id varchar2(150),DEVICE_CODE varchar2(150),NAME varchar2(150))On Commit Preserve Rows';--创建会话级临时表
execute immediate 'insert into TEMP_Dev_Table select distinct bai.ID,bai.DEVICE_CODE,bai.NAME from DEVICE_BASE_INFO bai WHERE bai.ROBOT_ID =(select ro_id from TEMP_ROBOT_Id) and IS_DEL=0 and STATUS=1';
 execute immediate 'Commit';
 execute immediate 'TRUNCATE TABLE TEMP_Dev_data_Table';
 execute immediate 'drop table TEMP_Dev_data_Table ';
 execute immediate ' Create Global Temporary Table TEMP_Dev_data_Table (id varchar2(150),DEVICE_CODE varchar2(150),
 NAME varchar2(150),type_name varchar2(100),YY_NUM varchar2(10),MM_NUM varchar2(10),Day_NUM varchar2(10),
 HH_NUM varchar2(10) )On Commit Preserve Rows';--创建会话级临时表
select Count(1) Into toCount from TEMP_ROBOT_Id;
 if toCount>0 then
 select robot_typeid Into Num_Id from TEMP_ROBOT_Id;
 if Num_Id=1 then
 dbms_output.put_line('视频没有数据');
elsif Num_Id=2 then
 dbms_output.put_line('井盖数据不通');
elsif Num_Id=3 then
 execute immediate 'TRUNCATE TABLE TEMP_SMOKE_ALARM_INFO';
 execute immediate 'drop table TEMP_SMOKE_ALARM_INFO ';
 execute immediate 'CREATE GLOBAL TEMPORARY table TEMP_SMOKE_ALARM_INFO ON COMMIT Preserve ROWS as select * from SMOKE_ALARM_INFO';--创建会话级临时表
execute immediate 'Commit';
 insert into TEMP_Dev_data_Table select t.ID,t.DEVICE_CODE,t.NAME,t.type_name,t.YY_NUM,t.MM_NUM,t.Day_NUM,t.HH_NUM from (
 select tab.ID,tab.NAME,tab.DEVICE_CODE,'烟感报警'as type_name,
 (select count(s.DEVICE_BASE_INFOID) HH_NUM from TEMP_SMOKE_ALARM_INFO s where s.DEVICE_BASE_INFOID=tab.ID
 group by to_char(s.CREATE_DATE,'YYYY'),s.DEVICE_BASE_INFOID) as YY_NUM
 from TEMP_Dev_Table tab)t where t.yy_num is not null;
 Commit;
 elsif Num_Id=4 then
 dbms_output.put_line('未知类型');
elsif Num_Id=5 then
 dbms_output.put_line('未知类型');
else
 dbms_output.put_line('未知类型');
end if;
 end if; 
 end;
 if totalCount<=0 then 
 open p_cur for select * from (
 select row_.*, rownum rownum_
 from (
 select t.* from TEMP_Dev_data_Table t
 order by t.id desc
 ) row_
 where rownum <=case when pageCount <> 0 then pageCount*1 else rownum end /*每页显示多少条*页数*/
 )
where rownum_>=case 
 when pageCount <> 0 
 then 
 /*(页数-1)=0 说明为第一页则(页数-1)*每页显示多少条否则(页数-1)*每页显示多少条+1*/
 case when (pageIndex-1)=0 then ((pageIndex-1)*1) else (((pageIndex-1)*pageCount)+1) end
 else rownum_ end; /*(页数-1)*每页显示多少条+1*/
 else
 open p_cur for select Count(1) as totalCount from TEMP_Dev_data_Table;
 end if;
end p_DevData;

以上所述是小编给大家介绍的Oracle带输入输出参数存储过程(包括sql分页功能),希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

更多精彩内容其他人还在看

Oracle硬解析和软解析的区别分析

我们都知道在Oracle中每条SQL语句在执行之前都需要经过解析,这里面又分为软解析和硬解析。在Oracle中存在两种类型的SQL语句,一类为 DDL语句(数据定义语言),就是每次执行都需要进行硬解析。还有一类就是DML语句,他们会根据情况选择要么进行硬解析,要么进行软解析。
收藏 0 赞 0 分享

VMware中linux环境下oracle安装图文教程(一)

刚刚接触ORACLE的人来说,从那里学,如何学,有那些工具可以使用,应该执行什么操作,一定回感到无助。所以在学习使用ORACLE之前,首先来安装一下ORACLE 10g,在来掌握其基本工具。俗话说的好:工欲善其事,必先利其器。作为一个新手,我们还是先在VMware虚拟机里安装吧。
收藏 0 赞 0 分享

VMware中linux环境下oracle安装图文教程(二)ORACLE 10.2.05版本的升级补丁安装

这篇文章是VMware中linux环境下oracle安装图文教程系列的第二篇,主要介绍了ORACLE 10.2.05版本的升级补丁安装,需要的朋友可以参考下
收藏 0 赞 0 分享

教你如何静默安装ORACLE

这篇文章主要介绍了教你如何静默安装ORACLE,需要的朋友可以参考下
收藏 0 赞 0 分享

Oracle数据库TNS常见错误的解决方法汇总

这篇文章主要介绍了Oracle数据库TNS常见错误的解决方法,需要的朋友可以参考下
收藏 0 赞 0 分享

Oracle监听口令及监听器安全详解

这篇文章主要介绍了Oracle监听口令及监听器安全的解决方法,需要的朋友可以参考下
收藏 0 赞 0 分享

Oracle阻塞(blockingblocked)实例详解

这篇文章主要介绍了Oracle阻塞(blockingblocked),需要的朋友可以参考下
收藏 0 赞 0 分享

Oracle中插入特殊字符:&和'的解决方法汇总

这篇文章主要介绍了Oracle中插入特殊字符:&amp;和&#39;的解决方法,很有实用价值!需要的朋友可以参考下
收藏 0 赞 0 分享

Oracle 11g Dataguard参数详解

这篇文章主要介绍了Oracle 11g Dataguard参数详解,包含了独立参数、主库参数、备库参数的详细说明,需要的朋友可以参考下
收藏 0 赞 0 分享

Oracle CBO几种基本的查询转换详解

这篇文章主要介绍了Oracle CBO几种基本的查询转换详解,包含视图合并、子查询解嵌套、谓语前推、物化视图查询重写等内容,需要的朋友可以参考下
收藏 0 赞 0 分享
查看更多