ORACLE 在存储过程中使用临时表

曹3170

 
　　一 临时表语法
　　
　　临时表只在Oracle 8i 以及以上产品中支持。  
　　语法：
[java] view plaincopyprint?

• create global temporary table 临时表名 on commit preserve/delete rows    
  
• --preserve:SESSION级的临时表，delete:TRANSACTION级的临时表.  
  

　　create global temporary table 临时表名 on commit preserve/delete rows  --preserve:SESSION级的临时表，delete:TRANSACTION级的临时表. 
　　
　　SESSION级的临时表数据在整个SESSION都存在，直到结束此次SESSION；而TRANSACTION级的临时表数据在TRANACTION结束后消失，即COMMIT/ROLLBACK或结束SESSION都会清除TRANACTION临时表数据。 
　　当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。当一个会话结束(用户正常退出、用户不正常退出、ORACLE实例崩溃)或者一个事务结束的时候，Oracle对这个会话的表执行 TRUNCATE 语句清空临时表数据，但不会清空其它会话临时表中的数据。临时表可以使用触发器。
　　
　　二 临时表ORACLE数据库与sqlserver的区别
　　
　　ORACLE临时表，transaction或者session结束，会清空表数据。但是表还存在。
　　SQL SERVER临时表，存储在 tempdb 中，当不再使用时会自动删除。
　　
　　1 SQL SERVER临时表是一种”内存表”，表是存储在内存中的。ORACLE临时表除非执行DROP TABLE,否则表定义会保留在数据字典中；
　　2 SQL SERVER临时表不存在类似ORACLE临时表事务级别上的功能；
　　3 SQL SERVER本地临时表(#) 与 ORACLE的会话级别临时表类似，但是在会话退出的时候，SQL SERVER会自动删除，ORACLE不会删除表；
　　4 SQL SERVER的全局临时表(##) 是指多个连接共享同一片内存。当没有指针引用该内存区域时，SQL SERVER自动释放全局临时表。
　　
　　ORACLE不是一种内存中的数据库，所以如果ORACLE类似SQL SERVER 频繁的对临时表进行建立和删除，必定会影响性能。所以ORACLE会保留临时表的定义直到用户DROP TABLE。
　　在ORACLE中，如果需要多个用户共享一个表(类似SQL SERVER的全局临时表##)，则可以利用永久表，并且在表中添加一些可以唯一标识用户的列。利用触发器和视图，当用户退出的时候，根据该登陆用户的唯一信息删除相应的表中的数据。 但这种方法给ORACLE带来了一定量的负载。
　　
　　
　　三  使用
　　
　　1 判断表不存在就创建表
　　
[java] view plaincopyprint?

• create ...  
  
• is  
  
•     temptable_name varchar2(20);  
  
•     v_count number(1);  
  
• begin  
  
•     temptable_name := 'TEMP_TABLENAME';  
  
•     select count(*) into v_count from tab where tname = temptable_name;  
  
•     if(v_count = 0) then  
  
•         execute immediate 'create table ...';  
  
•     else  
  
•         --其他表存在的操作  
  
•     end if;  
  

　　create ...istemptable_name varchar2(20);v_count number(1);begintemptable_name := 'TEMP_TABLENAME';select count(*) into v_count from tab where tname = temptable_name;if(v_count = 0) thenexecute immediate 'create table ...';else--其他表存在的操作end if; 
　　
　　2 注意使用时，可能会产生的错误：ORA-08103: object no longer exists，ORA-14452  attempt to create, alter or drop an index on temporary table already in use
　　
　　ORA-14452 ： 通过1的判断，一般会解决该问题。如果在表使用时，执行删除表操作，会出现该错误。
　　ORA-08103 ： 使用事务级的临时表时，如果，事务提交前执行删除表操作或者在事务提交以后使用，就会产生这样的问题。改成会话级别的可以解决这个问题，但是在会话级别的数据的正确性，可能会因为使用连接池，同一个会话中不同操作而产生错误。