Oracle存储过程中使用临时表

前端之家收集整理的这篇文章主要介绍了Oracle存储过程中使用临时表前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

一、Oracle临时表知识 @H_403_12@ 

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

1) 会话级临时表 示例@H_403_12@

1创建

@H_502_30@
  • createglobaltemporarytabletemp_tbl(col_avarchar2(30))
  • oncommitpreserverows
  • 2插入数据

    @H_502_30@
  • insertintotemp_tblvalues('testsessiontable')
  • 3提交

    @H_502_30@
  • commit;
  • 4查询

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


    2) 事务级临时表 示例@H_403_12@

    1创建

    @H_502_30@
  • createglobaltemporarytabletemp_tbl(col_avarchar2(30))
  • oncommitdeleterows
  • 2插入数据

    @H_502_30@
  • insertintotemp_tblvalues('testtransactiontable')
  • 3提交

    @H_502_30@
  • commit;
  • 4查询

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

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

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

    @H_502_30@
  • createorreplacepackageAMS_PKGas
  • typeREFCURSORTYPEisREFCURSOR;
  • procedureSPLIT_VOLUMES(P_CORP_NAMEINvarchar2,P_YEARINvarchar2,P_MONTHINvarchar2,P_VOL_TYPE_CODEINvarchar2,P_BILL_NUMINvarchar2,P_VOLUME_NUMINvarchar2,P_AREA_CODESINvarchar2,P_QUERY_sqloutvarchar2,P_OUTCURSORoutrefCursorType);
  • endAMS_PKG;
  • /
  • CREATEORREPLACEPACKAGEBODY"AMS_PKG"as
  • procedureSPLIT_VOLUMES(p_CORP_NAMEINvarchar2,--查询条件,公司名称
  • p_YEARINvarchar2,会计年度
  • p_MONTHINvarchar2,期间
  • p_VOL_TYPE_CODEINvarchar2,凭证类别编码
  • p_BILL_NUMINvarchar2,信息单号
  • p_VOLUME_NUMINvarchar2,册号
  • p_AREA_CODESINvarchar2,所在区域编码(产生册的区域),逗号分割。
  • --形式如'12C01','12201','12D01','12E01','12601',存储过程中将使用in的方式进行过滤
  • p_QUERY_sqloutvarchar2,--返回查询字符串
  • p_OutCursoroutrefCursorType--返回值
  • )is
  • v_sqlvarchar2(3000);
  • v_sql_WHEREvarchar2(3000);
  • v_temp1varchar2(300);
  • v_temp2varchar2(300);
  • v_tempBILLCODESvarchar2(3000);
  • V_CNTNUMBER(10,0);
  • V_VOLUME_IDNUMBER(10,0);
  • mycurrefCursorType;
  • --CURSORmycur(vvarchar2)is
  • --SELECTVOUCHTYPE,BILLCODESFROMPUB_VOLUMESwherevolumeid=v;
  • CURSORmycur_split(valvarchar2,splitMarkvarchar2)is
  • select*fromtable(myutil_split(val,splitMark));
  • begin
  • v_temp1:='';
  • v_temp2:='';
  • v_sql_WHERE:='';
  • v_tempBILLCODES:='';
  • V_CNT:=0;
  • V_VOLUME_ID:=0;--册表的系统编号
  • v_sql:='SELECTVOLUMEID,VOUCHTYPE,BILLCODESFROMPUB_VOLUMESWHERE1=1';
  • --dbms_output.put_line('p_BILL_NUM='||p_BILL_NUM);
  • IF(p_CORP_NAMEISNOTNULLANDLENGTH(p_CORP_NAME)>0)THEN--公司名称
  • BEGIN
  • v_sql_WHERE:=v_sql_WHERE||'ANDCORPNAMELIKE''%';
  • v_sql_WHERE:=v_sql_WHERE||p_CORP_NAME;
  • v_sql_WHERE:=v_sql_WHERE||'%''';
  • --dbms_output.put_line(p_BILL_NUM);
  • END;
  • ENDIF;
  • IF(p_YEARISNOTNULLANDLENGTH(p_YEAR)>0)THEN--会计年度
  • BEGIN
  • v_sql_WHERE:=v_sql_WHERE||'ANDYEAR=''';
  • v_sql_WHERE:=v_sql_WHERE||p_YEAR;
  • v_sql_WHERE:=v_sql_WHERE||'''';
  • --dbms_output.put_line(p_BILL_NUM);
  • END;
  • ENDIF;
  • IF(p_MONTHISNOTNULLANDLENGTH(p_MONTH)>0)THEN--期间
  • BEGIN
  • v_sql_WHERE:=v_sql_WHERE||'ANDMONTH=''';
  • v_sql_WHERE:=v_sql_WHERE||p_MONTH;
  • v_sql_WHERE:=v_sql_WHERE||'''';
  • --dbms_output.put_line(p_BILL_NUM);
  • END;
  • ENDIF;
  • IF(p_VOL_TYPE_CODEISNOTNULLANDLENGTH(p_VOL_TYPE_CODE)>0)THEN--凭证类别编码
  • BEGIN
  • v_sql_WHERE:=v_sql_WHERE||'ANDVOUCHTYPE=''';
  • v_sql_WHERE:=v_sql_WHERE||p_VOL_TYPE_CODE;
  • v_sql_WHERE:=v_sql_WHERE||'''';
  • --dbms_output.put_line(p_BILL_NUM);
  • END;
  • ENDIF;
  • IF(p_BILL_NUMISNOTNULLANDLENGTH(p_BILL_NUM)>0)THEN--信息单号
  • BEGIN
  • v_sql_WHERE:=v_sql_WHERE||'ANDBILLCODESLIKE''%';
  • v_sql_WHERE:=v_sql_WHERE||p_BILL_NUM;
  • v_sql_WHERE:=v_sql_WHERE||'%''';
  • --dbms_output.put_line(p_BILL_NUM);
  • END;
  • ENDIF;
  • IF(p_VOLUME_NUMISNOTNULLANDLENGTH(p_VOLUME_NUM)>0)THEN--册号
  • BEGIN
  • v_sql_WHERE:=v_sql_WHERE||'ANDVOLUMENUM='color: blu
  • 分享到:
    @H_403_1195@参考知识库

    Oracle知识库 3059关注 | 252收录
    @H_403_1195@评论

    猜你在找的Oracle相关文章