临时表和全局临时表有什么区别？

临时表一般存储在临时表空间

分两种，事务级和会话级

一般用来存储临时需要的数据

事务级的临时表在事务提交后自动删除，会话级临时表在会话结束后删除

会话级

create global temporary table tablename(col1 coltype,..) on commit preserve rows;

事务级

..(与会话级相同).. on commit delete rows;

对我有用[0]丢个板砖[0]引用举报管理TOP精华推荐：oracle面试题目总结---（300分相赠）！

oraclemch

(杉树)

等　级：

#2楼 得分：10回复于：2009-09-18 19:17:16我们在创建数据表的时候，若没有特殊的指明，那么我们创建的表是一个永久的关系型表格，也就是说，这个表格中对应的数据，除非是我们显示的删除的话，表中的数据是永远都存在的。相对应的，在Oracle数据库中还有一种类型的表，叫做临时表。这个临时表跟永久表最大的区别就是表中的数据不会永远的存在。当一个会话结束或者事务结束的时候，这个临时表中的数据，不用用户自己删除，数据库自己会自动清除。

1、 事务临时表的管理。

(1) 事务临时表的创建。

Oracle数据库根据临时表的性质不同，可以分为事务临时表与会话临时表。事务临时表是指数据只有在当前事务内有效。一般情况下，如果在创建数据表的时候，没有特殊指明这表是会话临时表的话，则该表默认为事务临时表。

我们可以以下面的语句创建事务临时表。

Create global temporary table Temp_user

(ID NUMBER(12) Primary key,name varchar2(10));

笔者建议：

这个创建临时表的语句中，虽然没有显性的指明该表是事务临时表，但是，默认的情况下，若没有指明是什么临时表的话，系统默认是事务临时表。我们要创建事务临时表时，可以不指定关键字。但是，这查看起来比较麻烦。我建议，无论在建立什么临时表，都要利用具体的关键字来显形的指明，这大家看起来都方便。一般可以利用ON COMMIT DELETE ROWS关键字来说明该表就是事务性的临时表，而不是会话性质的临时表。

(2) 事务临时表数据的变化分析。

事务临时表的话，当事务结束的时候，就会清空这个事务临时表。所以，当我们在数据库临时表中插入数据后，只要事务没有提交的话，该表中的数据就会存在。但是，当事务提交以后，该表中的数据就会被删除。而且，这个变化不会在重做日志中显示。

具体事务临时表与会话临时表有什么区别，我们在介绍完会话临时表后会详细介绍。

2、 会话临时表的管理。

会话临时表，顾名思义，是指数据只在当前会话内有效的临时表。关闭当前会话或者进行新的连接之后，数据表中的内容就会被清除。那会话临时表跟事务临时表到底有什么区别呢?我们以一个实例来看其中的区别。

(1) 首先，创建一个会话临时表。

CREATE GLOBAL TEMPOPARY TABLE TEMP_USER

(ID NUMBER(12) Primary key,name varchar2(10))

ON COMMIT PRESERVE ROWS;

也就是说，会话临时表跟事务临时表的创建语法大致相同，只有最后的关键字有区别。不过两个表虽然类似，但是其内部的处理机制还是有比较大的区别。

(2) 往该表中插入数据。　

　Insert into TEMP_USER values(1001,’victor’);

往数据库临时表中插入数据的方法，跟往普通表中插入数据的方法是一样的，都利用insert into语句进行操作。该临时表的数据在会话结束之前都是存在这个表格中的。

(3) 提交该事务并查询相关记录。

我们利用COMMIT的语句把该事务提交以后，再用SELECT查询语句进行查询。我们知道，若该表是事务临时表的话，则当该事务结束以后，该表中的内容就会被删除。但是，这是会话临时表，所以即使该事务提交了，但是，利用SELECT语句进行查询时，仍然可以查到该条员工记录。

(4) 结束当前会话，并重新连接数据库。

关闭当前会话，从新连接到数据库后，再利用SELECT语句查询时，会有什么结果呢?此时，就查不到我们刚才插入的数据。这也就是说，在关闭对话的时候，数据库系统已经把原有的数据删除了。从以上的分析我们可以看中，会话临时表与事务临时表主要的差异就在于删除数据时机的不同。事务性临时表是在事务提交的时候清除数据，而会话性临时表则是在关闭当前会话的时候清除临时表。只要当前会话没有关闭，即使事务完成了，会话临时表中的数据仍然存在，不会被清除。

3、 临时表管理需要注意的地方。

临时表相对与其他表来说，是一种比较特殊的表结构，但是，作用又比较大，Oracle数据库若没有这种表的话，还真是不行。为了管理好这种特殊的表，我们需要注意几个细节。

