Create a temporary table in oracle and create a temporary table in oracle

Create a temporary table in oracle and create a temporary table in oracle
The transaction is automatically cleared after the transaction is committed, and data is isolated between different transactions.Create global temporary table GLS_REPORT_EXPR(EXPRNO VARCHAR2 (30)Not Null, EXPR VARCHAR2 (1024 ))On commit delete rows
Creating a temporary table in oracle is different from creating a common table.

Summary of global temporary tables

Operations on temporary tables are faster than those on normal 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 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;
END %

2. Temporary tables specific to transactions
Create global temporary <TABLE_NAME> (<column specification>)
On commit delete rows;

In Oracle, the global temporary table is not deleted. In fact, you only need to create it once and then apply it directly. This 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.

How to create a temporary table in oracle

You # AA is the name of the temporary table. You cannot use the # symbol for the table name!

Alter GLOBAL TEMPORARY TABLE AAA
ON COMMIT DELETE ROWS
AS
SELECT * from aa;