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

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

createorreplace procedure p_DevData(p_id IN varchar2(150),pageIndexinNUMBER,pageCountinNUMBER,totalCountinNUMBER,p_curoutsys_refcursor)

AUTHID CURRENT_USER

as

begin

declare

Num_Idnumber;

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 strSqlusingp_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';-- 创建会话级临时表selectCount(1)IntotoCountfromTEMP_ROBOT_Id;

iftoCount>0then

selectrobot_typeidIntoNum_IdfromTEMP_ROBOT_Id;

ifNum_Id=1then

dbms_output.put_line('视频没有数据');

elsifNum_Id=2then

dbms_output.put_line('井盖数据不通');

elsifNum_Id=3then

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';

insertintoTEMP_Dev_data_Tableselectt.ID,t.DEVICE_CODE,t.NAME,t.type_name,t.YY_NUM,t.MM_NUM,t.Day_NUM,t.HH_NUMfrom(

selecttab.ID,tab.NAME,tab.DEVICE_CODE,'烟感报警'astype_name,

(selectcount(s.DEVICE_BASE_INFOID)HH_NUMfromTEMP_SMOKE_ALARM_INFO swheres.DEVICE_BASE_INFOID=tab.ID

groupbyto_char(s.CREATE_DATE,'YYYY'),s.DEVICE_BASE_INFOID)asYY_NUM

fromTEMP_Dev_Table tab)twheret.yy_numisnotnull;

Commit;

elsifNum_Id=4then

dbms_output.put_line('未知类型');

elsifNum_Id=5then

dbms_output.put_line('未知类型');

else

dbms_output.put_line('未知类型');

endif;

endif;

end;

iftotalCount<=0then

open p_curforselect*from(

selectrow_.*,rownum rownum_

from(

selectt.*fromTEMP_Dev_data_Table t

orderbyt.id desc

)row_

whererownum<=casewhenpageCount<>0thenpageCount*1elserownumend/* 每页显示多少条 * 页数 */

)

whererownum_>=case

whenpageCount<>0

then

/*(页数 - 1)=0 说明为第一页则 (页数 - 1)* 每页显示多少条否则 (页数 - 1)* 每页显示多少条 + 1*/

casewhen(pageIndex-1)=0then((pageIndex-1)*1)else(((pageIndex-1)*pageCount)+1)end

elserownum_end;/*(页数 - 1)* 每页显示多少条 + 1*/

else

open p_curforselectCount(1)astotalCountfromTEMP_Dev_data_Table;

endif;

endp_DevData;

来源: http://www.linuxidc.com/Linux/2018-10/154860.htm