对系统默认的约束名和索引名进行重命名
用于重命名系统默认约束条件名称和索引名称的存储过程示例:
或者将过程proc _ rename _ constraint
替换为
-查找用户所有表的游标
cursor _ table是从user _ tables中选择table _ name
-cursor
cursor cur _ cons(c _ tablevarchar 2)是select
c.constraint _ name,c.constraint _ type,
c . search _ condition from user _ constraints c
其中c.table_name=c_table
和substr(c.constraint_name,2,2)' K _ ';
-find a cursor
cursor cur _ columns(c _ cons varchar 2)is select
column _ name from user _ cons _ columns
其中constraint _ name = c _ cons
-存储修改后的约束名
v _ new _ cons _ name varchar 2(100);
-存储修改约束名称的SQL语句
v _ SQL varchar 2(150);
-存储重复名称的数量
CNT number:= 1;
-存储新索引名称的数量
n _ idx number;
-存储新约束名称的数量
n _ con number;
begin
-在cur _ table循环中回收cur _ l表的表名
-取cur _ cons (cur _ l表中cur _ lcons的约束名
。table _ name)循环。
v _ new _ cons _ name:= null;
-循环取cur _ columns(cur _ lcons . constraint _ name)循环中cur _ lcolumns的字段名
v _ new _ cons _ name:= v _ new _ cons _ name | | cur _ lcolumns . column _ name
end循环;
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 | | ' NOT NULL ';
elsif cur _ lcons . constraint _ type = ' C '
and instr(cur _ lcons . search _ condition,' IS NOT NULL ')= 0
and cur _ lcons . search _ condition NOT NULL then
v _ new _ cons _ name:= ' CK _ ' | | v _ new _ cons _ name;
end if;
-如果约束名称超过30个字符
iflength(v _ new _ cons _ name)> 29,则
v _ new _ cons _ name:= substr(v _ new _ cons _ name,1,15)
|
end if;
-查明系统中是否有新的约束名称
select count(*)into n _ con from user _ constraints
其中constraint _ name = v _ new _ cons _ name;
select count(*)into n _ idx from user _ indexes where
index _ name = v _ new _ cons _ name;
如果n_con > 0或n_idx > 0则
v _ new _ cons _ name:= v _ new _ cons _ name | | to _ char(CNT);
CNT:= CNT+1;
end if;
-重命名由主键和键创建的索引,该名称更改为新的约束名
if cur _ lcons . constraint _ type = ' p ' orcur _ lcons . constraint _ type = ' u ',然后
select count(*)into n _ con from user _ indexes where
index _
if n _ con = 1,然后
v _ SQL:= ' alter index ' | | cur _ lcons . constraint _ name
| '重命名为' | '
立即执行v _ sql
end if;
end if;
-重命名约束
v _ SQL:= ' alter table ' | | cur _ l table。table _ name ||| '重命名约束';
v _ SQL:= v _ SQL | | cur _ lcons . constraint _ name | | ' to ' | | v _ new _ cons _ name;
立即执行v _ sql
结束循环;
结束循环;
结束;
位律师回复
0条评论