问题描述
在Oracle中，您可以使用以下方式创建临时表:
In Oracle, you can create a temp table using something like:
CREATE GLOBAL TEMPORARY TABLE temp_table (
field1 NUMBER,
field2 NUMBER
)
ON COMMIT DELETE ROWS;
...这可能很漂亮，因为这将创建一个所有人都可以看到的表，但是一个INSERT插入表中的数据仅对他或她可见.此外，该数据会在交易或会话结束时自动删除(取决于其声明)，而不会损害其他所有人的临时数据.
... which could be pretty nifty, as this creates a table visible to everyone, but the data one INSERTs into the table is visible only to him or her. Additionally, that data is automatically deleted on at the end of the transaction or the session (depending on its declaration), leaving everyone else's temporary data unharmed.
但是，在SQL Server中，您可以使用以下命令创建临时表:
In SQL Server, however, you can create a temp table with:
CREATE TABLE #temp_table (field1 INT, field2 INT);
...据我所知，它在功能和功能上与Oracle的实现完全不同.该临时表仅对您可见，使用后将立即删除(该表).
... which, as I understand it, is substantially and functionally different than Oracle's implementation. This temp table is visible only to you, and is dropped (the table) immediately after use.
SQL Server中是否有能力模仿如上所述的Oracle行为?还是使用临时数据的唯一方法是每次工作迭代都必须重复创建临时表?
Is there any capacity in SQL Server to mimic the Oracle behavior as described above? Or is the only way to work with temporary data involve having to repeatedly CREATE the temp table with each iteration of work?
推荐答案
正如您所发现的SQL Server& Oracle临时表有根本的不同.
As you have discovered SQL Server & Oracle temporary tables are fundamentally different.
在Oracle中，全局临时表是存储临时会话特定(或事务特定)数据的永久对象.
In Oracle global temporary tables are permanent objects that store temporary session specific (or transaction specific) data.
在SQL Server中，临时表是存储临时数据的临时对象，其中#temp_tables存储会话本地数据，而## temp_tables存储全局数据. (我从不需要SQL Server全局临时表，也不知道它们解决了什么问题.)如果#temp_table是在存储过程中创建的，则在存储过程退出时将被删除.否则，它将在会话关闭时被删除.
In SQL Server temporary tables are temporary objects storing temporary data, with #temp_tables storing data that is local to a session and ##temp_tables storing data that is global. (I have never had a need for SQL Server global temp tables and don't know what problem they solve.) If the #temp_table was created in a stored procedure it will be dropped when the stored procedure exits. Otherwise it will be dropped when the session closes.
不，确实没有办法使SQL Server模仿Oracle.您可以将普通表与额外的列一起使用，以存储会话ID.但是就减少日志记录而言，您将无法获得临时表的优势.您必须手动删除临时数据.并处理过早退出的会话.
And no, there really isn't a way to make SQL Server mimic Oracle. You could use a normal table with an extra column storing a session ID. But you wouldn't get the advantages of temp tables with respect to less logging. You'd have to manually delete the temp data. And deal with cleaning up from sessions that quit prematurely.
编辑:Oracle和SQL Server之间的另一个区别是SQL Server允许DDL与其他语句一起包装在事务中.因此，如果您需要在临时事务中使用临时表，则create table #table_name...语句将不会像Oracle中的create table语句那样隐式提交当前事务.
Another difference between Oracle and SQL Server is that SQL Server allows DDL to be wrapped in a transaction with other statements. So if you need to use a temp table as part of a larger transaction, the create table #table_name... statement will not implicitly commit the current transaction like a create table statement would in Oracle.
这篇关于SQL Server/Oracle:专用临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！