多行資料的批處理之bulk collect

多行資料的批處理之bulk collect

在寫pl/sql的時候，很多時候都會用比較經典的模式，定義一個遊標cursor，然後迴圈從遊標中取值進行處理。
 類似下面的格式
declare
 cursor xxxx is xxxxx;
 begin
 loop cur in xxxxx loop
 xxxxx
 end loop;
 end;
 /

如果cursor中包含的資料太多的時候，可能會有效能問題，效能的考慮主要在於pl/sql引擎和sql引擎的切換，和編程中的上下文環境是類似的。
 這個時候可以考慮採用bulk collect 的方式直接一次性讀取資料島緩衝然後從緩衝中進一步處理。
 這種方式可以打個比方比較形象，比如 你帶著一個新人去完成一個任務，可能一天他要問你100個問題，你是希望他每隔幾分鐘想到了就問你呢，還是讓他自己把問題積累起來，專門設定一個時間來集中回答呢。可能你在忙另外一個事情，他問你一個問題，這個時候就會有上下文環境的切換，等你回答了之後，繼續工作的時候，又一個問題來了，這時候又得進行一次切換。。。。
 比方說我們設定一個表test，希望把test裡面的資料選擇性的插入到test_all中去
 實現的原始Pl/sql如下：
declare
 cursor test_cursors is select object_id,object_name from test;
 begin
 for test_cursor in test_cursors loop
 dbms_output.put_line('object_id: '||test_cursor.object_id);
 insert into test_all values(test_cursor.object_id,test_cursor.object_name);
 end loop;
 commit;
 end;
 /

如果採用bulk collect 方式，就會是如下的方式：
 declare
  type id_t is table of test.object_id%type;
  type name_t is table of test.object_name%type;
  object_id id_t;
  object_name name_t;
  cursor test_cursors is select object_id,object_name from test;
  begin
  open test_cursors;
 fetch test_cursors bulk collect into object_id,object_name;
 close test_cursors;
  for i in object_id.FIRST .. object_id.LAST loop
  dbms_output.put_line('object_id: '||object_id(i));
  insert into test_all values(object_id(i),object_name(i));
  end loop;
  commit;
  end;
  /

或者採用隱式遊標的方式：
 declare
  type id_t is table of test.object_id%type;
  type name_t is table of test.object_name%type;
  object_id id_t;
  object_name name_t;
  begin
  select object_id,object_name bulk collect into object_id,object_name from test where rownum<20;
  for i in object_id.FIRST .. object_id.LAST loop
  dbms_output.put_line('object_id: '||object_id(i));
  insert into test_all values(object_id(i),object_name(i));
  end loop;
  commit;
  end;
  /