介绍
这篇文章主要介绍甲骨文如何实现带输入输出参数存储过程,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!
/*开始这里不能直接执行选择语句但可以直接执行更新、删除、插入语句*/结束里面不能接执行选择语句,声明会话级临时表必须有“<代码>执行立即& # 39;截断表的表名& # 39;> create 或是replace procedure p_DevData (p_id varchar2(150),拷贝pageIndex 拷贝数,pageCount 拷贝数,totalCount 拷贝数,p_cur out sys_refcursor),
AUTHID CURRENT_USER
作为
开始
,声明
,Num_Id 数量;
,toCount 数量;
,strSql varchar2 (1000);
,开始
,strSql:=& # 39; insert into TEMP_ROBOT_Id select id, robot_typeid 得到ROBOT where id=: p_id 以及IS_DEL=0,以及状态=1 & # 39;;
,execute immediate & # 39; TRUNCATE TABLE TEMP_ROBOT_Id& # 39;;
,execute immediate & # 39; drop table TEMP_ROBOT_Id& # 39;;
,execute immediate & # 39; Create Global Temporary Table TEMP_ROBOT_Id (ro_id varchar2 (150), robot_typeid 数字)提醒Commit Preserve 行# 39;;——创建会话级临时表
execute immediate strSql using p_id;
,execute immediate & # 39;提交# 39;;
,execute immediate & # 39; TRUNCATE TABLE TEMP_Dev_Table& # 39;;
,execute immediate & # 39; drop table TEMP_Dev_Table & # 39;;
,execute immediate & # 39; Create Global Temporary Table TEMP_Dev_Table (id varchar2 (150), DEVICE_CODE varchar2 (150), NAME varchar2(150))提醒Commit Preserve 行# 39;;——创建会话级临时表
execute immediate & # 39; insert into TEMP_Dev_Table select distinct bai.ID, bai.DEVICE_CODE, bai.NAME 得到DEVICE_BASE_INFO bai WHERE bai.ROBOT_ID =(select ro_id 得到TEMP_ROBOT_Id),以及IS_DEL=0,以及状态=1 & # 39;;
,execute immediate & # 39;提交# 39;;
,execute immediate & # 39; TRUNCATE TABLE TEMP_Dev_data_Table& # 39;;
,execute immediate & # 39; drop table TEMP_Dev_data_Table & # 39;;
,execute immediate & # 39;, 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),)提醒Commit Preserve 行# 39;;——创建会话级临时表
select 计数(1),Into toCount 得到TEMP_ROBOT_Id;
,if toCount> 0,那么
,select robot_typeid Into Num_Id 得到TEMP_ROBOT_Id;
,if Num_Id=1,
,dbms_output.put_line(& # 39;视频没有数据& # 39;);
elsif Num_Id=2,
,dbms_output.put_line(& # 39;井盖数据不通& # 39;);
elsif Num_Id=3,
,execute immediate & # 39; TRUNCATE TABLE TEMP_SMOKE_ALARM_INFO& # 39;;
,execute immediate & # 39; drop table TEMP_SMOKE_ALARM_INFO & # 39;;
,execute immediate & # 39; CREATE GLOBAL TEMPORARY table TEMP_SMOKE_ALARM_INFO 提醒COMMIT Preserve ROWS as select *,得到SMOKE_ALARM_INFO& # 39;;——创建会话级临时表
execute immediate & # 39;提交# 39;;
,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 得到(
,select tab.ID tab.NAME、tab.DEVICE_CODE & # 39;烟感报警& # 39;as type_name,
,(select 计数(s.DEVICE_BASE_INFOID), HH_NUM 得到TEMP_SMOKE_ALARM_INFO s where s.DEVICE_BASE_INFOID=tab.ID
,group by to_char (s.CREATE_DATE & # 39; yyyy # 39;), s.DEVICE_BASE_INFOID), as YY_NUM
,得到TEMP_Dev_Table 选项卡)t where t.yy_num is not 零;
,提交;
,elsif Num_Id=4,
,dbms_output.put_line(& # 39;未知类型& # 39;);
elsif Num_Id=5,
,dbms_output.put_line(& # 39;未知类型& # 39;);
其他的
,dbms_output.put_line(& # 39;未知类型& # 39;);
最终获得;如果;
,最终获得;如果;
,结束;
,if totalCount<=0, then
,open p_cur for select *,得到;
,select row_。*, rownum rownum_
,得到;
,select t。*,得到TEMP_Dev_data_Table t
,order by t.id desc
,)row_
,where rownum & lt;=case when pageCount & lt;祝辞,0,then pageCount * 1, else rownum 最终获得/*每页显示多少条*页数*/,)
where rownum_>=case
,when pageCount & lt;祝辞,0,
,then
null
null
null
null
null
null
null
