这篇文章主要介绍Oracle如何实现带输入输出参数存储过程，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

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

createorreplaceprocedurep_DevData(p_idINvarchar2(150),pageIndexinNUMBER,pageCountinNUMBER,totalCountinNUMBER,p_curoutsys_refcursor)AUTHIDCURRENT_USERasbegindeclareNum_Idnumber;toCountNUMBER;strSqlvarchar2(1000);beginstrSql:='insertintoTEMP_ROBOT_Idselectid,robot_typeidfromROBOTwhereID=:p_idandIS_DEL=0andSTATUS=1';executeimmediate'TRUNCATETABLETEMP_ROBOT_Id';executeimmediate'droptableTEMP_ROBOT_Id';executeimmediate'CreateGlobalTemporaryTableTEMP_ROBOT_Id(ro_idvarchar2(150),robot_typeidnumber)OnCommitPreserveRows';--创建会话级临时表executeimmediatestrSqlusingp_id;executeimmediate'Commit';executeimmediate'TRUNCATETABLETEMP_Dev_Table';executeimmediate'droptableTEMP_Dev_Table';executeimmediate'CreateGlobalTemporaryTableTEMP_Dev_Table(idvarchar2(150),DEVICE_CODEvarchar2(150),NAMEvarchar2(150))OnCommitPreserveRows';--创建会话级临时表executeimmediate'insertintoTEMP_Dev_Tableselectdistinctbai.ID,bai.DEVICE_CODE,bai.NAMEfromDEVICE_BASE_INFObaiWHEREbai.ROBOT_ID=(selectro_idfromTEMP_ROBOT_Id)andIS_DEL=0andSTATUS=1';executeimmediate'Commit';executeimmediate'TRUNCATETABLETEMP_Dev_data_Table';executeimmediate'droptableTEMP_Dev_data_Table';executeimmediate'CreateGlobalTemporaryTableTEMP_Dev_data_Table(idvarchar2(150),DEVICE_CODEvarchar2(150),NAMEvarchar2(150),type_namevarchar2(100),YY_NUMvarchar2(10),MM_NUMvarchar2(10),Day_NUMvarchar2(10),HH_NUMvarchar2(10))OnCommitPreserveRows';--创建会话级临时表selectCount(1)IntotoCountfromTEMP_ROBOT_Id;iftoCount>0thenselectrobot_typeidIntoNum_IdfromTEMP_ROBOT_Id;ifNum_Id=1thendbms_output.put_line('视频没有数据');elsifNum_Id=2thendbms_output.put_line('井盖数据不通');elsifNum_Id=3thenexecuteimmediate'TRUNCATETABLETEMP_SMOKE_ALARM_INFO';executeimmediate'droptableTEMP_SMOKE_ALARM_INFO';executeimmediate'CREATEGLOBALTEMPORARYtableTEMP_SMOKE_ALARM_INFOONCOMMITPreserveROWSasselect*fromSMOKE_ALARM_INFO';--创建会话级临时表executeimmediate'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_INFOswheres.DEVICE_BASE_INFOID=tab.IDgroupbyto_char(s.CREATE_DATE,'YYYY'),s.DEVICE_BASE_INFOID)asYY_NUMfromTEMP_Dev_Tabletab)twheret.yy_numisnotnull;Commit;elsifNum_Id=4thendbms_output.put_line('未知类型');elsifNum_Id=5thendbms_output.put_line('未知类型');elsedbms_output.put_line('未知类型');endif;endif;end;iftotalCount<=0thenopenp_curforselect*from(selectrow_.*,rownumrownum_from(selectt.*fromTEMP_Dev_data_Tabletorderbyt.iddesc)row_whererownum<=casewhenpageCount<>0thenpageCount*1elserownumend/*每页显示多少条*页数*/)whererownum_>=casewhenpageCount<>0then/*(页数-1)=0说明为第一页则(页数-1)*每页显示多少条否则(页数-1)*每页显示多少条+1*/casewhen(pageIndex-1)=0then((pageIndex-1)*1)else(((pageIndex-1)*pageCount)+1)endelserownum_end;/*(页数-1)*每页显示多少条+1*/elseopenp_curforselectCount(1)astotalCountfromTEMP_Dev_data_Table;endif;endp_DevData;

以上是“Oracle如何实现带输入输出参数存储过程”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！