Create an Oracle temporary table

Create a temporary table:

Create an Oracle temporary table. There are two types of temporary tables:

1. Session-level temporary table

2. Transaction-level temporary tables

1) 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. Session-level temporary table creation method:

Create Global Temporary Table Table_Name

(Col1 Type1, Col2 Type2. ..) On Commit Preserve Rows;

Example:

Create global temporary table Student

(Stu_id Number (5 ),

Class_id Number (5 ),

Stu_Name Varchar2 (8 ),

Stu_Memo varchar2 (200) on Commit Preserve Rows;

2) 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 ). How to create a temporary transaction table:

Create Global Temporary Table Table_Name

(Col1 Type1, Col2 Type2. ..) On Commit Delete Rows;

Example:

Create global temporary table Classes

(Class_id Number (5 ),

Class_Name Varchar2 (8 ),

Class_Memo varchar2 (200) on Commit delete Rows;

3) differences between the two temporary tables

Session-level temporary tables use on commit preserve rows, while transaction-level temporary tables use on commit delete rows. In usage, session-level data is truncated only when the session ends temporary tables, in addition, the data in the temporary transaction table is truncated, whether it is commit, rollback, or session termination.

4) when to use a temporary table

1) When an SQL statement is associated with two or more tables, it is associated with some small tables. You can split a large table and store a small result set in a temporary table.

2) some temporary data may need to be stored during program execution, which is required throughout the program session.

I believe that you are familiar with creating temporary Oracle tables through the introduction above. As long as you practice frequently, you will be able to master how to create temporary Oracle tables.