Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to implement the stored procedure with input and output parameters in Oracle. It is very detailed and has certain reference value. Interested friends must finish reading it.

Begin / * the select statement cannot be executed directly here, but the update, delete and insert statements can be executed directly. The select statement cannot be executed in the session-level temporary table * / end. It is stated that the session-level temporary table must have a "execute immediate 'TRUNCATE TABLE table name'; otherwise, other session cannot drop (the TRUNCATE TABLE table name must be executed before the sentence" drop table table name ").

Create or replace procedure p_DevData (p_id IN varchar2, pageIndex in NUMBER,pageCount in NUMBER,totalCount in NUMBER,p_cur out sys_refcursor) AUTHID CURRENT_USERasbegin 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, robot_typeid number) On Commit Preserve Rows';-- creates session-level temporary table execute immediate strSql using pairid; 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, DEVICE_CODE varchar2, NAME varchar2) On Commit Preserve Rows' -- create session-level temporary table 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, DEVICE_CODE varchar2, NAME varchar2, type_name varchar2, YY_NUM varchar2, MM_NUM varchar2, Day_NUM varchar2, HH_NUM varchar2) On Commit Preserve Rows';-- creates session-level temporary tables 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 ('video does not have data'); elsif Num_Id=2 then dbms_output.put_line ('manhole cover data is not available'); 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';-- create session-level temporary table execute immediate' Commit' Insert into TEMP_Dev_data_Table select t.IDret.DEVICEcoded CODEretiont.NAMEret.typewritten namereage.YYroomNUM where s.DEVICE_BASE_INFOID=tab.ID group by to_char .MMroomNUM from t.Daywarning NUM from (select tab.ID,tab.NAME,tab.DEVICE_CODE,' smoke alarm'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 ('unknown type'); elsif Num_Id=5 then dbms_output.put_line ('unknown type'); else dbms_output.put_line ('unknown type'); end if; end if; end; if totalCount

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.