Tips for using temporary tables in Oracle stored procedures

In Oracle, temporary tables are divided into two types: SESSION and TRANSACTION. SESSION-level temporary table data exists throughout the SESSION until the SESSION is completed.

In Oracle, temporary tables are divided into two types: SESSION and TRANSACTION. SESSION-level temporary table data exists throughout the SESSION until the SESSION is completed.

I. Oracle temporary table knowledge

In Oracle, temporary tables are divided into two types: SESSION and TRANSACTION. SESSION-level temporary table data exists throughout the SESSION until the SESSION ends; the temporary table data at the TRANSACTION level disappears after the TRANACTION is completed, that is, the temporary table data of the TRANACTION is cleared by COMMIT/ROLLBACK or the end SESSION.

1) Session-level temporary table example

1. Create

Create global temporary table temp_tbl (col_a varchar2 (30 ))

On commit preserve rows

2. Insert data

Insert into temp_tbl values ('test session table ')

3. Submit

Commit;

4. Query

Select * from temp_tbl

The data 'test session table' is still logged.

End the SESSION, log on again, and query the data select * from temp_tbl. At this time, the record does not exist because the system automatically clears the record when the SESSION ends.

2) Example of a transaction-level temporary table

1. Create

Create global temporary table temp_tbl (col_a varchar2 (30 ))

On commit delete rows

2. Insert data

Insert into temp_tbl values ('test transaction table ')

3. Submit

Commit;

4. Query

Select * from temp_tbl

At this time, we can see that the inserted record 'test transaction table' does not exist, because the database has been cleared during the submission. Similarly, if the SESSION is ended without submission, the logon record does not exist.

,