一次遷移思考的記錄--bulk_collect的limit用法

今天在工作中遇到了一個比較頭疼的問題：在進行一個幾萬條記錄的表遷移時，該表中有幾百條含有單引號的記錄。

此表的處理過程是根據分析的深入而完善的。
1.最初，我們直接使用insert into…… select ……的句法直接將內容放入目標表中
歸納為:insert into dest_table select * from orig_table;

2.後來發現由於目標表中某些列設定的非空約束而使某些記錄不能成功匯入，因此引入了例外表，將不符合條件的內容放入"error_表名"格式的表中，使其不影響整個遷移
歸納為:insert into dest_table select * from orig_table log errors into error_dest_table ('insert'||to_char(sysdate,'yyyymmdd hh24:mi:ss')) reject limit unlimited;

3.源表和目標表的資料量都達到了八萬多，重複進行fetch在效能上顯然是不夠優化的，因此想到在動態語句中使用bulk collect，將得到的查詢結果一次性插入遊標中。但發現有些列存在單引號，而使用例外表只能截獲不符合約束的列，將多出單引號的列當做是缺少逗號來處理了，執行到這些地方就報錯。--此想法暫告擱淺,但對於不含單引號的普通資料還是可行的
歸納為：execute immediate 'select * from orig_table ' bulk collect into cursor_name;
 在迴圈中將遊標中的資料逐條讀取並insert到dest_table
 'insert into dest_table values ('||''''||cursor_name(v_count).column_name)'||''''||
 ' log errors into error_dest_table(to_char(sysdate,'||''''||'yyyymmdd hh24:mi:ss'||''''||'))'||
     ' reject limit unlimited';

4.學習pl/sql時發現可以採用using子句，將資料儲存之後，通過":引數名"進行呼叫，這種方式節省了變數的定義和引用量，最重要的是由於其不涉及到語句的拼湊，單引號的問題可以解決。
歸納為：execute immediate vc_select_statement bulk collect into v_column1，v_column2; 
 for i in 1..v_column1.count loop
   insert_statement:='insert into dest_table values(:var_column1,:var_column2)'||
           ' log errors into error_dest_table(to_char(sysdate,'||''''||'yyyymmdd hh24:mi:ss'||''''||'))'||
         ' reject limit unlimited';
   exectue immediate insert_statement using v_column1,v_column2;
 end loop;

5.今天，看到bulk collect的limit子句，想著應該將這八萬多條資料分批處理比較安全，做實驗試試！
首先想到在方案四的基礎上修改，但狡猾的execute immediate裡似乎不能用limit子句。或許是execute immediate太方便了，需要點兒侷限性吧。

這個問題至少應該是有方法處理的，首先想到的方法是在儲存過程中將源表資料中單引號替換為其他字元（必須保證當前表中沒有這個字元，如"~"，或者較長的字元，但不要超過定義的列長），然後在方案三中方法的基礎上對bulk collect使用limit子句進行限制。完成後將資料中的替換字元（"~"）替換為單引號。
此方法可以保證正確性，但時間消耗上比較大。

我們實驗一下：
（1）環境構造
SQL> conn scott/tiger@admin
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott

SQL> create table tian as select table_name,column_name from dba_tab_columns;

Table created

SQL> select count(1) from tian;

  COUNT(1)
----------
     54203

SQL> select count(1) from tian where column_name like '%'||chr(39)||'%';

  COUNT(1)
----------
         0
//當前表中是沒有含單引號的column_name的，我們將下劃線替換為單引號

SQL> insert into tian select table_name,replace(column_name,'_',chr(39)) from tian;

54203 rows inserted

SQL> commit;

Commit complete

SQL> select count(1) from tian where column_name like '%'||chr(39)||'%';

  COUNT(1)
----------
     30208

SQL> select count(1) from tian ;

  COUNT(1)
----------
    108406

（2）儲存過程編寫
create or replace procedure mig_tian
is
type cursor_typ is ref cursor;
cursor_tian cursor_typ;--建立遊標
type tian_typ is table of tian%rowtype;
tian_row tian_typ;--建立行型別
v_statement varchar2(300);--儲存語句
v_count number(6);--計數用
begin
update tian set column_name=replace(column_name,chr(39),'~');--將單引號替換為~，這個語句挺簡潔的 值得推薦！
 commit;
 open cursor_tian for 'select * from tian';
 loop --迴圈插入資料，每次最多5000條
   fetch cursor_tian bulk collect into tian_row limit 5000;
   exit when tian_row.count=0;
   v_count:=tian_row.count;
   while (v_count>0)
   loop
 v_statement:='insert into dest_table values ('
 ||''''||tian_row(v_count).table_name||''''||','
 ||''''||tian_row(v_count).column_name||''''||')'||
 ' log errors into error_dest_table(to_char(sysdate,'||''''||'yyyymmdd hh24:mi:ss'||''''||'))'||
     ' reject limit unlimited';
 execute immediate v_statement;
 v_count:=v_count-1;
   end loop;
   commit;
 end loop;
update tian set column_name=replace(column_name,'~',chr(39));--將單引號替換回來
 commit;
 exception when others then
 dbms_output.put_line(sqlerrm);
end;

在後來的實驗中，嘗試將以上方法與方案四結合，也可以達到目的了。由於涉及生產環境資訊，此部分略去。思想是一樣的，通過limit指定每次插入遊標的資料量，再通過迴圈將所有資料都執行一遍。利用using子句直接獲取變數，不會產生單引號出錯的麻煩。看來能oracle自己做的事，最好還是給它做吧。

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