Oracle Temporary Tables(Oracle 临时表).docxVIP

Oracle Temporary Tables(Oracle 临时表)1. 建立临时表语法A．ON COMMIT DELETE ROWS 定义了建立事务级临时表的方法CREATE GLOBAL TEMPORARY TABLE TABLE_NAME-----(COUMNS …)-----AS SELECT … FROM TABLE… ON COMMIT DELETE ROWS;当前session发出commit/rollback命令，则该事务周期发生的所有数据自动被Oracle删除(Oracle truncate table)。但不影响任何其他session的数据。B．ON COMMIT PRESERVE ROWS 定义了创建会话级临时表的方法CREATE GLOBAL TEMPORARY TABLE TABLE_NAME-----(COUMNS …)-----AS SELECT … FROM TABLE…ON COMMIT PRESERVE ROWS;当前session结束(用户正常退出 / 用户不正常退出 / Oracle实例崩溃)，Oracle对这个会话的中发生的数据进行删除(Oracle truncate table)。但不影响任何其他session的数据。2. 特点说明A．临时表数据自动清空后，但是临时表的结构以及元数据还存储在用户的数据字典中。表的定义对所有的会话可见B．临时表不需要DML锁C．可以索引临时表和在临时表基础上建立视图D．在临时表上的索引也是临时的，也是只对当前会话或者事务有效E．临时表可以拥有触发器F．可以用export和import工具导入导出临时表的定义，但是不能导出数据3. 使用技巧A．当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中B．程序执行过程中可能需要存放一些临时的数据，可以将这类数据放在临时表里非常方便C．存储过程中用到临时表:1 在建立临时表前，应先加上对表名的判断Select count(*) into v_count from user_tables where table_name = ‘XXX’;If v_count=0 thenCreate global temporary table …在存储过程结束处，应该记得删除表execute immediate drop table t_temp;2 若在存储过程中建的临时表为on commit delete rows。且存储过程中无commit/rollback命令，则在当前会话执行存储过程后，在该存储过程中发生的数据仍然可以select到，因为没有发出commit/rollback命令。因此无论是以何种形式建立的临时表，或者PLSQL中没有建立任何临时表，但只要在PLSQL中有DML(insert/update/delete)的操作，则在PLSQL结束处应加上COMMIT; 否则对有临时表的情况造成表数据无法及时清除，对没有临时表的情况造成对DML操作的表的死锁D．数据处理比较复杂的时候临时表快，反之视图快点E．在仅仅查询数据的时候建议用游标: open cursor for sql clause; 有时可使用临时表避免其他时候使用游标4. 与SQL Server临时表的对比A．SQL Server临时表的临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除B．SQL Server临时表的临时表有本地和全局两种类型的临时表，二者在名称、可见性和可用性上均不相同本地临时表的名称以单个数字符号#打头，仅对当前的用户连接是可见的，当用户从SQL Server 2000实例断开连接时被删除。全局临时表的名称以数学符号##打头，创建后对任何用户都是可见的，当所有引用该表的用户从SQL Server断开连接时被删除EG:1 如果创建名为employees的表，则任何人只要在数据库中有使用该表的安全权限就可以使用该表，除非它已删除。2 如果创建名为#employees的本地临时表，只有创建者能对该表执行操作且在断开连接时该表自动删除。3 如果创建名为##employees的全局临时表，数据表中的任何用户均可对该表执行操作。如果该表在创建者创建后没有其他用户使用，则当您断开连接时该表删除。如果该表在创建者创建后有其他用户使用，则SQL Server在所有用户断开连接后菜自动删除该表C．小结1 SQL Server临时表是一种”内存表”，表是存储在内存中的。Oracle临时表除非执行DROP TABLE，否则表定义会保留在数据字典中2 SQL Server临时表不存在类似Oracle临时表事务级别上的功能3 SQL Server本地临时表#与Oracle