Tips for using temporary tables in Oracle stored procedures

I.Oracle temp Table knowledge

In Oracle, temporary tables are divided into two sessions (session level), TRANSACTION (transaction level), and session-level temporal table data exists throughout the session until the session is closed; Temporary table data at the transaction level disappears after Tranaction ends, that is, the Commit/rollback or end session clears the tranaction temporal table data.

1) session-level interim representation example

1 Create

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

On commit Preserve rows

2 Inserting data

INSERT into TEMP_TBL values (' Test session table ')

3 Submit

Commit

4 queries

Select *from temp_tbl

You can see that the data ' test session table ' record is still in.

End session, re-login, and then query the data Select *from Temp_tbl, this time the record does not exist, because the system automatically clears the record when the session ends.

2) Example of a transaction-level interim representation

1 Create

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

On commit Delete rows

2 Inserting data

INSERT into TEMP_TBL values (' Test Transaction table ')

3 Submit

commit;

4 queries

Select *from temp_tbl

At this point you can see that the record you just inserted ' Test transaction table ' no longer exists, because the database is clear at the time of submission, again, if you do not commit the session, the re-login record does not exist.

Tips for using temporary tables in Oracle stored procedures