PLSQL_Oracle Temporary Table basic concepts and usage (concepts), plsql create Temporary Table

PLSQL_Oracle Temporary Table basic concepts and usage (concepts), plsql create Temporary Table

2014-06-08 BaoXinjian

I. Basic Concepts

1. My Understanding of temporary tables:

Create a table in Oracle. This table is not used for other functions. It is mainly used for some special functions of your software system. When you use it, the data in the table is useless. After creating a temporary Oracle table, it basically does not occupy the tablespace. If you do not specify that the temporary table (including the index of the temporary table) is empty, the data you insert into the temporary table is stored in the temporary tablespace (TEMP) of the ORACLE system ).

2. Basic Concepts

It is used to save the intermediate results of a transaction or session. It can only be visible to the session of the current year, and cannot be read from any other session. Therefore, the session concurrency is high.

3. When to use a temporary table

• When an SQL statement is associated with two or more tables and is associated with some small tables. You can split a large table and store a small result set in a temporary table.
• Some temporary data may need to be stored during program execution, which is required during the entire program session.

4. Limitations of temporary tables

• The lob object is not supported. This may be the consideration of the designer based on the running efficiency, but temporary tables cannot be used when this function is required in actual applications.
• The primary/foreign key relationship is not supported.

 

Ii. Type

1. Session-based

(1). Concepts

SESSION-level temporary table because the data in this temporary table is related to your current SESSION. When your current SESSION does not exit, the data in the temporary table will still exist, when you exit the current SESSION, all the data in the temporary table is lost, of course, if you log on to another SESSION at this time, you will not be able to see the data inserted into the temporary table in another SESSION. That is, the data inserted by two different sessions is irrelevant. When a SESSION exits, the data in the temporary table is truncated (truncate table, that is, data is cleared.

(2). Syntax

Create global temporary table xxgl. xxgl_test_journal (

Je_id NUMBER,

Je_name VARCHAR (100 BYTE)

) On commit persever rows

 

2. Based on things

(1). Concepts

A transaction-level temporary table is a transaction-related temporary table. When a transaction is committed or rolled back, the data in the temporary table will be truncated by itself, the other content is consistent with the SESSION-level temporary table (including when the SESSION is exited, the transaction-level temporary table will be automatically truncated ).

(2). Syntax

Create global temporary table xxgl. xxgl_test_journal (

Je_id NUMBER,

Je_name VARCHAR (100 BYTE)

) On commit delete rows

 

Iii. Differences between things and sessions

1. A session refers to a session, a connection

A transaction refers to an operation unit, which either succeeds or fails without intermediate state.

2. session --> v $ seesion

Transaction --> v $ transaction

3. One session can start multiple transactions

4. Differences between sessions and temporary transaction tables

Session-level temporary tables use on commit preserve rows;

The transaction level adopts on commit delete rows;

In terms of usage, data in the session level temporary table is truncated only when the session ends, and the data in the temporary table is truncated whether it is commit, rollback, or session ends;

 

4. A simple example

Create global temporary table xxgl. xxgl_test_journal (

Je_id NUMBER,

Je_name VARCHAR (100 BYTE)

) On commit delete rows;

 

Thanks and Regards

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.

Oracle for create temporary table as select ......

The global temporary table of oracle has a scope problem. You need to do so.
Create global temporary table temp1 (goodsid number) on commit preserve rows;
Insert into temp1 select...
Commit;
In this way, the data can be retained, but if the session is closed, the data will also be lost.