Oracle temporary table creation syntax and usage skills

OracleTemporaryTables (Oracle temporary table) 1. Create A temporary table syntax. A. ONCOMMITDELETEROWS defines the CREATEGLOBALTEMPORAR method for creating A transaction-level temporary table.

Oracle Temporary Tables (Oracle Temporary table) 1. create a Temporary table syntax A. on commit delete rows defines the method for creating A transaction-level Temporary table CREATE GLOBAL TEMPORAR

Oracle Temporary Tables (Temporary Oracle table)

1. Create a temporary table syntax

A. on commit delete rows defines how to create A transaction-level temporary table

Create global temporary table TABLE_NAME

----- (COUMNS ...)

----- As select... From table...

On commit delete rows;

When the current session issues the commit/rollback command, all data in the transaction cycle is automatically deleted by Oracle (Oracle truncate table ). But it does not affect the data of any other sessions.

B. ON COMMIT PRESERVE ROWS defines how to create a session-level temporary table

Create global temporary table TABLE_NAME

----- (COUMNS ...)

----- As select... From table...

On commit preserve rows;

The current session ends (the user Exits normally/the user does not exit normally/the Oracle instance crashes). Oracle deletes the data in this session (Oracle truncate table ). But it does not affect the data of any other sessions.

2. Features

A. After temporary table data is cleared automatically, the structure and metadata of the temporary table are stored in the user's data dictionary. Table definitions are visible to all sessions

B. DML locks are not required for temporary tables.

C. You can index temporary tables and create views based on temporary tables.

D. The index on the temporary table is also temporary and only valid for the current session or transaction.

E. Temporary tables can have triggers

F. You can use the export and import tools to import and export definitions of temporary tables, but cannot export data.

3. Tips

A. 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.

B. Some temporary data may need to be stored during program execution. It is very convenient to put such data in the temporary table.

C. Use temporary tables in the stored procedure:

1> before creating a temporary table, add the table name judgment.

Select count (*) into v_count from user_tables where table_name = 'xxx ';

If v_count = 0 then

Create global temporary table...

At the end of the stored procedure, remember to delete the table.

Execute immediate 'drop table t_temp ';