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

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

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分页功能)，希望对大家有所帮助