阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

215次阅读
没有评论

共计 3150 个字符,预计需要花费 8 分钟才能阅读完成。

记录一下 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;

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计3150字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中