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

于 2013-06-17 21:37:00 发布
阅读量167 收藏
点赞数
文章标签： 数据库
本文介绍了Oracle中的临时表，包括SESSION级和TRANSACTION级临时表的区别，并通过实例展示了如何创建及使用这两种类型的临时表。此外，还提供了一个在存储过程中利用临时表处理档案册借阅业务的示例。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一、Oracle临时表知识　　

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

1) 会话级临时表 示例

1创建

Sql代码 收藏代码
  1. createglobaltemporarytabletemp_tbl(col_avarchar2(30))
  2. oncommitpreserverows

2插入数据

Sql代码 收藏代码
  1. insertintotemp_tblvalues('testsessiontable')

3提交

Sql代码 收藏代码
  1. commit;

4查询

Sql代码 收藏代码
  1. select*fromtemp_tbl

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


2) 事务级临时表 示例

1创建

Sql代码 收藏代码
  1. createglobaltemporarytabletemp_tbl(col_avarchar2(30))
  2. oncommitdeleterows

2插入数据

Sql代码 收藏代码
  1. insertintotemp_tblvalues('testtransactiontable')

3提交

Sql代码 收藏代码
  1. commit;

4查询

Sql代码 收藏代码
  1. select*fromtemp_tbl

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

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

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

Sql代码 收藏代码
  1. createorreplacepackageAMS_PKGas
  2. typeREFCURSORTYPEisREFCURSOR;
  3. 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);
  4. endAMS_PKG;
  5. /
  6. CREATEORREPLACEPACKAGEBODY"AMS_PKG"as
  7. procedureSPLIT_VOLUMES(p_CORP_NAMEINvarchar2,--查询条件,公司名称
  8. p_YEARINvarchar2,--查询条件,会计年度
  9. p_MONTHINvarchar2,--查询条件,期间
  10. p_VOL_TYPE_CODEINvarchar2,--查询条件,凭证类别编码
  11. p_BILL_NUMINvarchar2,--查询条件,信息单号
  12. p_VOLUME_NUMINvarchar2,--查询条件,册号
  13. p_AREA_CODESINvarchar2,--查询条件,所在区域编码（产生册的区域），逗号分割。
  14. --形式如'12C01','12201','12D01','12E01','12601'，存储过程中将使用in的方式进行过滤
  15. p_QUERY_SQLoutvarchar2,--返回查询字符串
  16. p_OutCursoroutrefCursorType--返回值
  17. )is
  19. v_sqlvarchar2(3000);
  20. v_sql_WHEREvarchar2(3000);
  21. v_temp1varchar2(300);
  22. v_temp2varchar2(300);
  23. v_tempBILLCODESvarchar2(3000);
  24. V_CNTNUMBER(10,0);
  25. V_VOLUME_IDNUMBER(10,0);
  26. mycurrefCursorType;
  27. --CURSORmycur(vvarchar2)is
  28. --SELECTVOUCHTYPE,BILLCODESFROMPUB_VOLUMESwherevolumeid=v;
  29. CURSORmycur_split(valvarchar2,splitMarkvarchar2)is
  30. select*fromtable(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:='SELECTVOLUMEID,VOUCHTYPE,BILLCODESFROMPUB_VOLUMESWHERE1=1';
  39. --dbms_output.put_line('p_BILL_NUM='||p_BILL_NUM);
  42. IF(p_CORP_NAMEISNOTNULLANDLENGTH(p_CORP_NAME)>0)THEN--公司名称
  43. BEGIN
  44. v_sql_WHERE:=v_sql_WHERE||'ANDCORPNAMELIKE''%';
  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. ENDIF;
  50. IF(p_YEARISNOTNULLANDLENGTH(p_YEAR)>0)THEN--会计年度
  51. BEGIN
  52. v_sql_WHERE:=v_sql_WHERE||'ANDYEAR=''';
  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. ENDIF;
  58. IF(p_MONTHISNOTNULLANDLENGTH(p_MONTH)>0)THEN--期间
  59. BEGIN
  60. v_sql_WHERE:=v_sql_WHERE||'ANDMONTH=''';
  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. ENDIF;
  66. IF(p_VOL_TYPE_CODEISNOTNULLANDLENGTH(p_VOL_TYPE_CODE)>0)THEN--凭证类别编码
  67. BEGIN
  68. v_sql_WHERE:=v_sql_WHERE||'ANDVOUCHTYPE=''';
  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. ENDIF;
  74. IF(p_BILL_NUMISNOTNULLANDLENGTH(p_BILL_NUM)>0)THEN--信息单号
  75. BEGIN
  76. v_sql_WHERE:=v_sql_WHERE||'ANDBILLCODESLIKE''%';
  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. ENDIF;
  82. IF(p_VOLUME_NUMISNOTNULLANDLENGTH(p_VOLUME_NUM)>0)THEN--册号
  83. BEGIN
  84. v_sql_WHERE:=v_sql_WHERE||'ANDVOLUMENUM=''';
  85. v_sql_WHERE:=v_sql_WHERE||p_VOLUME_NUM;
  86. v_sql_WHERE:=v_sql_WHERE||'''';
  87. --dbms_output.put_line(p_BILL_NUM);
  88. END;
  89. ENDIF;
  90. p_QUERY_SQL:='SQL4WHERE:'||v_sql_WHERE;
  92. --dbms_output.put_line(v_sql||v_sql_WHERE||p_BILL_NUM);
  93. --OPENmycur(v_WHERE);
  94. OPENmycurFORv_sql||v_sql_WHERE;
  96. LOOP--循环册记录
  97. fetchmycurINTOV_VOLUME_ID,v_temp1,v_tempBILLCODES;
  98. EXITWHENmycur%NOTFOUND;
  99. V_CNT:=V_CNT+1;
  100. --DBMS_OUTPUT.PUT_LINE(V_CNT||':BILLCODES='||v_tempBILLCODES);
  101. OPENmycur_split(v_tempBILLCODES,',');
  102. LOOP--循环生成每一个册的单据记录
  103. fetchmycur_splitINTOv_temp2;
  104. EXITWHENmycur_split%NOTFOUND;
  105. --DBMS_OUTPUT.PUT_LINE(''||v_temp2);
  106. --DBMS_OUTPUT.PUT_LINE('p_BILL_NUM='||p_BILL_NUM||',v_temp2='||v_temp2);
  107. IF(p_BILL_NUMISNULLORp_BILL_NUM=TO_NUMBER(v_temp2))THEN
  108. v_temp1:='INSERTINTOTEMP_VOLUMES_QUERY(SELECT'''||v_temp2||''',A.*FROMPUB_VOLUMESAWHEREvolumeid='||V_VOLUME_ID||')';--写入到临时表
  109. --dbms_output.put_line('v_temp1='||v_temp1);
  110. executeimmediatev_temp1;
  111. ENDIF;
  112. ENDLOOP;
  113. CLOSEmycur_split;
  115. ENDLOOP;
  117. CLOSEmycur;
  121. --开始输出结果
  122. v_sql:='SELECTCE.DCODE,CE.VOLUMEID,CE.CORPCODE,CE.CORPNAME,QU.AREANAME,CE.YEAR,CE.MONTH,CE.BILLCODES,CE.VOUCHTYPE,SHI.ROOMNAME,';
  123. v_sql:=v_sql||'CE.VOLUMENUM,GUI.CABINETNUM,CE.CABINETLAYERFROMTEMP_VOLUMES_QUERYCE';
  124. v_sql:=v_sql||'LEFTOUTERJOINPUB_CORPSNAMEONCE.CORPCODE=NAME.CORPCODE';--册所属公司（产生单据的公司）
  125. v_sql:=v_sql||'LEFTOUTERJOINPUB_AREASQUONNAME.AREACODE=QU.AREACODE';--册所属区域（产生单据的公司所在区域）
  126. v_sql:=v_sql||'LEFTOUTERJOINPUB_CABINETSGUIONCE.CABINETCODE=GUI.CABINETCODE';--册所在档案柜（保存的位置）
  127. v_sql:=v_sql||'LEFTOUTERJOINPUB_ARCHIVESROOMSSHIONGUI.ROOMCODE=SHI.ROOMID';--册（柜）所在档案室（保存的位置）
  128. v_sql:=v_sql||'WHERE(GUI.ISMAIL=0ORGUI.ISSIGN=1)';--尚未邮寄的或者已签收的
  129. v_sql:=v_sql||'ANDCE.ISBORROW=''0''';--尚未借出去的
  130. IF(p_AREA_CODESISNOTNULLANDLENGTH(p_AREA_CODES)>0)THEN--如果需要限制册的所属区域
  131. BEGIN
  132. v_sql:=v_sql||'ANDQU.AREACODEIN('||p_AREA_CODES||')';
  133. END;
  134. ENDIF;
  136. p_QUERY_SQL:=p_QUERY_SQL||'SQL4RESULT:'||v_sql;--返回
  138. OPENp_OutCursorFORv_sql;
  139. SELECTCOUNT(1)INTOV_CNTFROMTEMP_VOLUMES_QUERY;
  140. dbms_output.put_line(v_sql||',V_CNT='||V_CNT);
  141. dbms_output.put_line(V_CNT);
  142. deletefromTEMP_VOLUMES_QUERY;
  143. COMMIT;
  145. endSPLIT_VOLUMES;
  147. end;
  148. /


三、结论
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';

iteye_11790
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值