What is the temporary table in oracle stored procedure

In Oracle, the temporary table of a stored procedure is a table used to temporarily store data; when the transaction or session ends, the data in the temporary table will be automatically cleared by the database, and the temporary table can be used according to the life of the database. The different cycles are divided into transaction-level temporary tables and session-level temporary tables. The creation syntax is "create global temporary table temporary table name on commit preserve/delete rows".

The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.

Temporary table in oracle stored procedure

Temporary table syntax:

create global temporary table 临时表名 on commit preserve/delete rows  
--preserve:SESSION级的临时表，delete:TRANSACTION级的临时表.

1. Function: used to 'temporarily' store data

(1) When a transaction or session ends, the 'data' in this temporary table will be automatically cleared by the database

(2) But the 'table structure' will still remain

2. Category: 'Differences in life cycle'

(1) Transaction-level temporary table: on commit delete rows; 'Delete' records when commit (default)

(2) Session-level temporary table: on commit preserve rows; 'save' records when committing, 'delete' records when ending the session

3. Note

(1 ) The efficiency of the temporary table processing is higher than the ordinary table

# & lt; 1 & gt;

(2) The operation method is the same as that of ordinary tables

4. In the following test, after creating the temporary table, the information query

SESSION-level temporary The table data exists throughout the SESSION until the end of the SESSION; and the TRANSACTION-level temporary table data disappears after the TRANACTION ends, that is, COMMIT/ROLLBACK or the end of the SESSION will clear the TRANACTION temporary table data.

When the session exits or the user submits a commit and rollback transaction, the data in the temporary table is automatically cleared, but the structure and metadata of the temporary table are still stored in the user's data dictionary. When a session ends (the user exits normally, the user exits abnormally, or the ORACLE instance crashes) or a transaction ends, Oracle executes the TRUNCATE statement on the table of this session to clear the temporary table data, but will not clear the data in the temporary tables of other sessions. . Temporary tables can use triggers.

Recommended tutorial: "

Oracle Video Tutorial

"

The above is the detailed content of What is the temporary table in oracle stored procedure. For more information, please follow other related articles on the PHP Chinese website!