oracle关联存储,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;