declare cursor tb_b is select id from b; cursor fieldB_A is --A、B表的字段对应关系 select bcol.column_name,bcol.data_type,m.field_a from user_tab_columns bcol,mapping m where upper(bcol.column_name)=upper(m.field_b) and bcol.table_name='B'; a_fields long; b_fields long; BEGIN --创建B表的字段字符串b_fields、对应的A表中的字段字符串a_fields for m in fieldB_A loop a_fields:=a_fields||','||m.field_a; b_fields:=b_fields||','||m.column_name; end loop; --去掉开头的',' a_fields:=substr(a_fields,2); b_fields:=substr(b_fields,2);
--用update tb set ()=(select from where ) where 来进行批量更新 execute immediate 'update B set ('||b_fields||')=(select '||a_fields||' from A where A.COLUMN2=B.ID); commit; end;
cursor tb_b is
select id from b;
cursor fieldB_A is --A、B表的字段对应关系
select bcol.column_name,bcol.data_type,m.field_a
from user_tab_columns bcol,mapping m
where upper(bcol.column_name)=upper(m.field_b)
and bcol.table_name='B';
a_fields long;
b_fields long;
BEGIN
--创建B表的字段字符串b_fields、对应的A表中的字段字符串a_fields
for m in fieldB_A loop
a_fields:=a_fields||','||m.field_a;
b_fields:=b_fields||','||m.column_name;
end loop;
--去掉开头的','
a_fields:=substr(a_fields,2);
b_fields:=substr(b_fields,2);
--用update tb set ()=(select from where ) where 来进行批量更新
execute immediate 'update B set ('||b_fields||')=(select '||a_fields||' from A where A.COLUMN2=B.ID);
commit;
end;