Tips for using temporary tables in Oracle stored procedures

Tips for using temporary tables in Oracle stored procedures 1. Oracle temporary table knowledge in Oracle, temporary tables are divided into SESSION and TRANSACTION. SESSION-level temporary table data exists throughout the SESSION, the temporary table data at the TRANSACTION level disappears after the TRANACTION ends, that is, the temporary table data at the COMMIT/ROLLBACK or end SESSION is cleared. 1) Session-level temporary table example 1 create SQL code create global temporary table temp_tbl (col_a varchar2 (30 )) on commit preserve rows 2 insert data SQL code insert into temp_tbl values ('test session table') 3 submit SQL code commit; 4. query the SQL code select * from temp_tbl. You can see that the 'test session table' data record is still in progress. End the SESSION, log on again, and query the data select * from temp_tbl. At this time, the record does not exist because the system automatically clears the record when the SESSION ends. 2) Example of transaction-level temporary table 1 create SQL code create global temporary table temp_tbl (col_a varchar2 (30 )) on commit delete rows 2 insert data SQL code insert into temp_tbl values ('test transaction table') 3 submit SQL code commit; 4. query the SQL code select * from temp_tbl. At this time, we can see that the inserted record 'test transaction table' does not exist, because the database has been cleared during submission. Similarly, if the SESSION is directly ended without being submitted, the logon record does not exist. 2. An example of using a temporary table in Oracle storage: When borrowing an archive, You need to split the workbook into detailed documents, the splitting is based on the BILLCODES (several comma-separated document numbers) fields in the table. The temporary table is used to save the split document information. Data in the temporary table is directly returned after the split. SQL code create or replace package AMS_PKG as type REFCURSORTYPE is REF CURSOR; procedure tables (P_CORP_NAME IN varchar2, P_YEAR IN varchar2, P_MONTH IN varchar2, tables IN varchar2, P_BILL_NUM IN varchar2, p_VOLUME_NUM IN varchar2, P_AREA_CODES IN varchar2, P_QUERY_ SQL out varchar2, P_OUTCURSOR out refCursorType); end AMS_PKG;/CREATE OR REPLACE PACKAGE BODY "AMS_PKG" as procedure SPLIT_VOL UMES (p_CORP_NAME IN varchar2, -- Query condition, company name p_YEAR IN varchar2, -- Query condition, accounting year p_MONTH IN varchar2, -- Query condition, period p_VOL_TYPE_CODE IN varchar2, -- Query condition, credential category encoding p_BILL_NUM IN varchar2, -- Query condition, information Ticket No. p_VOLUME_NUM IN varchar2, -- Query condition, Volume No. p_AREA_CODES IN varchar2, -- Query condition, the region code (the region where the volume is generated), separated by commas. -- The format is '12c01', '20170101', '12d01', '1201', and '20170101'. in the stored procedure, p_QUERY_ SQL out varchar2 is filtered using the in method, -- returns the query string p_OutCursor out refCursorType -- Return Value) is v_ SQL varchar2 (3000); v_ SQL _WHERE varchar2 (3000); v_temp1 varchar2 (300); v_temp2 varchar2 (300 ); v_tempBILLCODES varchar2 (3000); V_CNT NUMBER (); V_VOLUME_ID NUMBER (); mycur refCursorType; -- CURSOR mycur (v varchar2) is -- select vouchtype, BILLCODES FRO M PUB_VOLUMES where volumeid = v; CURSOR mycur_split (val varchar2, splitMark varchar2) is select * from table (myutil_split (val, splitMark); begin v_temp1: = ''; v_temp2: = ''; v_ SQL _WHERE: =''; v_tempBILLCODES: = ''; V_CNT: = 0; V_VOLUME_ID: = 0; -- the System ID of the volume table v_ SQL: = 'select VOLUMEID, VOUCHTYPE, billcodes from PUB_VOLUMES WHERE 1 = 1'; -- dbms_output.put_line ('P _ BILL_NUM = '| p_BILL_NUM); IF (p_CORP_NAME IS N Ot null and length (p_CORP_NAME)> 0) THEN -- company name BEGIN v_ SQL _WHERE: = v_ SQL _WHERE | 'and corpname like ''%'; v_ SQL _WHERE: = v_ SQL _WHERE | p_CORP_NAME; v_ SQL _WHERE: = v_ SQL _WHERE | '% '''; -- dbms_output.put_line (p_BILL_NUM); END IF; IF (p_YEAR IS NOT NULL AND LENGTH (p_YEAR)> 0) THEN -- accounting year begin v_ SQL _WHERE: = v_ SQL _WHERE | 'and year = '''; v_ SQL _WHERE: = v_ SQL _WHERE | p_YEAR; v_ SQL _WHERE: = V_ SQL _WHERE | '''; -- dbms_output.put_line (p_BILL_NUM); END IF; IF (p_MONTH IS NOT NULL AND LENGTH (p_MONTH)> 0) THEN -- BEGIN v_ SQL _WHERE: = v_ SQL _WHERE | 'and month = '''; v_ SQL _WHERE: = v_ SQL _WHERE | p_MONTH; v_ SQL _WHERE: = v_ SQL _WHERE | ''''; -- dbms_output.put_line (p_BILL_NUM); end if; IF (p_VOL_TYPE_CODE is not null and length (p_VOL_TYPE_CODE)> 0) THEN -- certificate category encoding BEGIN v_ SQL _ WHERE: = v_ SQL _WHERE | 'and vouchtype = '''; v_ SQL _WHERE: = v_ SQL _WHERE | p_VOL_TYPE_CODE; v_ SQL _WHERE: = v_ SQL _WHERE | '''; -- consume (p_BILL_NUM ); END; end if; IF (p_BILL_NUM is not null and length (p_BILL_NUM)> 0) THEN -- Information Order Number BEGIN v_ SQL _WHERE: = v_ SQL _WHERE | 'AND BILLCODES LIKE ''% '; v_ SQL _WHERE: = v_ SQL _WHERE | p_BILL_NUM; v_ SQL _WHERE: = v_ SQL _WHERE | '% '''; -- dbms_outpu T. put_line (p_BILL_NUM); end if; IF (p_VOLUME_NUM is not null and length (p_VOLUME_NUM)> 0) THEN -- Book Number BEGIN v_ SQL _WHERE: = v_ SQL _WHERE | 'and volumenum = '''; v_ SQL _WHERE: = v_ SQL _WHERE | p_VOLUME_NUM; v_ SQL _WHERE: = v_ SQL _WHERE | '''; -- begin (p_BILL_NUM); END; end if; p_QUERY_ SQL: = 'sql4where: '| v_ SQL _WHERE; -- dbms_output.put_line (v_ SQL | v_ SQL _WHERE | p_BILL_NUM );-- OPEN mycur (v_WHERE); OPEN mycur FOR v_ SQL | v_ SQL _WHERE; LOOP -- LOOP book record fetch mycur INTO V_VOLUME_ID, v_temp1, v_tempBILLCODES; EXIT WHEN mycur % NOTFOUND; V_CNT: = V_CNT + 1; -- DBMS_OUTPUT.PUT_LINE (V_CNT | ': BILLCODES =' | v_tempBILLCODES); OPEN mycur_split (v_tempBILLCODES ,','); LOOP -- generate the fetch mycur_split INTO v_temp2; exit when mycur_split % NOTFOUND; -- DBMS_OUTPUT.PUT_LINE (''| v_te Mp2); -- DBMS_OUTPUT.PUT_LINE ('p_bill_num = '| p_BILL_NUM |', v_temp2 = '| v_temp2); IF (p_BILL_NUM is null or p_BILL_NUM = TO_NUMBER (v_temp2 )) THEN v_temp1: = 'insert INTO TEMP_VOLUMES_QUERY (SELECT ''' | v_temp2 | ''',. * FROM PUB_VOLUMES a where volumeid = '| V_VOLUME_ID |') '; -- write to temporary table -- dbms_output.put_line ('v _ temp1 =' | v_temp1); execute immediate v_temp1; end if; end loop; CLOSE mycur_spl It; end loop; CLOSE mycur; -- start to output the result v_ SQL: = 'select CE. DCODE, CE. VOLUMEID, CE. CORPCODE, CE. CORPNAME, QU. AREANAME, CE. YEAR, CE. MONTH, CE. BILLCODES, CE. VOUCHTYPE, SHI. ROOMNAME, '; v_ SQL: = v_ SQL | 'ce. VOLUMENUM, GUI. CABINETNUM, CE. cabinetlayer from TEMP_VOLUMES_QUERY CE '; v_ SQL: = v_ SQL | 'left OUTER JOIN PUB_CORPS NAME ON CE. CORPCODE = NAME. CORPCODE '; -- Company of the book (the company that generates the document) v_ SQL: = v_ SQL | 'left OUTER JOIN PU B _AREAS QU ON NAME. AREACODE = QU. AREACODE '; -- region of the volume (region of the company where the document is generated) v_ SQL: = v_ SQL | 'left OUTER JOIN PUB_CABINETS GUI ON CE. CABINETCODE = GUI. cabinetcode'; -- file cabinet in which the volume is stored v_ SQL: = v_ SQL | 'left OUTER JOIN PUB_ARCHIVESROOMS SHI ON GUI. ROOMCODE = SHI. ROOMID '; -- the archive room WHERE the volume (cabinet) is located (storage location) v_ SQL: = v_ SQL | 'where (GUI. ISMAIL = 0 or gui. ISSIGN = 1) '; -- v_ SQL: = v_ SQL |' AND CE. ISBORROW = ''0'''; -- not borrowed IF (p_AREA_CODES is not null and length (p_AREA_CODES)> 0) THEN -- IF you need to limit the region of the book to BEGIN in v_ SQL: = v_ SQL | 'AND QU. areacode in ('| p_AREA_CODES |') '; end if; p_QUERY_ SQL: = p_QUERY_ SQL | 'sql4result:' | v_ SQL; -- Return OPEN p_OutCursor FOR v_ SQL; select count (1) INTO V_CNT FROM TEMP_VOLUMES_QUERY; dbms_output.put_line (v_ SQL | ', V_CNT =' | V_CNT); dbms_output.put_line (V_CNT); delete from TE MP_VOLUMES_QUERY; COMMIT; end SPLIT_VOLUMES; end;/3. Conclusion 1. on commit delete rows indicates that the temporary table is specified by a transaction. After each COMMIT, ORACLE truncates the table (DELETE all ROWS) 2. on commit preserve rows indicates that the temporary table is specified by the session. When the session is interrupted, ORACLE truncates the table. 3. Data in temporary tables (both session-level and transaction-level) is session isolated, and data is not shared between different sessions. 4. When using a transaction-level temporary table in storage, note that the data of this transaction is deleted before commit. Otherwise, data may increase continuously (the reason is not clear yet ). 5. syntax of the two temporary tables: create global temporary table name on commit preserve | delete rows; When preserve is used, it is a SESSION-level temporary table, delete is a temporary table at the TRANSACTION level. 6. Features and performance (compared with common tables and views) temporary tables are only valid in the current connection; temporary tables are not indexed, therefore, if the data volume is large or is queried multiple times, it is not recommended to use it. When the data processing is complex, the table is faster, and the view is faster. When querying data only, we recommend that you use a cursor: open cursor for 'SQL Claus ';