create or replace procedure proc_rename_constraint
as
--²éÕÒÓû§ËùÓбíµÄÓαê
cursor cur_table is select table_name from user_tables;

--²éÕÒijÕűíËùÓÐÔ¼ÊøµÄÓαê
cursor cur_cons (c_table varchar2) is select c.constraint_name,c.constraint_type,
c.search_condition from user_constraints c where c.table_name=c_table
and substr(c.constraint_name,2,2) <> 'K_';

--²éÕÒij¸öÔ¼ÊøËùÓÐ×ֶεÄÓαê
cursor cur_columns(c_cons varchar2) is select column_name from user_cons_columns
where constraint_name=c_cons;

--´æ´¢Ð޸ĺóµÄÔ¼ÊøÃû
v_new_cons_name varchar2(100);

--´æ´¢ÐÞ¸ÄÔ¼ÊøÃûµÄSQLÓï¾ä
v_sql varchar2(150);

--´æ´¢ÖØÃûµÄ¸öÊý
cnt number :=1;

--´æ´¢ÐÂË÷ÒýÃûµÄ¸öÊý
n_idx number;

--´æ´¢ÐÂÔ¼ÊøÃûµÄ¸öÊý
n_con number;

begin
--Ñ­»·È¡±íÃû
for cur_ltable in cur_table loop

--Ñ­»·È¡Ô¼ÊøÃû
for cur_lcons in cur_cons(cur_ltable.table_name) loop

v_new_cons_name :=null;

--Ñ­»·È¡×Ö¶ÎÃû
for cur_lcolumns in cur_columns(cur_lcons.constraint_name) loop
v_new_cons_name := v_new_cons_name || cur_lcolumns.column_name;
end loop;
v_new_cons_name := replace(v_new_cons_name,'_','');
v_new_cons_name := cur_ltable.table_name ||'_' || v_new_cons_name;

if cur_lcons.constraint_type='P' then
v_new_cons_name := 'PK_' || v_new_cons_name;
elsif cur_lcons.constraint_type='R' then
v_new_cons_name := 'FK_' || v_new_cons_name;
elsif cur_lcons.constraint_type='U' then
v_new_cons_name := 'UK_' || v_new_cons_name;
elsif cur_lcons.constraint_type='C'
and instr(cur_lcons.search_condition,'IS NOT NULL') > 0 then
v_new_cons_name := 'CK_' || v_new_cons_name || 'NOTNULL' ;
elsif cur_lcons.constraint_type='C'
and instr(cur_lcons.search_condition,'IS NOT NULL') = 0
and cur_lcons.search_condition is not null then
v_new_cons_name := 'CK_' || v_new_cons_name;
end if;

--Ô¼ÊøÃû¼ÙÈ糬¹ý30¸ö×Ö·ûµÄ´¦Àí
if length(v_new_cons_name) > 29 then
v_new_cons_name := substr(v_new_cons_name,1,15) || substr(v_new_cons_name,-14);
end if;

--²éÕÒϵͳÀïÊÇ·ñÓÐеÄÔ¼ÊøÃû
select count(*) into n_con from user_constraints where constraint_name=v_new_cons_name;
select count(*) into n_idx from user_indexes where index_name=v_new_cons_name;
if n_con > 0 or n_idx > 0 then
v_new_cons_name := v_new_cons_name || to_char(cnt);
cnt := cnt  1;
end if;


--¶ÔÓÉÖ÷¼üºÍΨһ¼ü´´½¨µÄË÷Òý½øÐиÄÃû,¸ÄºóµÄÃû×ÖΪеÄÔ¼ÊøÃû
if cur_lcons.constraint_type='P' or cur_lcons.constraint_type='U' then
select count(*) into n_con from user_indexes where index_name=cur_lcons.constraint_name;
if n_con = 1 then
v_sql := 'alter index ' || cur_lcons.constraint_name || ' rename to ' || v_new_cons_name;
execute immediate v_sql;
end if;
end if;

--¶ÔÔ¼Êø¸ÄÃû
v_sql := 'alter table ' || cur_ltable.table_name || ' rename constraint ';
v_sql := v_sql || cur_lcons.constraint_name || ' to ' || v_new_cons_name;
execute immediate v_sql;

end loop;

end loop;

end;