Oracle事務臨時表的一個隱藏問題

regonly1發表於2010-12-25
Oracle臨時分為兩類,
一類是事務臨時表,以on commit delete rows指示。按照指示含義,我們可以知道這類臨時表資料是在commit之後就清除資料的。
一類是session臨時表,以on commit preserve rows指示。同樣,按照含義,表明這類表在commit之後還是保留資料的(直到session結束後會被自動清除)。
為什麼要提Oracle的這兩類臨時表呢?他們的屬性就這麼點,還有啥好提的?呵呵。看下面例子:
--先建立一個事務臨時表:
create global temporary table xxx(x int) on commit delete rows;
--執行如下語句:
SQL> declare
  2      cursor cx is
  3      select * from xxx;
  4      type typ_cx is table of cx%rowtype index by pls_integer;
  5      v_cx typ_cx;
  6  begin
  7      insert into xxx
  8      select rownum from dual connect by rownum <= 20000;
  9      open cx;
 10      loop
 11          fetch cx bulk collect into v_cx limit 3000;
 12          exit when v_cx.count = 0;
 13          dbms_output.put_line('Count: ' || v_cx.count);
 14          commit;
 15      end loop
 16      commit;
 17  end;
 18  /
發生報錯:
ORA-01410: invalid ROWID
ORA-06512: at line 12
覺得正常嗎?
現在刪除該臨時表,建立一個session級的臨時表,其他都不變:
--刪除臨時表
drop table xxx
--建立session臨時表
create global temporary table xxx(x int) on commit preserve rows;
--執行之前那個過程
SQL> declare
  2      cursor cx is
  3      select * from xxx;
  4      type typ_cx is table of cx%rowtype index by pls_integer;
  5      v_cx typ_cx;
  6  begin
  7      insert into xxx
  8      select rownum from dual connect by rownum <= 20000;
  9      open cx;
 10      loop
 11          fetch cx bulk collect into v_cx limit 3000;
 12          exit when v_cx.count = 0;
 13          dbms_output.put_line('Count: ' || v_cx.count);
 14          commit;
 15      end loop
 16      commit;
 17  end;
 18  /
 
PL/SQL procedure successfully completed
執行成功!!這是為什麼?
看程式碼知道,這個過程就是將20000個數字的資料插入到臨時表,然後開啟臨時表的遊標,將這些數字以每批3000分批從遊標中取出並列印對應批次的數量。列印後,執行一個commit命令。問題就出在這個commit命令上,因為commit表示一個提交,而事務臨時表在commit之後,資料隨之被清空,導致下次fetch遊標中的資料時無法取到,從而發生第一個過程執行時的錯誤。而session臨時表就不會有這個問題。
另外,需要注意的是,session臨時表是在整個session期間有效的,因此在同個session期間如果執行了多次,會重複的插入資料到該表中。開發的時候要注意這個現象,否則很容易出現累加效應。


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12932950/viewspace-682606/,如需轉載,請註明出處,否則將追究法律責任。

相關文章