一是要注意临时表不能永久的保存数据。只所以称为临时表，就是因为该表中的内容只是临时存在的。当一个会话或者事务结束时，该表中的内容就会被自动清空。所以，在临时表中，一般不要保存永久数据。在实务中，有个不好的操作习惯，就是有些人在测试数据库的时候，喜欢把测试的数据放在临时数据表中。其实，这是对Oralce临时数据表认识的错误。若我们在数据库中，把要测试的数据，如销售定单的内容放在数据库的临时表中的话，则在其他功能中，如要测试销售定单日报表的功能时，就会找不到相关的定单内容。因为离开特定的会话或者事务的话，临时表中的内容就会不存在了。所以，Oralce数据库中所讲的临时表不是给我们来存储测试数据的。

二是临时表中的数据不会备份、恢复，对其的修改也不会有任何的日志信息。若我们在操作数据库的时候，往数据库的临时表中存入了一些信息。此时突然服务器出现当机。此时，我们想通过数据库备份文件恢复数据库临时表中的内容，或者查看临时表的日志信息，都是无法实现的。也就是说，当服务器以外死机重新启动后，临时表中的内容就会被清空。在数据库的任何地方，如数据库备份文件或者日志信息中，都查不到在重新启动之前数据库临时表中保存了哪些内容，就好象根本没有对临时表进行操作一样。

三是临时表表空间的管理。临时表在Oracle数据库中，也是表的一种，其也有对应的表空间。在创建临时表的时候，若我们不指定表空间的话，默认的表空间是SYSTEM。对于临时表的表空间管理的话，我们需要注意一个小的细节。若我们把临时表的表空间归属为SYSTEM的话，也就是说，在创建临时表的时候不具体指定具体的表空间，则这个默认的表空间是不能被删除的。而若我们在创建临时表表空间的时候，指定为SYSTEM以外的表空间的话，则在不需要这表空间的时候，我们可以删除。所以，为了后续管理的方便，笔者还是建议大家在创建临时表的时候，要指定表空间。

四是要注意一个问题，临时表只是数据是临时的，而表仍然是永久的。也就是说，当一个会话结束或者一个事务完成时，其临时表中的数据虽然删除了，但是，临时表本身仍然是存在的。也就是说。Oracle数据库中的临时表表是全局的，只是数据是临时的。这跟SQL Server数据库系统具有比较大的区别。其实，这两个数据库在临时表的处理上有很大的不同，各有各的特色。在以后的文章中，我会专门叙述这两种数据库在临时表管理机制上的不同，欢迎大家关注。

五是要注意Oracle数据库在给临时表填入数据的时候，不会对相应的记录加锁。也就是说，当在临时表上执行DML语句的操作时，不会给记录加锁，也不会将数据的变化内容写到重做(REDO)日志中。所以不能用临时表保存永久的数据，也不能对临时表进行共同的操作。这是新手在管理数据库临时表经常会碰到的问题。

六是临时表与普通表之间不能相互转换。在一般情况下，临时表建立后，该表就不能被转换成永久表。所以，这也说明一个道理，利用临时表作为数据库设计时候的测试表不合适。这个临时表可能跟我们按字面意思理解的临时表有误，不是我们所认为的为了测试表结构而建立的临时表。这一点是我们在刚开始接触ORACLE数据库时，经常会犯的错误。

SqlServer 2000 游标用法小例

DECLARE CURSOR (T-SQL)创建游标 September 14th, 2006 by OoperMan (1 votes, average: 5 out of 5) Loading ... SQL Server 2005 联机丛书 DECLARE CURSOR (Transact-SQL) 更新日期： 2005 年 12 月 5 日

定义 Transact-SQL 服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。DECLARE CURSOR 接受基于 SQL-92 标准的语法和使用一组 Transact-SQL 扩展插件的语法。

Transact-SQL 语法约定

语法

SQL 92 Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR ...{ READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [;] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]

参数

cursor_name 所定义的 Transact-SQL 服务器游标的名称。cursor_name 必须符合标识符规则。有关标识符规则的详细信息，请参阅使用标识符作为对象名称。

INSENSITIVE 定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的这一临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用 SQL-92 语法时，如果省略 INSENSITIVE，则已提交的（任何用户）对基础表的删除和更新都反映在后面的提取中。

SCROLL 指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未在 SQL-92 DECLARE CURSOR 中指定 SCROLL，则 NEXT 是唯一支持的提取选项。如果也指定了 FAST_FORWARD，则不能指定 SCROLL。

select_statement 定义游标结果集的标准 SELECT 语句。在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。

