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

问题描述：

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

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

类似

Declare table @table (int id)

在SQL Server中

In SQL server

然后使用选择语句填充它

And then populate it with a select statement

有可能吗?

谢谢

答

是的，Oracle有临时表.这是 AskTom 描述它们的文章，并且此处是正式的oracle CREATE表文档.

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中，只有临时表中的 data 是临时的.该表是其他会话可见的常规对象.在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.

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

Oracle 18c添加了私有临时表，它们是单会话内存中对象.请参见文档以获取更多详细信息.私有临时表可以动态创建和删除.

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.

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

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

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数据库中创建临时表?

相关推荐