根據條件動態更新不同表的資料

前天運維問了一個sql，研究了半天發現自己的方法不是最優。以下模擬一下：


需求：
根據條件更新不同的表，將欄位tl_date資料後面新增"_bak"字串。


1.準備資料


drop table test_tl_a;
drop table test_tl_b;
drop table test_tl_c;
create table test_tl_a as select 'A'||to_char(to_date('20151001','yyyymmdd')+level-1,'yyyymmdd') tl_date from dual connect by level<5;
create table test_tl_b as select 'B'||to_char(to_date('20151001','yyyymmdd')+level-1,'yyyymmdd') tl_date from dual connect by level<5;
create table test_tl_c as select 'C'||to_char(to_date('20151001','yyyymmdd')+level-1,'yyyymmdd') tl_date from dual connect by level<5;
alter table TEST_TL_A modify TL_DATE VARCHAR2(20);
alter table TEST_TL_B modify TL_DATE VARCHAR2(20);
alter table TEST_TL_C modify TL_DATE VARCHAR2(20);


SQL> SELECT * FROM TEST_TL_A;
 
TL_DATE
--------------------
A20151001
A20151002
A20151003
A20151004
 
SQL> SELECT * FROM TEST_TL_B;
 
TL_DATE
--------------------
B20151001
B20151002
B20151003
B20151004


SQL> SELECT * FROM TEST_TL_C;
 
TL_DATE
--------------------
C20151001
C20151002
C20151003
C20151004
 
運維給出的思路（方法1）：
create or replace procedure test_tl 
is
  tab_name_tmp varchar2(40);
  date_nu      number;
  v_sql varchar2(500);
begin
  select to_number(to_char(sysdate,'ss'))
    into date_nu
    from dual;     --這裡作為更新不同表的依據，根據時間不同分別更新test_tl_a\test_tl_b\test_tl_c
  if date_nu <= 20 then
    tab_name_tmp := 'test_tl_a';
  elsif date_nu >= 21 and date_nu <= 40 then
    tab_name_tmp := 'test_tl_b';
  else
    tab_name_tmp := 'test_tl_c';
  end if;
  dbms_output.put_line(tab_name_tmp);  
  v_sql := 'update ' || tab_name_tmp || ' set tl_date=tl_date'||'||''_bak'''
  ||' where mod(to_number(substr(tl_date,-1,1)),2) =0';  --這裡是拼接的語句，使用大量單引號
  dbms_output.put_line(v_sql);
  execute immediate v_sql;
  commit;
end;


下面是執行結果：
SQL> set time on
9:49:09 SQL> set timing on
9:49:12 SQL> set serveroutput on
10:11:53 SQL> exec test_tl;
 
test_tl_c
update test_tl_c set tl_date=tl_date||'_bak' where mod(to_number(substr(tl_date,-1,1)),2) =0
 
PL/SQL procedure successfully completed
 
Executed in 0.047 seconds
 
10:11:57 SQL> SELECT * FROM TEST_TL_C;
 
TL_DATE
--------------------
C20151001
C20151002_bak
C20151003
C20151004_bak
 
Executed in 0.031 seconds


以上儲存過程拼接的語句是：
update test_tl_c set tl_date=tl_date||'_bak' where mod(to_number(substr(tl_date,-1,1)),2) =0
實際的語句比上面例子複雜，因此單引號看起來比較亂。


我提供了一個使用繫結變數逐條更新的方法，如下（方法2）：
create or replace procedure test_tl2 
is
  tab_name_tmp varchar2(40);
  date_nu      number;
  v_sql varchar2(500);
  type typ_tl_date is table of test_tl_a.tl_date%type;--宣告型別
  v_tl_date typ_tl_date;--型別變數
  v_replace varchar2(20);
  v_sql_tab varchar2(500);