Microsoft 如果 select_statement 中的子句与所请求的游标类型的功能有冲突，则 SQL Server 会将游标隐式转换为其他类型。有关详细信息，请参阅使用隐式游标转换。

READ ONLY 禁止通过该游标进行更新。在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。该选项优于要更新的游标的默认功能。

UPDATE [OF column_name [,…n]] 定义游标中可更新的列。如果指定了 OF column_name [,…n]，则只允许修改列出的列。如果指定了 UPDATE，但未指定列的列表，则可以更新所有列。

cursor_name 所定义的 Transact-SQL 服务器游标的名称。cursor_name 必须符合标识符规则。有关标识符规则的详细信息，请参阅使用标识符作为对象名称。

LOCAL 指定对于在其中创建的批处理、存储过程或 触发器来说，该游标的作用域是局部的。该游标名称仅在这个作用域内有效。在批处理、存储过程、触发器或存储过程 OUTPUT 参数中，该游标可由局部游标变量引用。OUTPUT 参数用于将局部游标传递回调用批处理、存储过程或触发器，它们可在存储过程终止后给游标变量分配参数使其引用游标。除非 OUTPUT 参数将游标传递回来，否则游标将在批处理、存储过程或触发器终止时隐式释放。如果 OUTPUT 参数将游标传递回来，则游标在最后引用它的变量释放或离开作用域时释放。

GLOBAL 指定该游标的作用域对来说连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在断开连接时隐式释放。

注意：如果 GLOBAL 和 LOCAL 参数都未指定，则默认值由 default to local cursor 数据库选 项的设置控制。在 SQL Server 7.0 版中，该选项默认为 FALSE，以便与 SQL Server 的早期版本相匹配，在早期版本中，所有游标都是全局的。该选项的默认值在以后的 SQL Server 版本中可能会更改。有关详细信息，请参阅“设置数据库选项”。

FORWARD_ONLY 指定游标只能从第一行滚动到最后一行。FETCH NEXT 是唯一支持的提取选项。如果在指定 FORWARD_ONLY 时不指定 STATIC、KEYSET 和 DYNAMIC 关键字，则游标作为 DYNAMIC 游标进行操作。如果 FORWARD_ONLY 和 SCROLL 均未指定，则除非指定 STATIC、KEYSET 或 DYNAMIC 关键字，否则默认为 FORWARD_ONLY。STATIC、KEYSET 和 DYNAMIC 游标默认为 SCROLL。与 ODBC 和 ADO 这类数据库 API 不同，STATIC、KEYSET 和 DYNAMIC Transact-SQL 游标支持 FORWARD_ONLY。

STATIC 定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的这一临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。

KEYSET 指定当游标打开时，游标中行的成员身份和顺序已经固定。对行进行唯一标识的键集内置在 tempdb 内一个称为 keyset 的表中。

注意：如果查询引用了至少一个无唯一索引的表，则键集游标将转换为静态游标。

对基表中的非键值所做的更改（由游标所有者更改或由其他用户提交）可以在用户滚动游标时看到。其他用户执行的插入是不可见的（不能通过 Transact-SQL 服务器游标执行插入）。如果删除行，则在尝试提取行时返回值为 -2 的 @@FETCH_STATUS。从游标以外更新键值类似于删除旧行然后再插入新行。具有新值的行是不可见的，并在尝试提取具有旧值的行时，将返回值为 -2 的 @@FETCH_STATUS。如果通过指定 WHERE CURRENT OF 子句利用游标来完成更新，则新值是可见的。

DYNAMIC 定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游标不支持 ABSOLUTE 提取选项。

FAST_FORWARD 指定启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。如果指定了 SCROLL 或 FOR_UPDATE，则不能也指定 FAST_FORWARD。

注意：在 SQL Server 2000 中，FAST_FORWARD 和 FORWARD_ONLY 游标选项是互相排斥的。如果指定了二者，则会引发错误。在 SQL Server 2005 中，这两个关键字可以用在同一个 DECLARE CURSOR 语句中。

READ_ONLY 禁止通过该游标进行更新。在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。该选项优于要更新的游标的默认功能。

SCROLL_LOCKS 指定通过游标进行的定位更新或删除保证会成功。将行读取到游标中以确保它们对随后的修改可用时，Microsoft SQL Server 将锁定这些行。如果还指定了 FAST_FORWARD 或 STATIC，则不能指定 SCROLL_LOCKS。

OPTIMISTIC 指定如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不会成功。当将行读入游标时 SQL Server 不会锁定行。相反，SQL Server 使用 timestamp 列值的比较，或者如果表没有 timestamp 列，则使用校验和值，以确定将行读入游标后是否已修改该行。如果已修改该行，则尝试进行的定位更新或删除将失败。如果还指定了 FAST_FORWARD，则不能指定 OPTIMISTIC。

