Oracle批量更新

①导出批量更新SQL

select CONCAT(CONCAT(CONCAT(CONCAT('update test_a set a_date=', b_date),                            'where a_id='),                     b_id),              ' and a_flag=1;')  from test_b;

②批量更新数据

#方法一  update test_a a   set a.a_date = (select b.b_date                     from test_b b                    where a.a_id = b.b_id                      and a.a_flag = 1); #方法二begin  for r in (select a.rowid, b.b_date              from TEST_A a, test_b b             where a.a_id = b.b_id             and a.a_flag =1) loop    update TEST_A t set t.a_date = r.b_date where t.rowid = r.rowid;  end loop;end;#方法三create or replace procedure test as  type temp_record IS record(b_id test_b.b_id%type,  b_date test_b.b_date%type  );  type temp_record_table IS table OF temp_record;  tmp temp_record_table ;begin  SELECT b.b_id, b.b_date BULK COLLECT INTO tmp FROM test_b b;  FOR I IN 1 .. tmp.COUNT LOOP    UPDATE TEST_A a SET a.a_date = tmp(I).b_date    WHERE a.a_id = tmp(I).b_id         AND a.a_flag = 1;  END LOOP;end;#方法四merge into TEST_A ausing test_b bon (a.a_id = b.b_id and a.a_flag = 1)when matched then  update set a.a_date = b.b_date;