@H_502_9@一、Oracle临时表知识 @H_502_9@在Oracle中,临时表分为SESSION、TRANSACTION两种,SESSION级的临时表数据在整个SESSION都存在,直到结束此次SESSION;而 TRANSACTION级的临时表数据在TRANACTION结束后消失,即COMMIT/ROLLBACK或结束SESSION都会清除 TRANACTION临时表数据。
1) 会话级临时表 示例
1创建
- createglobaltemporarytabletemp_tbl(col_avarchar2(30))
- oncommitpreserverows
@H_
502_9@2插入数据
- insertintotemp_tblvalues('testsessiontable')
@H_
502_9@ @H_502_9@3提交
- commit;
@H_
502_9@4查询
- select*fromtemp_tbl
@H_
502_9@ @H_502_9@可以看到数据'test session table'记录还在。
结束SESSION,重新登录,再查询数据select *from temp_tbl,这时候记录已不存在,因为系统在结束SESSION时自动清除记录 。 @H_502_9@
2) 事务级临时表 示例
1创建
- createglobaltemporarytabletemp_tbl(col_avarchar2(30))
- oncommitdeleterows
@H_
502_9@ @H_502_9@ @H_502_9@2插入数据
- insertintotemp_tblvalues('testtransactiontable')
@H_
502_9@ @H_502_9@ @H_502_9@3提交
- commit;
@H_
502_9@4查询
- select*fromtemp_tbl
@H_
502_9@ @H_502_9@这时候可以看到刚才插入的记录'test transaction table'已不存在了,因为提交时已经晴空了数据库;同样,如果不提交而直接结束SESSION,重新登录记录也不存在 。 @H_502_9@ @H_502_9@二、在Oracle存储中使用临时表的一个例子 @H_502_9@描述:档案册借阅时,需要把册拆分成详细的单据,拆分依据是册表中的BILLCODES(若干个用逗号分割的单据号)字段,临时表用于保存拆分出来的单据信息。拆分结束后直接返回临时表的数据。
- 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
- createglobaltemporarytabletemp_tbl(col_avarchar2(30))
- oncommitpreserverows
- insertintotemp_tblvalues('testsessiontable')
- commit;
- select*fromtemp_tbl
- createglobaltemporarytabletemp_tbl(col_avarchar2(30))
- oncommitdeleterows
- insertintotemp_tblvalues('testtransactiontable')
- commit;
- select*fromtemp_tbl
- 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
参考知识库