Notes and demos for creating temporary tables during storage

Precautions for creating a temporary table in the stored procedure and the demo cannot be used directly in the stored procedure of creating a temporary table in the ORACLE stored procedure, therefore, you can only use dynamic SQL statements to execute -- ON COMMIT DELETE ROWS. This indicates that the temporary table is specified as a transaction. After each COMMIT, ORACLE truncates the table (DELETE all ROWS) -- on commit preserve rows indicates that the temporary table is specified by the session. When the session is interrupted, ORACLE truncates the table. Create or replace procedure temptest (p_searchDate in date) IS v_count INT; str varchar2 (300); BEGIN v_count: = 0; str: = 'drop table sett_dailytest'; execute immediate str; str: = 'Create global temporary table SETT_DAILYTEST (naccountid number not null, nsubaccountid number not null) on commit preserve rows '; execute immediate str; ---- execute str using dynamic SQL statements: = 'insert into SETT_DAILYTEST (select naccounti D, nsubaccountid from sett_dailyaccountbalance) '; execute immediate str; END temptest; the Stored Procedure www.2cto.com for creating a temporary table above executes some operations to write data to the temporary table. Create or replace procedure PR_DAILYCHECK (p_Date in date, p_Office in integer, p_Currency in integer, P_Check in integer, p_countNum out integer) IS v_count INT; BEGIN v_count: = 0; IF p_Date is null then hour ('date cannot be blank '); ELSE www.2cto.com IF P_Check = 1 THEN insert into SETT_DAILYTEST (select naccountid, nsubaccountid from nation where dtdate = p_Date); select co Unt (sd. naccountid) into v_count from sett_subaccount ss, sett_account sa, sett_dailytest sd where sd. naccountid = sa. id and sd. nsubaccountid = ss. id and sa. id = ss. naccountid AND sa. nofficeid = p_Office AND sa. ncurrencyid = p_Currency and rownum <2; COMMIT; p_countNum: = v_count; distinct (p_countNum); end if; IF P_Check = 2 THEN insert into SETT_DAILYTEST (select naccountid, nsubaccounti D from sett_dailyaccountbalance where dtdate = p_Date); select count (sd. naccountid) into v_count from sett_cfsubaccount ss, sett_account sa, sett_dailytest sd where sd. naccountid = sa. id and sd. nsubaccountid = ss. id and sa. id = ss. naccountid AND sa. nofficeid = p_Office AND sa. ncurrencyid = p_Currency and rownum <2; COMMIT; p_countNum: = v_count; dbms_output.put_line (p_countNum); end if; END PR_DAILYCHECK; www.2cto.com need to create a temporary table. Please give an example. Thank you! Values are temporary create global temporary table flight_schedule (startdate DATE, enddate DATE, cost NUMBER) values create proecdure name_pro as str varchar2 (100); begin str: = 'Create global temporary table tablename on commit preserve rows as select * from others_table '; execute immedi Ate str; end;/You can specify a temporary table as transaction-related (default) or session-related: on commit delete rows: specify that the temporary table is transaction-related, oracle truncates the table after each submission. On commit preserve rows: specifies that the temporary table is session-related. Oracle truncates the table after the session is terminated. ====================== You can create the following two temporary tables: 1. Session-specific TEMPORARY table create global temporary <TABLE_NAME> (<column specification>) on commit preserve rows; ======== www.2cto.com: the summary of global temporary tables is faster than that of general tables. Because: 1 when creating a temporary table, you do not need to insert entries into the catalog table. You do not need to access the catalog table to use the temporary table. Therefore, there is no competition for the catalog table. 2. Only the app that creates a temporary table can access the temporary table, so there is no lock when processing the temporary table. 3. If the not logged option is specified, logs are NOT recorded when processing temporary tables. Therefore, if a large amount of temporary data is used in only one session of the database, saving the data into the temporary table can greatly improve the performance. Declare global temporary table tt (C1 INT, C2 CHAR (20); after the connect reset command, the temporary table no longer exists. Temporary tables are dynamically compiled. Therefore, the use of temporary tables must also be placed after declare curser create procedure INSTT2 (P1 INT, P2 CHAR (20 )) begin declare global temporary table tt (C1 INT, C2 CHAR (20) % insert into session. tt values (P1, P2); begin declare C1 cursor with return for select * from session. TT; END % 2. The TEMPORARY table create global temporary <TABLE_NAME> (<column specification>) on commit delete rows exclusive to the transaction. In Oracle, the global temporary table is not deleted. In fact, you only need to CREATE it once, you can directly apply it in the future, which is different from MS and Sybase. In fact, when the database is disconnected, the data in the temporary table is automatically cleared. Different sessions are isolated from each other. Do not be careful about mutual impact. However, if a connection is used for sharing, you must use On Commit delete rows to make the data valid only within the transaction. The definition of creating a temporary table on www.2cto.com 3 is visible to all sessions, but the data in the table is only valid for the current SESSION or transaction. creation Method: 1) on commit delete rows defines the method for creating a transaction-level temporary table. create global temporary table admin_work_area (startdate DATE, enddate DATE, class CHAR (20) on commit delete rows; EXAMPLE: SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 2 (startdate DATE, 3 enddate DATE, 4 class CHAR (20) 5 on commit delete rows; SQL> create table permernate (a number); SQL> insert into admin_work_area values (sysdate, sysdate, 'temperary table'); SQL> insert into permernate values (1); SQL> commit; SQL> select * from admin_work_area; SQL> select * from permernate; A 1 2) on commit preserve rows defines how to create a session-level temporary table. create global temporary table admin_work_area (startdate DATE, enddate DATE, class CHAR (20) on commit preserve rows; EXAMPLE: www.2cto.com Session 1: SQL> drop table admin_work_area; SQL> create global temporary table admin_work_area 2 (startdate DATE, 3 enddate DATE, 4 class CHAR (20) 5 on commit preserve rows; SQL> insert into permernate values (2 ); SQL> insert into admin_work_area values (sysdate, sysdate, 'session temperary '); SQL> commit; SQL> select * from permernate; A ---------- 1 2 SQL> select * from admin_work_area; startdate enddate class ---------- ------------------ 17-1 & Ocirc; & Acirc;-03 17-1 & Ocirc; & Acirc;-03 session temperary www.2cto.com session 2: SQL> select * from permernate; A ---------- 1 2 SQL> select * from admin_work_area; no row is selected. session 2 cannot see the data in the temporary table in session 1. author: Zhao Mo Yan