Oracle存储过程中使用临时表

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

@H_502_9@一、Oracle临时表知识  

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

1) 会话级临时表 示例

1创建

  1. createglobaltemporarytabletemp_tbl(col_avarchar2(30))
  2. oncommitpreserverows
@H_502_9@2插入数据

  1. insertintotemp_tblvalues('testsessiontable')
@H_502_9@

@H_502_9@3提交

  1. commit;
@H_502_9@4查询

  1. select*fromtemp_tbl
@H_502_9@

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

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

1创建

  1. createglobaltemporarytabletemp_tbl(col_avarchar2(30))
  2. oncommitdeleterows
@H_502_9@

@H_502_9@

@H_502_9@2插入数据

  1. insertintotemp_tblvalues('testtransactiontable')
@H_502_9@

@H_502_9@

@H_502_9@3提交

  1. commit;
@H_502_9@4查询

  1. select*fromtemp_tbl
@H_502_9@

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

@H_502_9@

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

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

  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
  18. v_sqlvarchar2(3000);
  19. v_sql_WHEREvarchar2(3000);
  20. v_temp1varchar2(300);
  21. v_temp2varchar2(300);
  22. v_tempBILLCODESvarchar2(3000);
  23. V_CNTNUMBER(10,0);
  24. V_VOLUME_IDNUMBER(10,0);
  25. mycurrefCursorType;
  26. --CURSORmycur(vvarchar2)is
  27. --SELECTVOUCHTYPE,BILLCODESFROMPUB_VOLUMESwherevolumeid=v;
  28. CURSORmycur_split(valvarchar2,splitMarkvarchar2)is
  29. select*fromtable(myutil_split(val,splitMark));
  30. begin
  31. v_temp1:='';
  32. v_temp2:='';
  33. v_sql_WHERE:='';
  34. v_tempBILLCODES:='';
  35. V_CNT:=0;
  36. V_VOLUME_ID:=0;--册表的系统编号
  37. v_sql:='SELECTVOLUMEID,VOUCHTYPE,BILLCODESFROMPUB_VOLUMESWHERE1=1';
  38. --dbms_output.put_line('p_BILL_NUM='||p_BILL_NUM);
  39. IF(p_CORP_NAMEISNOTNULLANDLENGTH(p_CORP_NAME)>0)THEN--公司名称
  40. BEGIN
  41. v_sql_WHERE:=v_sql_WHERE||'ANDCORPNAMELIKE''%';
  42. v_sql_WHERE:=v_sql_WHERE||p_CORP_NAME;
  43. v_sql_WHERE:=v_sql_WHERE||'%''';
  44. --dbms_output.put_line(p_BILL_NUM);
  45. END;
  46. ENDIF;
  47. IF(p_YEARISNOTNULLANDLENGTH(p_YEAR)>0)THEN--会计年度
  48. BEGIN
  49. v_sql_WHERE:=v_sql_WHERE||'ANDYEAR=''';
  50. v_sql_WHERE:=v_sql_WHERE||p_YEAR;
  51. v_sql_WHERE:=v_sql_WHERE||'''';
  52. --dbms_output.put_line(p_BILL_NUM);
  53. END;
  54. ENDIF;
  55. IF(p_MONTHISNOTNULLANDLENGTH(p_MONTH)>0)THEN--期间
  56. BEGIN
  57. v_sql_WHERE:=v_sql_WHERE||'ANDMONTH=''';
  58. v_sql_WHERE:=v_sql_WHERE||p_MONTH;
  59. v_sql_WHERE:=v_sql_WHERE||'''';
  60. --dbms_output.put_line(p_BILL_NUM);
  61. END;
  62. ENDIF;
  63. IF(p_VOL_TYPE_CODEISNOTNULLANDLENGTH(p_VOL_TYPE_CODE)>0)THEN--凭证类别编码
  64. BEGIN
  65. v_sql_WHERE:=v_sql_WHERE||'ANDVOUCHTYPE=''';
  66. v_sql_WHERE:=v_sql_WHERE||p_VOL_TYPE_CODE;
  67. v_sql_WHERE:=v_sql_WHERE||'''';
  68. --dbms_output.put_line(p_BILL_NUM);
  69. END;
  70. ENDIF;
  71. IF(p_BILL_NUMISNOTNULLANDLENGTH(p_BILL_NUM)>0)THEN--信息单号
  72. BEGIN
  73. v_sql_WHERE:=v_sql_WHERE||'ANDBILLCODESLIKE''%';
  74. v_sql_WHERE:=v_sql_WHERE||p_BILL_NUM;
  75. v_sql_WHERE:=v_sql_WHERE||'%''';
  76. --dbms_output.put_line(p_BILL_NUM);
  77. END;
  78. ENDIF;
  79. IF(p_VOLUME_NUMISNOTNULLANDLENGTH(p_VOLUME_NUM)>0)THEN--册号
  80. BEGIN
  81. v_sql_WHERE:=v_sql_WHERE||'ANDVOLUMENUM='color: blu
分享到:
参考知识库
Oracle知识库 @H_404_1202@3059关注 | @H_404_1202@252收录

猜你在找的Oracle相关文章