代码之家 › 专栏 › 技术社区 › Richard Neil Ilagan

SQL Server/Oracle:私有临时表

sql-server-2008 oracle sql

Richard Neil Ilagan · 技术社区 · 14 年前

在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行为?或者,使用临时数据的唯一方法是在每次工作迭代时重复创建临时表吗?

3 回复 | 直到 13 年前

Shannon Severance 14 年前

正如您所发现的,SQL Server和Oracle临时表有根本不同。

在Oracle中,全局临时表是存储临时会话特定(或事务特定)数据的永久对象。

在SQL Server中,临时表是存储临时数据的临时对象,其中,临时表存储会话本地的数据,临时表存储全局数据。(我从未需要SQL Server全局临时表,也不知道它们解决了什么问题。)如果临时表是在存储过程中创建的,则在存储过程退出时将删除它。否则,会话关闭时将删除它。

不,实际上没有办法让SQL Server模仿Oracle。您可以使用一个带有额外列的普通表来存储会话ID。但是,对于较少的日志记录,您不会得到临时表的优势。你必须手动删除临时数据。处理从过早退出的会话中清除的问题。

编辑: Oracle和SQL Server的另一个区别是,SQL Server允许将DDL与其他语句包装在事务中。因此,如果需要将临时表用作更大事务的一部分,那么 create table #table_name... 语句不会像 create table 这句话在甲骨文里是可以的。

codingguy3000 14 年前

这不是主题,但您是否知道在SQL Server中可以创建这样的临时表:

select *
into #temp_table
from mytable

David McKittrick 13 年前

当需要合并来自具有公共合并字段的不同源的数据时,SQL中的临时表可能非常有用,但在合并之前需要求和金额,以便比较两个源的净合计。在一个有用的金融体系中。当我们从SQL Server迁移到Oracle时,我很失望,因为我失去了这个功能。

下面的示例用于PeopleSoft财务实现。一旦在两者之间运行了接口,预算模块(KK表)和总帐(日记账)的资金余额应相同。下面的查询从KK表中按资金汇总预算金额,并将其存储在临时表中,然后从总帐中按资金汇总相应的金额,然后合并两个预汇总的数据表,以允许比较两个来源的每个资金的净金额,并且仅当以下两个来源的金额之间存在差异时才列出结果:基金。在这种情况下,预算和总账模块不同步。这实际上是一个非常优雅的解决方案,不需要为这个查询/报告创建一个可供其他人使用的全局临时表。

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

/*** 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