如何在 Oracle 数据库中创建临时表？

小开

最佳答案

Yep, Oracle has temporary tables. Here is a link to an 问 Tom article describing them and here is the official oracle CREATE TABLE documentation.

但是，在 Oracle 中，只有临时表中的资料是临时的。该表是其他会话可见的常规对象。在 Oracle 中频繁地创建和删除临时表是一种不好的做法。

CREATE GLOBAL TEMPORARY TABLE today_sales(order_id NUMBER)
ON COMMIT PRESERVE ROWS;

Oracle 18c 添加了私有临时表，这些表是单会话内存对象。有关详细信息，请参阅 the documentation。可以动态创建和删除私有临时表。

CREATE PRIVATE TEMPORARY TABLE ora$ptt_today_sales AS
SELECT * FROM orders WHERE order_date = SYSDATE;

临时表可能很有用，但是在 Oracle 中经常被滥用。通常可以通过使用内联视图将多个步骤组合成单个 SQL 语句来避免这些错误。

小开

Just a tip.. Temporary tables in Oracle are different to SQL Server. You create it ONCE and only ONCE, not every session. The rows you insert into it are visible only to your session, and are automatically deleted (i.e., TRUNCATE, not DROP) when you end you session ( or end of the transaction, depending on which "ON COMMIT" clause you use).

小开

CREATE GLOBAL TEMPORARY TABLE Table_name
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;

小开

CREATE TABLE table_temp_list_objects AS
SELECT o.owner, o.object_name FROM sys.all_objects o WHERE o.object_type ='TABLE';