begin
  select to_number(to_char(sysdate,'ss'))
    into date_nu
    from dual;
  if date_nu <= 20 then
    tab_name_tmp := 'test_tl_a';
  elsif date_nu >= 21 and date_nu <= 40 then
    tab_name_tmp := 'test_tl_b';
  else
    tab_name_tmp := 'test_tl_c';
  end if;
  dbms_output.put_line(tab_name_tmp);  
  
  --根據表名拼接語句，將對應資料插入
  v_sql_tab:='select tl_date from '||tab_name_tmp;
  execute immediate v_Sql_tab bulk collect into v_tl_date;
  
  --迴圈處理，每次將陣列中的一條記錄更新
  for i in v_tl_date.first .. v_tl_date.last loop
    v_replace:=v_tl_date(i)||'_bak';
    v_sql := 'update ' || tab_name_tmp || ' set tl_date=:x'
    ||' where tl_date=:y and mod(to_number(substr(tl_date,9,1)),2) =0';
    dbms_output.put_line(v_sql);
    dbms_output.put_line(v_replace);
    execute immediate v_sql using v_replace,v_tl_date(i);
    commit;
  end loop;
end;


執行情況為：
11:12:03 SQL> exec test_tl2;
 
test_tl_a
update test_tl_a set tl_date=:x where tl_date=:y and mod(to_number(substr(tl_date,-1,1)),2) =0
A20151001_bak
update test_tl_a set tl_date=:x where tl_date=:y and mod(to_number(substr(tl_date,-1,1)),2) =0
A20151002_bak
update test_tl_a set tl_date=:x where tl_date=:y and mod(to_number(substr(tl_date,-1,1)),2) =0
A20151003_bak
update test_tl_a set tl_date=:x where tl_date=:y and mod(to_number(substr(tl_date,-1,1)),2) =0
A20151004_bak
 
PL/SQL procedure successfully completed
 
Executed in 0.016 seconds
 
11:12:15 SQL> select * from test_tl_a;
 
TL_DATE
--------------------
A20151001
A20151002_bak
A20151003
A20151004_bak


相比於方法1，此方法優點在於去掉了多個單引號造成的混亂，
在處理大批量資料時，方法2的事務規模小，而方法1可能因為事務規模過大而無法執行；
但缺點也是顯而易見的-每條滿足條件的資料都需要被更新一次。




如果上表中大量資料需要更新，需要批量處理。
oracle中可以使用bulk collect+forall。
但forall只能對緊隨其後的DML操作有效，因此只能單獨使用bulk collect。
為了在批量處理的同時控制事務規模，使用limit進行限制。


方法3：
create or replace procedure test_tl2
is
  tab_name_tmp varchar2(40);
  date_nu      number;
  v_sql varchar2(500);
  type typ_tl_date is table of test_tl_a.tl_date%type;--宣告型別
  v_tl_date typ_tl_date;--型別變數
  v_replace varchar2(20);
  v_sql_tab varchar2(500);
begin
  select to_number(to_char(sysdate,'ss'))
    into date_nu
    from dual;
  if date_nu <= 20 then
    tab_name_tmp := 'test_tl_a';
  elsif date_nu >= 21 and date_nu <= 40 then
    tab_name_tmp := 'test_tl_b';
  else
    tab_name_tmp := 'test_tl_c';
  end if;
  dbms_output.put_line(tab_name_tmp);  
  
  --根據表名拼接語句，將對應資料插入
  v_sql_tab:='select tl_date from '||tab_name_tmp;
  execute immediate v_Sql_tab bulk collect into v_tl_date;
  
  --迴圈處理，每次將陣列中的一條記錄更新
  for i in v_tl_date.first .. v_tl_date.last loop
    v_replace:=v_tl_date(i)||'_bak';
    v_sql := 'update ' || tab_name_tmp || ' set tl_date=:x'
    ||' where tl_date=:y and mod(to_number(substr(tl_date,9,1)),2) =0';
    dbms_output.put_line(v_sql);
    dbms_output.put_line(v_replace);
    execute immediate v_sql using v_replace,v_tl_date(i);
    commit;
  end loop;
end;

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