TYPE_WARNING 指定如果游标从所请求的类型隐式转换为另一种类型，则向客户端发送警告消息。

select_statement 定义游标结果集的标准 SELECT 语句。在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。

注意：您可以在游标声明中使用查询提示；但是，如果还使用 FOR UPDATE OF 子句，则请在 FOR UPDATE OF 之后指定 OPTION (query_hint)。

如果 select_statement 中的子句与所请求的游标类型的功能有冲突，则 SQL Server 会将游标隐式转换为其他类型。有关详细信息，请参阅“隐式游标转换”。

FOR UPDATE [OF column_name [,…n]] 定义游标中可更新的列。如果提供了 OF column_name [,…n]，则只允许修改列出的列。如果指定了 UPDATE，但未指定列的列表，则除非指定了 READ_ONLY 并发选项，否则可以更新所有的列。

备注

DECLARE CURSOR 定义 Transact-SQL 服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。OPEN 语句填充结果集，FETCH 从结果集返回行。CLOSE 语句释放与游标关联的当前结果集。DEALLOCATE 语句释放游标所使用的资源。

DECLARE CURSOR 语句的第一种格式使用 SQL-92 语法声明游标行为。DECLARE CURSOR 的第二种格式使用 Transact-SQL 扩展插件，这些扩展插件允许您使用在 ODBC 或 ADO 的数据库 API 游标函数中所使用的相同游标类型来定义游标。

不能混淆这两种格式。如果在 CURSOR 关键字的前面指定 SCROLL 或 INSENSITIVE 关键字，则不能在 CURSOR 和 FOR select_statement 关键字之间使用任何关键字。如果在 CURSOR 和 FOR select_statement 关键字之间指定任何关键字，则不能在 CURSOR 关键字的前面指定 SCROLL 或 INSENSITIVE。

如果使用 Transact-SQL 语法的 DECLARE CURSOR 不指定 READ_ONLY、OPTIMISTIC 或 SCROLL_LOCKS，则默认值如下：

如果 SELECT 语句不支持更新（由于权限不够、访问的远程表不支持更新等等），则游标为 READ_ONLY。

STATIC 和 FAST_FORWARD 游标默认为 READ_ONLY。

DYNAMIC 和 KEYSET 游标默认为 OPTIMISTIC。

游标名称只能被其他 Transact-SQL 语句引用。它们不能被数据库 API 函数引用。例如，声明游标之后，不能通过 OLE DB、ODBC 或 ADO 函数或方法引用游标名称。不能使用提取函数或 API 的方法来提取游标行；只能通过 Transact-SQL FETCH 语句提取这些行。

在声明游标后，可使用下列系统存储过程确定游标的特性。

系统存储过程 说明 sp_cursor_list 返回当前在连接上可视的游标列表及其特性。

sp_describe_cursor 说明游标属性，例如是只前推的游标还是滚动游标。

sp_describe_cursor_columns 说明游标结果集中的列的属性。

sp_describe_cursor_tables 说明游标所访问的基表。

在声明游标的 select_statement 中可以使用变量。游标变量值在声明游标后不发生更改。在 SQL Server 版本 6.5 以及早期版本中，每次重新打开游标时都会重新刷新变量值。

权限

默认情况下，将 DECLARE CURSOR 权限授予对游标中所使用的视图、表和列具有 SELECT 权限的任何用户。

示例

A. 使用简单游标和语法

在打开该游标时所生成的结果集包括表中的所有行和所有列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为未指定 SCROLL 选项，所以 FETCH NEXT 是唯一可用的提取选项。

DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor B. 使用嵌套游标生成报表输出

以下示例显示如何嵌套游标以生成复杂的报表。为每个供应商声明内部游标。

SET NOCOUNT ON DECLARE @vendor_id int, @vendor_name nvarchar(50), @message varchar(80), @product nvarchar(50) PRINT '-------- Vendor Products Report --------' DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' ' SELECT @message = '----- Products From Vendor: ' + @vendor_name PRINT @message -- Declare an inner cursor based -- on vendor_id from the outer cursor. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id-- Variable value from the outer cursor OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS 0 PRINT ' ' WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = ' ' + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product END CLOSE product_cursor DEALLOCATE product_cursor -- Get the next vendor. FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name END CLOSE vendor_cursor DEALLOCATE vendor_cursor

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/qinghecool/archive/2008/06/25/2586675.aspx

鹏仔微信 15129739599 鹏仔QQ344225443 鹏仔前端 pjxi.com 共享博客 sharedbk.com