根据条件动态更新不同表的数据

前天运维问了一个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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1813942/