Detailed example of Oracle temporary table

This article brings you relevant knowledge about Oracle, which mainly introduces related issues about temporary tables, including transaction-level temporary tables, session-level temporary tables, etc., as follows Let's take a look, I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

1. Oracle Temporary Table

Temporary table is a special kind of table. When we need to repeatedly operate a batch of data in a certain (or multiple) table, by creating a temporary table for the batch of data, the operation may be simplified and efficiency may be improved!

Basic syntax:

create global temporary table 临时表名(colum,colum,.......)on commit delete rows;on commit preserve rows;

Description:

• on commit delete rows; Description The data rows are only visible in the current transaction, which is also the default value. The data is after the transaction is submitted. The rows disappear; a transaction-level temporary table is created.
• on commit preserve rows; Description The data rows are only visible in the current session; a session-level temporary table is created.

2. Example demonstration

2.1. Transaction-level temporary table

-- 创建事务级临时表 tmp_user 注：on commit delete rows 可省略create global temporary table tmp_user (
  user_id   varchar2(10),
  user_name varchar2(20)) on commit delete rows;

2.2. Session-level temporary table

-- 创建会话级临时表 tmp_user1create global temporary table tmp_user1 (
  user_id   varchar2(10),
  user_name varchar2(20))on commit preserve rows;

##Three , Temporary table description

The data in the temporary table is based on a session or a transaction, and cannot be accessed by other sessions.
• Temporary tables can be dedicated to transaction processing or to sessions. For transaction-specific temporary tables, the data exists during the transaction; for session-specific temporary tables, the data exists during the session. In both cases, the data inserted by the session is specific to the session. Each session can only view and modify its own data. Therefore, the data in the temporary table never obtains the DML lock

Oracle Video Tutorial"

The above is the detailed content of Detailed example of Oracle temporary table. For more information, please follow other related articles on the PHP Chinese website!