如何在 Oracle 数据库中创建临时表?
我想在 Oracle 数据库中创建一个临时表,
例如
Declare table @table (int id)
在 SQL Server 中
,然后用 select 语句填充它,
这可能吗?
谢谢
I would like to create a temporary table in a Oracle database
something like
Declare table @table (int id)
In SQL server
And then populate it with a select statement
Is it possible?
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
是的,Oracle 有临时表。以下是 AskTom< 的链接/a> 描述它们的文章,这里是官方的 Oracle CREATE表文档。
然而,在Oracle中,只有临时表中的数据是临时的。该表是其他会话可见的常规对象。在 Oracle 中频繁创建和删除临时表是一种不好的做法。
Oracle 18c 添加了私有临时表,它们是单会话内存对象。请参阅 文档了解更多详细信息。私有临时表可以动态创建和删除。
临时表可能很有用,但它们在 Oracle 中经常被滥用。通常可以通过使用内联视图将多个步骤组合到单个 SQL 语句中来避免它们。
Yep, Oracle has temporary tables. Here is a link to an AskTom article describing them and here is the official oracle CREATE TABLE documentation.
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 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.
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 Server 不同。您只需创建一次,而不是每次会话。您插入其中的行仅对您的会话可见,并且当您结束会话(或事务结束)时会自动删除(即
TRUNCATE
,而不是DROP
) ,取决于您使用的“ON COMMIT”子句)。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
, notDROP
) when you end you session ( or end of the transaction, depending on which "ON COMMIT" clause you use).如果您尝试创建类似于 SQL Server 的内容(其中 SELECT INTO #TEMP_TABLE_NAME),则无法在 Oracle 中使用 CREATE TABLE 实现这一点,除非您具有安全性,如果您是常规报告编写者,则可能不太可能这样做。
在 Oracle 中,如果您尝试将 SELECT 块的输出存储在内存中以提高查询性能,则需要在 CTE 的主 SELECT 中使用 Oracle 提示 /+ MATERIALIZE/。这会将 CTE 的结果存储在内存中,并且可以在查询的其他部分中访问,而无需冒每次引用 CTE 时重新执行 CTE 的风险。
示例:
如果您之后检查执行计划,您将看到该对象将被读入“光标持续时间内存”。
If you are trying to create something similar to SQL Server where you SELECT INTO #TEMP_TABLE_NAME this cannot be achieved in Oracle using CREATE TABLE unless you have security to do so which is probably unlikely if you are a regular report writer.
In Oracle, if you are trying to store the output of a SELECT block in memory to increase query performance, you'll need to use the Oracle hint /+ MATERIALIZE/ in the main SELECT of a CTE. This will store the results of the CTE in memory and can be accessed in other parts of your query without running the risk of re-executing the CTE each time it is referenced.
Example:
If you check the execution plan after, you'll see that the object will be read in as "Cursor Duration Memory".