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

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;