批量修改表中某些字段的值

一 使用场景
   表中某一字段值原值"123-ABC”， 但要求修改为"123ABC“，数据库中很多表有关联此字段值

二 修改方式

1 首先清除主键约束
select 'ALTER  TABLE ' || consc.table_name || ' disable  constraint ' ||
       consc.constraint_name
  from dba_cons_columns consc,
       (select tb.owner, tb.table_name, cols.column_name
          from dba_tables tb, dba_tab_cols cols
         where tb.OWNER = 'ORCL'
           and cols.OWNER = tb.OWNER
           and tb.table_name = cols.table_name
           and cols.column_name in ('XLID', 'TQID')) tbs
 where consc.table_name = tbs.table_name
   and consc.owner = 'ORCL'
   and consc.column_name in ('XLID', 'TQID')
 order by consc.constraint_name

2 其次生成更新语句
select tb.owner,
       ' update ' || tb.table_name || ' set ' || cols.column_name ||
       '= substr(' || cols.column_name || ',0,instr(' || cols.column_name ||
       ',''-'')-1)||substr(' || cols.column_name || ',instr(' ||
       cols.column_name || ',''-'')+1,length(' || cols.column_name ||
       ')) where instr(' || cols.column_name || ',''-'')>0',
       cols.column_name
  from dba_tables tb, dba_tab_cols cols
 where tb.OWNER = 'ORCL'
   and cols.OWNER = tb.OWNER
   and tb.table_name = cols.table_name
   and cols.column_name in ('XLID', 'TQID')

3 恢复主键约束
select 'ALTER  TABLE ' || consc.table_name || ' able  constraint ' ||
       consc.constraint_name
  from dba_cons_columns consc,
       (select tb.owner, tb.table_name, cols.column_name
          from dba_tables tb, dba_tab_cols cols
         where tb.OWNER = 'ORCL'
           and cols.OWNER = tb.OWNER
           and tb.table_name = cols.table_name
           and cols.column_name in ('XLID', 'TQID')) tbs
 where consc.table_name = tbs.table_name
   and consc.owner = 'ORCL'
   and consc.column_name in ('XLID', 'TQID')
 order by consc.constraint_name