Oracle、SqlServer——临时表

一、概述：

oracle数据库的临时表的特点：

• 临时表默认保存在TEMP中；
• 表结构一直存在，直到删除；即创建一次，永久使用；
• 不支持主外键。
• 可以索引临时表和在临时表基础上建立视图。建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效. 临时表可以拥有触发器.

临时表分为事务型和会话型

• 会话型：基于会话的临时表，数据从会话开始到会话结束之间是有效的，当会话结束时，表中的数据会自动清空。不同会话之间的数据是隔离的，互不影响。
• 事务型：基于事务的临时表，其比会话型的临时表更灵活，可以认为是从会话型临时表的优化，因为表中的数据的保存时间与会话型相同，有效期从会话开始，在会话结束时，数据库自动清空临时表中的数据。与会话型临时表不同的是 在事务提交或者事务回滚 时将清空临时表中的数据。当然，会话型临时表在会话期间可以采用 delete 临时表名;的方式清空临时表数据。

语法:

create global temporary table 临时表名
(
……
)
on commit [preserve|delete] rows;　

• preserve时就是会话（SESSION）型的临时表
• delete就是事务（TRANSACTION）型的临时表 

而TRANSACTION级的临时表数据在TRANACTION结束后消失，即COMMIT/ROLLBACK或结束SESSION都会清除TRANACTION临时表数据。 

二、示例：

1、创建临时表

declare tempisexist integer:=0;
begin
　　select count(*) into tempisexist from all_tables where table_name=‘NK_SLTJ‘;
　　if tempisexist=0 then--不存在临时表就创建一个
　　　　execute immediate(‘
　　　　　　CREATE GLOBAL TEMPORARY TABLE NK_SLTJ
　　　　　　(
　　　　　　　　LCK_FJNM varchar(36),
　　　　　　　　LCMC varchar(70),
　　　　　　　　GFX integer,
　　　　　　　　ZFX integer,
　　　　　　　　DFX integer,
　　　　　　　　KZDSL integer
　　　　　　)
　　　　　　on commit preserve rows‘　　--preserve表示回话级。
　　　　);
　　end if;
end;

二、使用临时表

　　declare
　　　　FXZ NUMBER;
　　　　FJNM varchar(36);
　　　　ZZNM varchar(36):=‘77c48880-a2be-4d3c-97b7-26f8de0bee63‘;
　　　　CURSOR NKFXZcur is select NKFXJZ_FXZ,NKLCK_FJNM from NKFXJZ INNER JOIN NKLCK ON NKLCK_NM=NKFXJZ_LCNM where NKLCK_ZZNM=ZZNM;
　　begin
　　　　delete NK_SLTJ;　--防止在统一会话中多次执行导致数据重复，因此程序一开始就应清空临时表数据　
　　　　insert into NK_SLTJ SELECT NKLCK_FJNM, NKLCK_MC,0,0,0,KZD FROM　　--向临时表插入数据。
　　　　　　( select KZDSL.NKLCK_FJNM,KZDSL.KZD,LCJZ.NKLCK_MC from
　　　　　　(select substr(NKLCK_FJNM,1,4) as NKLCK_FJNM, count(NKNKJZ_KZD) AS KZD from NKLCK LEFT join NKNKJZ ON NKNKJZ_LCNM =NKLCK_NM WHERE NKLCK_ZZNM=ZZNM group by substr(NKLCK_FJNM,1,4)) KZDSL
　　　　INNER JOIN
　　　　　　(select NKLCK_FJNM, NKLCK_MC from NKLCK WHERE NKLCK_JC=1 and NKLCK_ZZNM=ZZNM) LCJZ ON LCJZ.NKLCK_FJNM=KZDSL.NKLCK_FJNM) ccc;

　　　　open NKFXZcur; --打开游标
　　　　fetch NKFXZcur INTO FXZ,FJNM; --提取游标数据
　　　　while NKFXZcur%FOUND loop --循环
　　　　　　if FXZ>=3.5 and FXZ<=5 then --高风险
　　　　　　　　update NK_SLTJ set GFX=GFX+1 where LCK_FJNM=substr(FJNM,0,4);
　　　　　　elsif FXZ>2 and FXZ<3.5 then --中风险
　　　　　　　　update NK_SLTJ set ZFX=ZFX+1 where LCK_FJNM=substr(FJNM,0,4);
　　　　　　else
　　　　　　　　if FXZ>=0 and FXZ<=2 then --低风险
　　　　　　　　　　update NK_SLTJ set DFX=DFX+1 where LCK_FJNM=substr(FJNM,0,4);
　　　　　　　　end if;
　　　　　　end if;
　　　　　　fetch NKFXZcur INTO FXZ,FJNM ;
　　　　end loop;--结束循环
　　close NKFXZcur; --关闭游标
end;

三、与SqlServer 临时表的区别

oracle：

• 有事务表和会话表两种；
• 临时表永远保留表结构，数据保存在磁盘上。与永久表唯一不同的是在用户会话结束或者事务提交、回滚后删除数据，但是保留表结构；

SqlServer则是本地和全局临时表。

• 本地表：表名以 # 开头；在会话结束时彻底删除表结构，不同用户之间彼此隔绝；
• 全局表：表名以 ## 开头，对所有用户均可见，当一个用户创建全局表后，其他用户可以访问全局表，但创建的用户结束回话时，全局表虽然接受现有连接全局表的用户访问，但不再接受新用户的访问，当已连接的用户断开连接后将彻底删除表结构。
• SqlServer：在需要临时表时手动创建，数据保存在内存中，会话结束时直接删除表结构。