一、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提交
4查询
可以看到数据'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提交
4查询
这时候可以看到刚才插入的记录'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,
-
-
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_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 ';
-
-
-
-
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 || '%''';
-
-
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 || '''';
-
-
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 || '''';
-
-
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 || '''';
-
-
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 || '%''';
-
-
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 || '''';
-
-
END;
-
END IF;
-
p_QUERY_SQL := 'SQL4WHERE: ' || v_sql_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 ;
-
-
OPEN mycur_split(v_tempBILLCODES,',');
-
LOOP
-
fetch mycur_split INTO v_temp2 ;
-
EXIT WHEN mycur_split%NOTFOUND;
-
-
-
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 || ')';
-
-
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';