前言
我们的软件设计数据库里的表一般都会挺多的,特别是用户想要到新的报表格式时,相关的查询可能会关联多个数据表,如果全部用select的表进行关联查询除了写语句会非常的长,测试时如果出现问题也不容易修改,所以我们就会引入一个临时表的概念,分步把要组合的数据插入到临时表中,再用select查询临时表输出数据。这样做的好处可以分部进行处理,调试的时候也可以看到临时表中每步的数据变化,方便找到问题。
Oracle临时表介绍
Oracle的临时表temporary tables,一般我们分为两种临时表,分别的会话级的临时表和事务级的临时表。
1.会话级临时表
会话级临时表是指临时表中的数据只在会话生命周期之中存在,当用户退出会话结束的时候,Oracle自动清除临时表中数据。
格式:
Create Global Temporary Table Table_Name ( Col1 Type1, Col2 Type2 ... ) On Commit Preserve Rows;
2.事务级临时表 事务级临时表是指临时表中的数据只在事务生命周期中存在。 Create Global Temporary Table Table_Name ( Col1 Type1, Col2 Type2 ... ) On Commit Delete Rows;
当一个事务结束(commit or rollback),Oracle自动清除临时表中数据。
注意
一般我们在SQL里面用到临时表都会是用到时候创建,用完后删除(或是自动删除),如下:
但是在Oracle里面因为有会话级和事务级临时表,用完后会自动清空等,不建议每次都Drop然后再Create,主要原因也是我在测试的过程中发现用到Drop临时表时报过错了,当时的错误没记下来,后来就是变为没有就创建,有就清空数据的写法。
临时表用法
我们直接上代码
declare vi_count integer; vs_sSql varchar2(4000):=''; begin vs_sSql:= 'select count(*) from user_tables where table_name = upper(' || chr(39) || 'temp_cstable' || chr(39) || ')'; execute immediate vs_sSql into vi_count; dbms_output.put_line(vi_count); --判断temp_cstable的临时表是否存在,如果存在清空里面数据,不存在即创建 if vi_count>0 then vs_sSql := 'delete from temp_cstable'; execute immediate vs_sSql; else vs_sSql := ' create global temporary table temp_cstable ( incode varchar2(20), barcode varchar2(20), xstotal number, yhtotal number) on commit delete rows'; execute immediate vs_sSql; end if; end;
我们上面这就是用到了一个事务级的临时表,其实创建这个就非常简单。
-END-