SQL Server / Oracle：私有临时表

Question

SQL Server / Oracle：私有临时表

sqloraclesql-server-2008

5

在Oracle中，您可以使用以下方式创建临时表：

CREATE GLOBAL TEMPORARY TABLE temp_table (
    field1 NUMBER,
    field2 NUMBER
)
ON COMMIT DELETE ROWS;

这是一个相当不错的功能，可以创建一个对所有人可见但只有插入数据的用户自己可见的表。此外，该数据会在事务或会话结束时（根据声明）自动删除，不会影响其他人的临时数据。

然而，在SQL Server中，您可以使用以下方式创建临时表：

CREATE TABLE #temp_table (field1 INT, field2 INT);

据我理解，这与 Oracle 的实现有着实质性和功能性的差异。该临时表只对您可见，并且在使用后立即删除（该表）。

SQL Server 是否具有模仿上述 Oracle 行为的能力？还是唯一的处理临时数据的方法涉及每次迭代工作都需要重复创建临时表？

- Richard Neil Ilagan

你能解释一下为什么你想在SQLServer中模仿Oracle的行为吗？这只是为了避免执行CREATE TABLE语句的要求吗？ - user359040

@Mark ~ 主要是这样。但如果我处理 (1) 重复的临时数据插入，(2) 数据格式和字段基本相同，(3) 并且是用户唯一的（即数据私有于用户会话），按照Oracle的实现，在表格中处理所有这些操作就更加合理 :) - Richard Neil Ilagan

Oracle私有临时表 - Lukasz Szozda

3个回答

2

在SQL中使用临时表可以非常有用，当您需要合并具有共同合并字段的不同来源的数据，但在合并之前需要对金额进行求和以比较两个来源的净总额时。在财务系统中，这非常有用。我们从SQL Server迁移到Oracle后失去了这个功能，我感到很失望。

以下示例适用于PeopleSoft财务实施。一旦在两者之间运行接口，预算模块（KK表）和总账（journal）应该对一个基金具有相同的余额。以下查询从KK表按基金汇总预算金额并将其存储在临时表中，然后按基金从总账中汇总相应金额，然后合并两个预先汇总的数据表以允许比较来自两个来源的每个基金的净金额，并仅在基金金额存在差异时列出结果。在这种情况下，预算和GL模块不同步。这实际上是一个相当优雅的解决方案，而不需要为此查询/报告创建全局临时表供其他人使用。

希望有人会发现这很有用。当时它帮助了我。

/*** START NESTED QUERY #1                                             ***/
/*** THE FOLLOWING CREATES TWO TEMP TABLES WITH NET AVAILABLE PER FUND ***/
/*** WITH ONE AMOUNT BASED ON KK TABLES AND ONE AMOUNT BASED ON        ***/
/*** BUDGETARY GL BALANCES. THEN TEMP TABLES ARE MERGED BY FUND AND    ***/
/*** NET DIFFERENCE CALCULATED-SELECTING  FUNDS WITH DIFFERENCES.      ***/
/*** IF BUDGET CHECKING IS COMPLETE AND JOURNALS CREATED AND POSTED    ***/
/*** THERE SHOULD BE NO DIFFERENCES.                                   ***/

--create a temp table with journal amounts summed by fund code
CREATE TABLE #JRNLsum(
FUND_CODE char(5),
JRNLAMT decimal(19,2) )
INSERT INTO #JRNLsum (FUND_CODE, JRNLAMT)
select FUND_CODE, sum(MONETARY_AMOUNT * -1) JRNLAMT 
FROM PS_JRNL_LN 
INNER JOIN PS_JRNL_HEADER 
ON PS_JRNL_LN.JOURNAL_ID = PS_JRNL_HEADER.JOURNAL_ID 
where ((ACCOUNT BETWEEN 430000 and 469999) and (FISCAL_YEAR >= '2009')) 
GROUP BY FUND_CODE order by FUND_CODE


--create a temp table with KK ledger amounts summed by fund code
CREATE TABLE #KKsum(
FUND_CODE char(5),
KKAMT decimal(19,2) )
INSERT INTO #KKsum (FUND_CODE, KKAMT) 
select FUND_CODE, sum(POSTED_TOTAL_AMT  * -1) KKAMT
from PS_LEDGER_KK where LEDGER like 'FUND_%'
group by FUND_CODE order by FUND_CODE

--join kk temp date to journal temp data, keep only
--fund code, kk net amount, and journal net amount
--and select only fund codes where there is a difference
--between kk net amount and journal net amount
select #KKsum.FUND_CODE, JRNLAMT, KKAMT from #JRNLsum
INNER JOIN #KKsum
on #KKsum.FUND_CODE=#JRNLsum.FUND_CODE 
where (JRNLAMT - KKAMT) <> 0.00


--drop the two temp tables
drop table #KKsum
drop table #JRNLsum

/*** END NESTED QUERY #1

- David McKittrick

1

这个话题有些偏离，但你知道在SQL Server中可以像这样创建临时表吗：

select *
into #temp_table
from mytable

- codingguy3000

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Shannon Severance · Accepted Answer

正如您所发现的，SQL Server和Oracle临时表在本质上是不同的。

在Oracle中，全局临时表是永久对象，用于存储临时会话特定（或事务特定）数据。

在SQL Server中，临时表是存储临时数据的临时对象，其中#temp_tables存储仅限于会话的数据，而##temp_tables存储全局数据。（我从未需要使用SQL Server全局临时表，也不知道它们解决了什么问题。）如果#temp_table是在存储过程中创建的，则在存储过程退出时将删除它。否则，当会话关闭时将删除它。

并且，不，实际上没有办法使SQL Server模仿Oracle。您可以使用具有额外列存储会话ID的普通表。但是，您将无法获得与较少日志记录相关的临时表的优点。您必须手动删除临时数据。并处理从过早退出的会话清理的问题。

编辑：Oracle和SQL Server之间的另一个区别是，SQL Server允许将DDL包装在与其他语句一起的事务中。因此，如果您需要将临时表用作较大事务的一部分，则create table #table_name...语句不会像在Oracle中一样隐式提交当前事务。