Oracle存储过程中使用临时表

一、Oracle临时表知识　 　

在Oracle中，临时表分为SESSION、TRANSACTION两种，SESSION级的临时表数据在整个SESSION都存在，直到结束此次SESSION；而 TRANSACTION级的临时表数据在TRANACTION结束后消失，即COMMIT/ROLLBACK或结束SESSION都会清除 TRANACTION临时表数据。 

1) 会话级临时表 示例 

1创建

Sql代码 

1. create global temporary table temp_tbl(col_a varchar2(30))  
2. on commit preserve rows  

 

 2插入数据

Sql代码 

1. insert into temp_tbl values('test session table')  

 

 3提交

Sql代码 

1. commit;  

 

 4查询

Sql代码 

1. select *from temp_tbl  

 

 可以看到数据'test session table'记录还在。
结束SESSION，重新登录，再查询数据select *from temp_tbl，这时候记录已不存在，因为系统在结束SESSION时自动清除记录 。

2) 事务级临时表 示例 

1创建

Sql代码 

1. create global temporary table temp_tbl(col_a varchar2(30))  
2. on commit delete rows  

   

 

2插入数据

Sql代码 

1. insert into temp_tbl values('test transaction table')  

   

 

3提交

Sql代码 

1. commit ;  

 

4查询

Sql代码 

1. select *from temp_tbl  

 

 这时候可以看到刚才插入的记录'test transaction table'已不存在了,因为提交时已经晴空了数据库；同样，如果不提交而直接结束SESSION，重新登录记录也不存在 。

 

二、在Oracle存储中使用临时表的一个例子

描述：档案册借阅时，需要把册拆分成详细的单据，拆分依据是册表中的BILLCODES（若干个用逗号分割的单据号）字段，临时表用于保存拆分出来的单据信息。拆分结束后直接返回临时表的数据。

Sql代码 

1. create or replace package AMS_PKG as  
2.    type REFCURSORTYPE is REF CURSOR;  
3.    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);  
4. end AMS_PKG;  
5. /  
6. CREATE OR REPLACE PACKAGE BODY "AMS_PKG" as  
7.   procedure SPLIT_VOLUMES(p_CORP_NAME      IN   varchar2,         --查询条件,公司名称  
8.                           p_YEAR           IN   varchar2,         --查询条件,会计年度  
9.                           p_MONTH          IN   varchar2,         --查询条件,期间  
10.                           p_VOL_TYPE_CODE  IN   varchar2,         --查询条件,凭证类别编码  
11.                           p_BILL_NUM       IN   varchar2,         --查询条件,信息单号  
12.                           p_VOLUME_NUM     IN   varchar2,         --查询条件,册号  
13.                           p_AREA_CODES     IN   varchar2,         --查询条件,所在区域编码（产生册的区域），逗号分割。  
14.                                                                   --形式如 '12C01','12201','12D01','12E01','12601'，存储过程中将使用in的方式进行过滤  
15.                           p_QUERY_SQL     out   varchar2,         --返回查询字符串  
16.                           p_OutCursor      out  refCursorType --返回值  
17.                           ) is  
18.   
19.     v_sql   varchar2(3000);  
20.     v_sql_WHERE   varchar2(3000);  
21.     v_temp1   varchar2(300);  
22.     v_temp2   varchar2(300);  
23.     v_tempBILLCODES varchar2(3000);  
24.     V_CNT NUMBER(10,0);  
25.     V_VOLUME_ID NUMBER(10,0);  
26.     mycur refCursorType;  
27.     --CURSOR mycur( v varchar2) is  
28.     --               SELECT VOUCHTYPE,BILLCODES FROM PUB_VOLUMES where volumeid=v;  
29.     CURSOR mycur_split( val varchar2,splitMark varchar2) is  
30.                    select * from table(myutil_split(val,splitMark));  
31.   begin  
32.     v_temp1    :='';  
33.     v_temp2    :='';  
34.     v_sql_WHERE := '';  
35.     v_tempBILLCODES  := '';  
36.     V_CNT            := 0;  
37.     V_VOLUME_ID            := 0;--册表的系统编号  
38.     v_sql := 'SELECT VOLUMEID,VOUCHTYPE,BILLCODES FROM PUB_VOLUMES WHERE 1=1 ';  
39.     --dbms_output.put_line('p_BILL_NUM='||p_BILL_NUM);  
40.       
41.      
42.     IF (p_CORP_NAME IS NOT NULL AND LENGTH(p_CORP_NAME) >0) THEN --公司名称  
43.        BEGIN  
44.          v_sql_WHERE := v_sql_WHERE || ' AND CORPNAME LIKE ''%';  
45.          v_sql_WHERE := v_sql_WHERE || p_CORP_NAME;  
46.          v_sql_WHERE := v_sql_WHERE || '%''';  
47.          --dbms_output.put_line(p_BILL_NUM);  
48.        END;  
49.     END IF;  
50.     IF (p_YEAR IS NOT NULL AND LENGTH(p_YEAR) >0)  THEN --会计年度  
51.        BEGIN  
52.          v_sql_WHERE := v_sql_WHERE || ' AND YEAR = ''';  
53.          v_sql_WHERE := v_sql_WHERE || p_YEAR;  
54.          v_sql_WHERE := v_sql_WHERE || '''';  
55.          --dbms_output.put_line(p_BILL_NUM);  
56.        END;  
57.     END IF;  
58.     IF (p_MONTH IS NOT NULL AND LENGTH(p_MONTH) >0)  THEN --期间  
59.        BEGIN  
60.          v_sql_WHERE := v_sql_WHERE || ' AND MONTH = ''';  
61.          v_sql_WHERE := v_sql_WHERE || p_MONTH;  
62.          v_sql_WHERE := v_sql_WHERE || '''';  
63.          --dbms_output.put_line(p_BILL_NUM);  
64.        END;  
65.     END IF;  
66.     IF (p_VOL_TYPE_CODE IS NOT NULL AND LENGTH(p_VOL_TYPE_CODE) >0) THEN --凭证类别编码  
67.        BEGIN  
68.          v_sql_WHERE := v_sql_WHERE || ' AND VOUCHTYPE = ''';  
69.          v_sql_WHERE := v_sql_WHERE || p_VOL_TYPE_CODE;  
70.          v_sql_WHERE := v_sql_WHERE || '''';  
71.          --dbms_output.put_line(p_BILL_NUM);  
72.        END;  
73.     END IF;  
74.     IF (p_BILL_NUM IS NOT NULL AND LENGTH(p_BILL_NUM) >0) THEN --信息单号  
75.        BEGIN  
76.          v_sql_WHERE := v_sql_WHERE || ' AND BILLCODES LIKE ''%';  
77.          v_sql_WHERE := v_sql_WHERE || p_BILL_NUM;  
78.          v_sql_WHERE := v_sql_WHERE || '%''';  
79.          --dbms_output.put_line(p_BILL_NUM);  
80.        END;  
81.     END IF;  
82.     IF (p_VOLUME_NUM IS NOT NULL AND LENGTH(p_VOLUME_NUM) >0) THEN --册号  
83.        BEGIN  
84.          v_sql_WHERE := v_sql_WHERE || ' AND VOLUMENUM = '