Oracle 临时表详解及实例

什么是临时表

Oracle 临时表（Temporary Table），即只在当前会话中存在并可见，当会话结束时临时表数据将被自动清空。临时表可用于存储临时数据或中间结果，比如存储在子查询中生成的中间结果等。Oracle 临时表的表结构（表名、列名、数据类型、约束等）与普通表几乎一致，临时表支持的数据类型和约束也和普通表完全一致。

创建临时表

在 Oracle 数据库中，可以使用 CREATE GLOBAL TEMPORARY 或 CREATE PRIVATE TEMPORARY 语句来创建临时表，二者的区别在于临时表的可见性不同：

• CREATE GLOBAL TEMPORARY 创建的是全局临时表，即多个会话都可以访问该临时表。

• CREATE PRIVATE TEMPORARY 创建的是私有临时表，即只有创建该临时表的会话才可以访问该临时表。

在创建临时表时，可以指定 ON COMMIT DELETE ROWS 或 ON COMMIT PRESERVE ROWS 选项来确定临时表数据是否在事务提交时被清除。

下面是创建临时表的语法：

CREATE [GLOBAL | PRIVATE] TEMPORARY TABLE table_name (
    column1 datatype [NULL | NOT NULL],
    column2 datatype [NULL | NOT NULL],
    ...
) [ON COMMIT { DELETE ROWS | PRESERVE ROWS }]

访问临时表

访问临时表的语法与普通表类似，可以使用 SELECT、INSERT、UPDATE、DELETE 等语句对临时表进行操作。

下面是一个简单的 SELECT 示例：

SELECT * FROM global_temp_table;

临时表示例

示例一

下面是一个创建全局临时表的示例：

CREATE GLOBAL TEMPORARY TABLE global_temp_table (
    id NUMBER(10, 0),
    name VARCHAR2(50)
) ON COMMIT DELETE ROWS;

在临时表中插入数据：

INSERT INTO global_temp_table VALUES (1, 'Alice');
INSERT INTO global_temp_table VALUES (2, 'Bob');

查询临时表：

SELECT * FROM global_temp_table;

输出结果：

ID   NAME   
---- -------
1    Alice  
2    Bob    

另外一个会话中也可以访问刚刚创建的全局临时表：

-- 另外一个会话
SELECT * FROM global_temp_table;

输出结果：

ID   NAME   
---- -------
1    Alice  
2    Bob    

示例二

下面是一个创建私有临时表的示例：

CREATE PRIVATE TEMPORARY TABLE private_temp_table (
    id NUMBER(10, 0),
    name VARCHAR2(50)
) ON COMMIT DELETE ROWS;

在临时表中插入数据：

INSERT INTO private_temp_table VALUES (1, 'Alice');
INSERT INTO private_temp_table VALUES (2, 'Bob');

查询临时表：

SELECT * FROM private_temp_table;

输出结果：

ID   NAME   
---- -------
1    Alice  
2    Bob    

在另外一个会话中查询同样的私有临时表：

-- 另外一个会话
SELECT * FROM private_temp_table;

输出结果：

ORA-00942: table or view does not exist

这是因为私有临时表只能被创建它的会话访问，其他会话无法访问该临时表。

总结

Oracle 临时表是一个临时存储中间结果或临时数据的好工具，可以有效提高查询效率和数据处理速度。但是，使用临时表需要注意以下几点：

• 临时表占用数据库的存储空间，因此应该尽量控制临时表数据的大小。

• 临时表只存在于当前会话中，其他会话无法访问。

• 临时表的数据在会话结束时会被自动清除，因此需要在查询结束前将临时表中的数据导出到其他表中。

希望这篇文章可以为读者在实际工作中使用 Oracle 临时表提供一些帮助。