对系统默认的约束名和索引名进行重命名的存储过程示例

对系统默认的约束名和索引名进行重命名的存储过程示例[ZT]

标签：PLSQLconstraint 分类：技术学习 2009-02-28 20:05

createorreplaceprocedureproc_rename_constraint

　　as

　　--查找用户所有表的游标

　　cursorcur_tableisselecttable_namefromuser_tables;

　　--查找某张表所有约束的游标

　　cursorcur_cons(c_tablevarchar2)isselect

　　c.constraint_name,c.constraint_type,

　　c.search_conditionfromuser_constraintsc

　　wherec.table_name=c_table

　　andsubstr(c.constraint_name,2,2)<>'K_';

　　--查找某个约束所有字段的游标

　　cursorcur_columns(c_consvarchar2)isselect

　　column_namefromuser_cons_columns

　　whereconstraint_name=c_cons;

　　--存储修改后的约束名

　　v_new_cons_namevarchar2(100);

　　--存储修改约束名的SQL语句

　　v_sqlvarchar2(150);

　　--存储重名的个数

　　cntnumber:=1;

　　--存储新索引名的个数

　　n_idxnumber;

　　--存储新约束名的个数

　　n_connumber;

　　begin

　　--循环取表名

　　forcur_ltableincur_tableloop

　　--循环取约束名

　　forcur_lconsincur_cons(cur_ltable.table_name)loop

　　v_new_cons_name:=null;

　　--循环取字段名

　　forcur_lcolumnsincur_columns(cur_lcons.constraint_name)loop

　　v_new_cons_name:=v_new_cons_name||cur_lcolumns.column_name;

　　endloop;

　　v_new_cons_name:=replace(v_new_cons_name,'_','');

　　v_new_cons_name:=cur_ltable.table_name||'_'||v_new_cons_name;

　　ifcur_lcons.constraint_type='P'then

　　v_new_cons_name:='PK_'||v_new_cons_name;

　　elsifcur_lcons.constraint_type='R'then

　　v_new_cons_name:='FK_'||v_new_cons_name;

　　elsifcur_lcons.constraint_type='U'then

　　v_new_cons_name:='UK_'||v_new_cons_name;

　　elsifcur_lcons.constraint_type='C'

　　andinstr(cur_lcons.search_condition,'ISNOTNULL')>0then

　　v_new_cons_name:='CK_'||v_new_cons_name||'NOTNULL';

　　elsifcur_lcons.constraint_type='C'

　　andinstr(cur_lcons.search_condition,'ISNOTNULL')=0

　　andcur_lcons.search_conditionisnotnullthen

　　v_new_cons_name:='CK_'||v_new_cons_name;

　　endif;

　　--约束名如果超过30个字符的处理

　　iflength(v_new_cons_name)>29then

　　v_new_cons_name:=substr(v_new_cons_name,1,15)

　　||substr(v_new_cons_name,-14);

　　endif;

　　--查找系统里是否有新的约束名

　　selectcount(*)inton_confromuser_constraints

　　whereconstraint_name=v_new_cons_name;

　　selectcount(*)inton_idxfromuser_indexeswhere

　　index_name=v_new_cons_name;

　　ifn_con>0orn_idx>0then

　　v_new_cons_name:=v_new_cons_name||to_char(cnt);

　　cnt:=cnt+1;

　　endif;

　　--对由主键和唯一键创建的索引进行改名,改后的名字为新的约束名

　　ifcur_lcons.constraint_type='P'orcur_lcons.constraint_type='U'then

　　selectcount(*)inton_confromuser_indexeswhere

　　index_name=cur_lcons.constraint_name;

　　ifn_con=1then

　　v_sql:='alterindex'||cur_lcons.constraint_name

　　||'renameto'||v_new_cons_name;

　　executeimmediatev_sql;

　　endif;

　　endif;

　　--对约束改名

　　v_sql:='altertable'||cur_ltable.table_name||'renameconstraint';

　　v_sql:=v_sql||cur_lcons.constraint_name||'to'||v_new_cons_name;

　　executeimmediatev_sql;

　　endloop;

　　endloop;

　　end;