SQL 如何在 Oracle 数据库中创建临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2671518/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you create a temporary table in an Oracle database?
提问by GigaPr
I would like to create a temporary table in a Oracle database
我想在 Oracle 数据库中创建一个临时表
something like
就像是
Declare table @table (int id)
In SQL server
在 SQL 服务器中
And then populate it with a select statement
然后用 select 语句填充它
Is it possible?
是否可以？
Thanks
谢谢
回答by hamishmcn
Yep, Oracle has temporary tables. Here is a link to an AskTomarticle describing them and hereis the official oracle CREATE TABLE documentation.
是的，Oracle 有临时表。这是描述它们的AskTom文章的链接，这是官方 oracle CREATE TABLE 文档。
However, in Oracle, only the datain 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 documentationfor 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 语句中来避免它们。
回答by Matthew Watson
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”子句）时自动删除（即，TRUNCATE， not DROP）。
回答by harish from nit ameerpet
CREATE GLOBAL TEMPORARY TABLE Table_name
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;
回答by Edy Muniz
CREATE TABLE table_temp_list_objects AS
创建表 table_temp_list_objects AS
SELECT o.owner, o.object_name FROM sys.all_objects o WHERE o.object_type ='TABLE';
SELECT o.owner, o.object_name FROM sys.all_objects o WHERE o.object_type ='TABLE';
