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

I would like to create a temporary table in a Oracle database

我想在Oracle数据库中创建一个临时表。

something like

类似的

Declare table @table (int id)

In SQL server

在SQL server

And than populate it with a select statement

然后用select语句填充它

Is it possible?

是可能的吗?

Thanks

谢谢

3 个解决方案

#1

106

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

是的，Oracle有临时表。这里是AskTom文章的链接，描述了它们，这里是oracle创建表的官方文档。

However, in Oracle, only the data in a temporary table is temporary. The table is a regular object visible to other sessions. It is a bad practice to frequently create and drop temporary tables in Oracle.

但是，在Oracle中，只有临时表中的数据是临时的。该表是其他会话可见的常规对象。在Oracle中频繁创建和删除临时表是一种糟糕的做法。

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

Oracle 18c added private temporary tables, which are single-session in-memory objects. See the documentation for more details. Private temporary tables can be dynamically created and dropped.

Oracle 18c增加了私有临时表，它们是内存中的单会话对象。更多细节请参阅文档。可以动态地创建和删除私有临时表。

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

Temporary tables can be useful but they are commonly abused in Oracle. They can often be avoided by combining multiple steps into a single SQL statement using inline views.

临时表是有用的，但是在Oracle中它们经常被滥用。通过使用内联视图将多个步骤组合到一个SQL语句中，通常可以避免这些问题。

#2

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).

只是一个提示。Oracle中的临时表与SQL Server不同。您只创建一次，而不是每个会话。插入到其中的行仅对会话可见，并被自动删除(例如。当您结束会话(或事务结束时，取决于您使用哪个“on COMMIT”子句)时，将截断而不是删除。

#3

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

#1

106