当前位置:数据库 > Oracle >>

Oracle存储过程中临时表的使用技巧

Oracle存储过程中临时表的使用技巧
 
一、Oracle临时表知识   
在Oracle中,临时表分为SESSION、TRANSACTION两种,SESSION级的临时表数据在整个SESSION都存在,直到结束此次SESSION;而 TRANSACTION级的临时表数据在TRANACTION结束后消失,即COMMIT/ROLLBACK或结束SESSION都会清除 TRANACTION临时表数据。 
 
1) 会话级临时表 示例 
 
1创建
Sql代码  
create global temporary table temp_tbl(col_a varchar2(30))  
on commit preserve rows  
 
 2插入数据
Sql代码  
insert into temp_tbl values('test session table')  
 
 3提交
Sql代码  
commit;  
 
 4查询
Sql代码  
select *from temp_tbl  
 
 可以看到数据'test session table'记录还在。
结束SESSION,重新登录,再查询数据select *from temp_tbl,这时候记录已不存在,因为系统在结束SESSION时自动清除记录 。
 
2) 事务级临时表 示例 
 
1创建
Sql代码  
create global temporary table temp_tbl(col_a varchar2(30))  
on commit delete rows  
   
 
2插入数据
Sql代码  
insert into temp_tbl values('test transaction table')  
   
 
3提交
Sql代码  
commit ;  
 
4查询
Sql代码  
select *from temp_tbl  
 
 这时候可以看到刚才插入的记录'test transaction table'已不存在了,因为提交时已经晴空了数据库;同样,如果不提交而直接结束SESSION,重新登录记录也不存在 。
 
二、在Oracle存储中使用临时表的一个例子
描述:档案册借阅时,需要把册拆分成详细的单据,拆分依据是册表中的BILLCODES(若干个用逗号分割的单据号)字段,临时表用于保存拆分出来的单据信息。拆分结束后直接返回临时表的数据。
Sql代码  
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;  
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,