一、Oracle临时表知识
在Oracle中,临时表分为SESSION、TRANSACTION两种,SESSION级的临时表数据在整个SESSION都存在,直到结束此次SESSION;而 TRANSACTION级的临时表数据在TRANACTION结束后消失,即COMMIT/ROLLBACK或结束SESSION都会清除 TRANACTION临时表数据。
1) 会话级临时表 示例
1.创建
create global temporary table temp_tbl(col_a varchar2(30)) on commit preserve rows
2.插入数据
insert into temp_tbl values('test session table')
3.提交
commit;
4.查询
select *from temp_tbl
可以看到数据'test session table'记录还在。
结束SESSION,重新登录,再查询数据select *from temp_tbl,这时候记录已不存在,因为系统在结束SESSION时自动清除记录 。
2) 事务级临时表 示例
1.创建
create global temporary table temp_tbl(col_a varchar2(30)) on commit delete rows
2.插入数据
insert into temp_tbl values('test transaction table')
3.提交
commit ;
4.查询
select *from temp_tbl
这时候可以看到刚才插入的记录'test transaction table'已不存在了,因为提交时已经晴空了数据库;
同样,如果不提交而直接结束SESSION,重新登录记录也不存在。
二、在Oracle存储中使用临时表的一个例子
描述:档案册借阅时,需要把册拆分成详细的单据,拆分依据是册表中的BILLCODES(若干个用逗号分割的单据号)字段,
临时表用于保存拆分出来的单据信息。拆分结束后直接返回临时表的数据。
create or replace package AMS_PKG as type REFCURSORTYPE is REF CURSOR; procedure SPLIT_VOLUMES (P_CORP_NAME IN varchar2,P_YEAR IN varchar2,P_MONTH IN varchar2,P_VOL_TYPE_CODE IN varchar2,P_BILL_NUM IN varchar2,P_VOLUME_NUM IN varchar2,P_AREA_CODES IN varchar2,P_QUERY_SQL out varchar2,P_OUTCURSOR out refCursorType); end AMS_PKG; / CREATE OR REPLACE PACKAGE BODY "AMS_PKG" as procedure SPLIT_VOLUMES(p_CORP_NAME IN varchar2, --查询条件,公司名称 p_YEAR IN varchar2, --查询条件,会计年度 p_MONTH IN varchar2, --查询条件,期间 p_VOL_TYPE_CODE IN varchar2, --查询条件,凭证类别编码 p_BILL_NUM IN varchar2, --查询条件,信息单号 p_VOLUME_NUM IN varchar2, --查询条件,册号 p_AREA_CODES IN varchar2, --查询条件,所在区域编码(产生册的区域),逗号分割。 --形式如 '12C01','12201','12D01','12E01','12601',存储过程中将使用in的方式进行过滤 p_QUERY_SQL out varchar2, --返回查询字符串 p_OutCursor out refCursorType --返回值 ) is v_sql varchar2(3000); v_sql_WHERE varchar2(3000); v_temp1 varchar2(300); v_temp2 varchar2(300); v_tempBILLCODES varchar2(3000); V_CNT NUMBER(10,0); V_VOLUME_ID NUMBER(10,0); mycur refCursorType; --CURSOR mycur( v varchar2) is -- SELECT VOUCHTYPE,BILLCODES FROM PUB_VOLUMES where volumeid=v; CURSOR mycur_split( val varchar2,splitMark varchar2) is select * from table(myutil_split(val,splitMark)); begin v_temp1 :=''; v_temp2 :=''; v_sql_WHERE := ''; v_tempBILLCODES := ''; V_CNT := 0; V_VOLUME_ID := 0;--册表的系统编号 v_sql := 'SELECT VOLUMEID,VOUCHTYPE,BILLCODES FROM PUB_VOLUMES WHERE 1=1 '; --dbms_output.put_line('p_BILL_NUM='||p_BILL_NUM); IF (p_CORP_NAME IS NOT NULL AND LENGTH(p_CORP_NAME) >0) THEN --公司名称 BEGIN v_sql_WHERE := v_sql_WHERE || ' AND CORPNAME LIKE ''%'; v_sql_WHERE := v_sql_WHERE || p_CORP_NAME; v_sql_WHERE := v_sql_WHERE || '%'''; --dbms_output.put_line(p_BILL_NUM); END; END IF; IF (p_YEAR IS NOT NULL AND LENGTH(p_YEAR) >0) THEN --会计年度 BEGIN v_sql_WHERE := v_sql_WHERE || ' AND YEAR = '''; v_sql_WHERE := v_sql_WHERE || p_YEAR; v_sql_WHERE := v_sql_WHERE || ''''; --dbms_output.put_line(p_BILL_NUM); END; END IF; IF (p_MONTH IS NOT NULL AND LENGTH(p_MONTH) >0) THEN --期间 BEGIN v_sql_WHERE := v_sql_WHERE || ' AND MONTH = '''; v_sql_WHERE := v_sql_WHERE || p_MONTH; v_sql_WHERE := v_sql_WHERE || ''''; --dbms_output.put_line(p_BILL_NUM); END; END IF; IF (p_VOL_TYPE_CODE IS NOT NULL AND LENGTH(p_VOL_TYPE_CODE) >0) THEN --凭证类别编码 BEGIN v_sql_WHERE := v_sql_WHERE || ' AND VOUCHTYPE = '''; v_sql_WHERE := v_sql_WHERE || p_VOL_TYPE_CODE; v_sql_WHERE := v_sql_WHERE || ''''; --dbms_output.put_line(p_BILL_NUM); END; END IF; IF (p_BILL_NUM IS NOT NULL AND LENGTH(p_BILL_NUM) >0) THEN --信息单号 BEGIN v_sql_WHERE := v_sql_WHERE || ' AND BILLCODES LIKE ''%'; v_sql_WHERE := v_sql_WHERE || p_BILL_NUM; v_sql_WHERE := v_sql_WHERE || '%'''; --dbms_output.put_line(p_BILL_NUM); END; END IF; IF (p_VOLUME_NUM IS NOT NULL AND LENGTH(p_VOLUME_NUM) >0) THEN --册号 BEGIN v_sql_WHERE := v_sql_WHERE || ' AND VOLUMENUM = '''; v_sql_WHERE := v_sql_WHERE || p_VOLUME_NUM; v_sql_WHERE := v_sql_WHERE || ''''; --dbms_output.put_line(p_BILL_NUM); END; END IF; p_QUERY_SQL := 'SQL4WHERE: ' || v_sql_WHERE; --dbms_output.put_line(v_sql || v_sql_WHERE || p_BILL_NUM); --OPEN mycur(v_WHERE); OPEN mycur FOR v_sql || v_sql_WHERE; LOOP--循环册记录 fetch mycur INTO V_VOLUME_ID,v_temp1,v_tempBILLCODES ; EXIT WHEN mycur%NOTFOUND; V_CNT := V_CNT + 1 ; --DBMS_OUTPUT.PUT_LINE( V_CNT || ':BILLCODES = ' || v_tempBILLCODES); OPEN mycur_split(v_tempBILLCODES,','); LOOP--循环生成每一个册的单据记录 fetch mycur_split INTO v_temp2 ; EXIT WHEN mycur_split%NOTFOUND; --DBMS_OUTPUT.PUT_LINE(' ' || v_temp2); --DBMS_OUTPUT.PUT_LINE(' p_BILL_NUM= ' || p_BILL_NUM||',v_temp2='||v_temp2); IF (p_BILL_NUM IS NULL OR p_BILL_NUM = TO_NUMBER(v_temp2)) THEN v_temp1 := 'INSERT INTO TEMP_VOLUMES_QUERY (SELECT '''|| v_temp2 || ''',A.* FROM PUB_VOLUMES A WHERE volumeid = ' || V_VOLUME_ID || ')';--写入到临时表 --dbms_output.put_line( 'v_temp1=' || v_temp1); execute immediate v_temp1; END IF; END LOOP; CLOSE mycur_split; END LOOP; CLOSE mycur; --开始输出结果 v_sql := 'SELECT CE.DCODE,CE.VOLUMEID,CE.CORPCODE,CE.CORPNAME,QU.AREANAME,CE.YEAR,CE.MONTH,CE.BILLCODES,CE.VOUCHTYPE,SHI.ROOMNAME, '; v_sql := v_sql || 'CE.VOLUMENUM,GUI.CABINETNUM,CE.CABINETLAYER FROM TEMP_VOLUMES_QUERY CE '; v_sql := v_sql || 'LEFT OUTER JOIN PUB_CORPS NAME ON CE.CORPCODE = NAME.CORPCODE ';--册所属公司(产生单据的公司) v_sql := v_sql || 'LEFT OUTER JOIN PUB_AREAS QU ON NAME.AREACODE=QU.AREACODE ';--册所属区域(产生单据的公司所在区域) v_sql := v_sql || 'LEFT OUTER JOIN PUB_CABINETS GUI ON CE.CABINETCODE=GUI.CABINETCODE ';--册所在档案柜(保存的位置) v_sql := v_sql || 'LEFT OUTER JOIN PUB_ARCHIVESROOMS SHI ON GUI.ROOMCODE = SHI.ROOMID ';--册(柜)所在档案室(保存的位置) v_sql := v_sql || 'WHERE (GUI.ISMAIL = 0 OR GUI.ISSIGN = 1) ';--尚未邮寄的或者已签收的 v_sql := v_sql || 'AND CE.ISBORROW = ''0'' ';--尚未借出去的 IF (p_AREA_CODES IS NOT NULL AND LENGTH(p_AREA_CODES) >0) THEN --如果需要限制册的所属区域 BEGIN v_sql := v_sql || 'AND QU.AREACODE IN ('|| p_AREA_CODES || ') '; END; END IF; p_QUERY_SQL := p_QUERY_SQL || ' SQL4RESULT: ' || v_sql;--返回 OPEN p_OutCursor FOR v_sql; SELECT COUNT(1) INTO V_CNT FROM TEMP_VOLUMES_QUERY; dbms_output.put_line(v_sql || ',V_CNT=' || V_CNT); dbms_output.put_line(V_CNT); delete from TEMP_VOLUMES_QUERY; COMMIT; end SPLIT_VOLUMES; end; /
三、结论
1、ON COMMIT DELETE ROWS 说明临时表是事务指定,每次提交后ORACLE将截断表(删除全部行)
2、ON COMMIT PRESERVE ROWS 说明临时表是会话指定,当中断会话时ORACLE将截断表。
3、临时表(无论会话级还是事务级)中的数据都是会话隔离的,不同session之间不会共享数据。
4、在存储中使用事务级临时表时,注意commit前删除掉本事务的数据,否则可能会出现数据不断增加的情况(原因尚未搞明白)。
5、 两种临时表的语法:
create global temporary table 临时表名 on commit preserve|delete rows;
用preserve时就是SESSION级的临时表,
用delete就是TRANSACTION级的临时表。
6、特性和性能(与普通表和视图的比较)
临时表只在当前连接内有效;
临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用;
数据处理比较复杂的时候时表快,反之视图快点;
在仅仅查询数据的时候建议用游标: open cursor for 'sql clause';
相关推荐
NULL 博文链接:https://fruitking.iteye.com/blog/1447333
NULL 博文链接:https://forlan.iteye.com/blog/2319341
CLOB变量需要dbms_lob.createtemporary,临时表空间中,建立临时LOB。 大数据量,返回值虽然是CLOB,依然会报错; CLOB变量需要dbms_lob.createtemporary,临时表空间中,建立临时LOB。 大数据量,返回值虽然是CLOB...
一个选查询后插入到一个临时表的oracle函数
oracle存储过程演示,临时表批量导入到正式表
应用于存在外键或其他约束时,需要跳过约束条件将数据导入表中,可使用该存储过程临时关闭或开启数据约束, 使用时可根据实际情况进行修改
在oracle 中,临时表与普通数据表一样只需要一次...相对于其它类型的表,临时表只有在用户实际向表中添加数据时,才会为其为配存储空间,并且分配的空间来自临时表空间(temp),这就避免了与永久的数据争用存储空间。
Oracle数据库创建临时表的过程以及和SQL Server临时表的不同点的对比的相关知识是本文我们主要要介绍的内容,接下来就让我们一起来了解一下这部分内容吧,希望能够对您有所帮助。...会话级临时表是指临时表中的
Oracle8i引入了分区表和对象表,Oracle8i引入了临时表,使表的功能更强大。视图是一个或多个表中数据的逻辑表达式。表可以看作有行和列的电子数据表,表是关系数据库中一种拥有数据的结构。用CREATE TABLE语句建立表...
oracle数据库同步perl脚本示例 其中有2个文件,一个是 perl同步脚本,另一个是存储过程;...大致思路是:perl脚本从数据库A同步数据至库B的临时表中,再通过存储过程将库B中的临时数据过滤导入正式数据当中。
5.6.4 查看临时表在数据库中的信息 86 5.6.5 临时表的应用场景 86 5.7 特殊的表dual 87 5.7.1 分析dual表 87 5.7.2 dual表的应用场景 87 5.7.3 修改dual表对查询结果的影响 88 5.8 本章实例 89 5.9 本章小结 ...
表空间概述 Oracle的表空间属于Oracle中的存储结构,是一种用于存储数据库...临时表空间:存储数据库的中间执行过程,如:保存order by数据库排序,分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空
PL/SQL中使用DML和DDL语言 PL/SQL中的异常 PL/SQL函数编写 第8章-游标,数据的缓存区 什么是游标 显示游标 隐式游标 第9章-视图,数据库中虚拟的表 什么是视图 视图的创建 操作视图数据的限制 视图的...
3、查询临时表记录放置到游标中。 4、删除临时表记录。 包的定义声明: 代码如下: CREATE OR REPLACE PACKAGE CHEN_TEST_PACKGE IStype cursor_type is ref cursor; /***************************************...
主要包括各种类型的表创建以及适用情形,如外部表、分区表、嵌套表、全局临时表等;完整性约束的管理;索引,包括B树索引、基于函数的索引、位图索引、反向索引、降序索引、压缩索引等的使用方法及其适用情形等。在...
实现的方式有多种,如编程存储过程返回游标,在存储过程中对两批数据进行比较...等等,当然返回差异数据的方式多种多样,既可以是游标,又可以临时表或其它方式。 本文主要论述利用ORACLE的MINUS函数和OVER函数,...
如临时表数据与排序段 回滚段-->用于事物回滚或闪回内存的撤销数据 表空间分类:系统表空间(system、sysaux),非系统表空间 一个表空间至少包含一个数据文件,一个数据文件只能属于一个表空间。
并在程序中创建存储过程 create or replace PACKAGE NCS_ICP_TJ AS /*lfx@ncs-cyber.com.cn*/ /* TODO 在此输入程序包声明 (类型, 异常错误, 方法等) */ /*根据备案主体ID拷贝通过表备案数据到备案临时表,...