Oracle：关联表修改存储过程

CREATE OR REPLACE PROCEDURE change_Zgh(v_oldzgh in varchar2,
                                       v_newzgh in varchar2) is

  cursor cur is
    select (select column_name
              from user_cons_columns b
             where b.constraint_name = a.constraint_name) cname,
           a.table_name tname
      from user_constraints a
     where R_CONSTRAINT_NAME in
           (select constraint_name
              from user_constraints
             where table_name = 'T_JZGJBXX_JBXX');
  v_cname varchar2(100) := '';
  v_tname varchar2(100) := '';
  v_sql   varchar2(2000) := '';
begin

  FOR cur_result in cur LOOP
    begin
      v_cname := cur_result.cname;
      v_tname := cur_result.tname;
      v_sql   := 'update ' || v_tname || ' set ' || v_cname || ' =''' ||
                 v_newzgh || ''' where  ' || v_cname || ' = ''' || v_oldzgh || '''';
      execute immediate v_sql;
      commit;
    end;
  end loop;

end change_Zgh;