Sybase 开发者问：如何在 Oracle 中创建临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/221822/
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
Sybase Developer Asks: How To Create a Temporary Table in Oracle?
提问by AJ.
I'm familiar with Sybase / SQL server, where I can create a temp. table like this:
我熟悉 Sybase/SQL 服务器，我可以在其中创建临时文件。像这样的表：
SELECT *
INTO #temp
FROM tab1 ,
tab2
WHERE tab1.key = tab2.fkey
SELECT *
FROM #temp
WHERE field1 = 'value'
#temp only exists for the duration of this session, and can only be seen by me.
#temp 只在本次会话期间存在，并且只能被我看到。
I would like to do a similar thing in Oracle, but I'm reading about "global temporary tables", which don't sound like the same thing.
我想在 Oracle 中做类似的事情，但我正在阅读“全局临时表”，这听起来不像是同一件事。
How can I do the same thing in Oracle as I'm doing in Sybase?
如何在 Oracle 中执行与在 Sybase 中相同的操作？
Thanks :)
谢谢 ：）
采纳答案by David Aldridge
Your first approach ought to be to do this as a single query:
您的第一种方法应该是将其作为单个查询执行：
SELECT *
FROM
(
SELECT *
FROM tab1 ,
tab2
WHERE tab1.key = tab2.fkey
)
WHERE field1 = 'value';
For very complex situations or where temp# is very large, try a subquery factoring clause, optionally with the materialize hint:
对于非常复杂的情况或 temp# 非常大的情况，请尝试使用子查询分解子句，可选择使用具体化提示：
with #temp as
(
SELECT /*+ materialize */
*
FROM tab1 ,
tab2
WHERE tab1.key = tab2.fkey
)
SELECT *
FROM temp#
WHERE field1 = 'value';
If that is not helpful, go to the Global Temporary Table method.
如果这没有帮助，请转到全局临时表方法。
回答by Colin Pickard
A global temporary table is not the same, the definition remains after the end of the session, also the table (but not the data) is visible to all sessions.
全局临时表不一样，定义在会话结束后仍然存在，并且表（但不是数据）对所有会话都是可见的。
If you are writing stored procedures, have you looked into cursors? It's a bit more complicated, but a very efficient and clean way to work with a temporary data set.
如果您正在编写存储过程，您是否研究过游标？它有点复杂，但使用临时数据集是一种非常有效和干净的方式。
回答by Noah Yetter
Oracle does not provide a direct analogue of this facility. A global temporary table is similar, but it must be created in advance and can be difficult to alter down the line due to locking issues.
Oracle 不提供此功能的直接模拟。全局临时表与此类似，但它必须提前创建，并且由于锁定问题可能难以更改。
Most needs of this nature can be met with cursors or one of the different pl/sql collection types (nested tables, varrays, associative arrays), but none of those can be used as if they were a table. That is, you cannot SELECT from them.
大多数这种性质的需求都可以通过游标或不同的 pl/sql 集合类型之一（嵌套表、变量数组、关联数组）来满足，但这些都不能像表一样使用。也就是说，您不能从它们中选择。
回答by carson
I believe global temporarytables are the same. They will give you private access to a temporary table that dies when the session ends:
我相信全局临时表是一样的。他们将为您提供对会话结束时死亡的临时表的私有访问权限：
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.
全局临时表中的数据是私有的，因此会话插入的数据只能由该会话访问。全局临时表中特定于会话的行可以为整个会话保留，或者只为当前事务保留。ON COMMIT DELETE ROWS 子句指示应在事务结束时删除数据。
After reading the question a few more times I believe it the main difference, and maybe your issue is that the temporary tables persist between sessions. So the exact equivalent is not possible as you would imagine in Oracle it would be something like:
在多次阅读问题后，我认为这是主要区别，也许您的问题是临时表在会话之间持续存在。因此，不可能像您在 Oracle 中想象的那样完全等效，它类似于：
CREATE GLOBAL TEMPORARY TABLE my_temp_table ON COMMIT DELETE ROWS select * from other table;
That table will live until it is dropped even through sessions although the data in it does not. Instead you would need to create the temporary table in advance.
该表将一直存在，直到它通过会话被删除，尽管其中的数据没有。相反，您需要提前创建临时表。
回答by tardate
The temporary table model is somewhat different in Oracle, and centers around the "CREATE GLOBAL TEMPORARY TABLE.." statement. Temp table definitions are always global, but data is always private to the session, and whether data persists over a commit depends on whether the qualification "on commit preserve rows" or "on commit delete rows" is specified.
Oracle 中的临时表模型有些不同，它以“CREATE GLOBAL TEMPORARY TABLE..”语句为中心。临时表定义始终是全局的，但数据始终是会话私有的，并且数据是否在提交后持续存在取决于是否指定了“提交时保留行”或“提交时删除行”的限定条件。
I have some Perl scripts and a blogpost that explores the specific behaviour or Oracle temp tables on my blog.
回答by Jim Hudson
Yes, carson has it right. Global temporary tables are only visible to the session that creates them, and disappear either at the first commit or rollback, or at the end of the session. You can set that when you create the gtt.
是的，卡森说得对。全局临时表仅对创建它们的会话可见，并且在第一次提交或回滚时或在会话结束时消失。您可以在创建 gtt 时设置它。
