创建删除外键的SQL
因为工作需要重建外键,手写了一个创建外键的PL_SQL,以后需要重建外键的同志就不需要自己写了,可以参照一下做法:
/*创建表db_constraint,用于保存生成的SQL*/
create table db_dmzh.db_constraint
(
constraint varchar2(4000),
tab_name varchar2(100),
cons_name varchar2(1000),
del_constraint varchar2(1000),
cjbz varchar2(1)
)
tablespace DB_ZGXT
storage
(
initial 24K
next 10M
minextents 1
maxextents unlimited
pctincrease 0
);
/*表的注释*/
comment on table DB_DMZH.db_constraint
is '代码表与外健关联表';
comment on column db_constraint.constraint
is '创建外键的sql语句';
comment on column db_constraint.tab_name
is '关联的代码表的名字';
comment on column db_constraint.cons_name
is '外键约束的名字';
comment on column db_constraint.cjbz
is '是否成功‘1’代表成功,‘0’或空代表不成功';
comment on column db_constraint.del_constraint
is '删除外键的sql语句';
delete from db_dmzh.db_constraint;
commit;
/*创建所有的关于系统维护的外键约束*/
declare
p_str varchar2(1000);
p_owner varchar2(100);
p_tabname varchar2(1000);
p_consname varchar2(1000);
p_r_constraint varchar2(1000);
p_r_owner varchar2(1000);
cursor c2 is select column_name from all_cons_columns
where owner=p_owner and table_name=p_tabname and constraint_name=p_consname order by position ;
cursor c1 is select owner,table_name,constraint_name,ltrim(r_constraint_name,substr(r_constraint_name,1,3)),r_owner
from all_constraints
where owner in ('DB_GLFW','DB_FPGL','DB_ZSJK','DB_JCGL','DB_FZZX','DB_ZGXT','DB_XTWH')
and CONSTRAINT_TYPE='R' and r_owner='DB_XTWH' ;
p_str1 varchar2(2000);
begin
open c1;
loop
fetch c1 into p_owner,p_tabname,p_consname,p_r_constraint,p_r_owner;
exit when c1%notfound;
/*内循环*/
p_str :='';
p_str1 :='';
open c2;
loop
fetch c2 into p_str1;
exit when c2%notfound;
p_str :=p_str||p_str1||','; /*生成要加外键的字段*/
end loop;
close c2;
/*内循环结束*/
select substr(p_str,1,length(p_str) - 1) into p_str from dual;
/*去多余的逗号*/
insert into db_dmzh.db_constraint (constraint,tab_name,cons_name,del_constraint)
select ' alter table '||p_owner||'.'||p_tabname||' add constraint '||p_consname||
' foreign key ('||p_str||') references '||p_r_owner||'.'||p_r_constraint
,p_r_constraint
,p_consname
,'ALTER TABLE '||p_owner||'.'||p_tabname||' drop constraint '||p_consname
from dual;
commit;
end loop;
close c1;
end;
/
/*创建表db_constraint,用于保存生成的SQL*/
create table db_dmzh.db_constraint
(
constraint varchar2(4000),
tab_name varchar2(100),
cons_name varchar2(1000),
del_constraint varchar2(1000),
cjbz varchar2(1)
)
tablespace DB_ZGXT
storage
(
initial 24K
next 10M
minextents 1
maxextents unlimited
pctincrease 0
);
/*表的注释*/
comment on table DB_DMZH.db_constraint
is '代码表与外健关联表';
comment on column db_constraint.constraint
is '创建外键的sql语句';
comment on column db_constraint.tab_name
is '关联的代码表的名字';
comment on column db_constraint.cons_name
is '外键约束的名字';
comment on column db_constraint.cjbz
is '是否成功‘1’代表成功,‘0’或空代表不成功';
comment on column db_constraint.del_constraint
is '删除外键的sql语句';
delete from db_dmzh.db_constraint;
commit;
/*创建所有的关于系统维护的外键约束*/
declare
p_str varchar2(1000);
p_owner varchar2(100);
p_tabname varchar2(1000);
p_consname varchar2(1000);
p_r_constraint varchar2(1000);
p_r_owner varchar2(1000);
cursor c2 is select column_name from all_cons_columns
where owner=p_owner and table_name=p_tabname and constraint_name=p_consname order by position ;
cursor c1 is select owner,table_name,constraint_name,ltrim(r_constraint_name,substr(r_constraint_name,1,3)),r_owner
from all_constraints
where owner in ('DB_GLFW','DB_FPGL','DB_ZSJK','DB_JCGL','DB_FZZX','DB_ZGXT','DB_XTWH')
and CONSTRAINT_TYPE='R' and r_owner='DB_XTWH' ;
p_str1 varchar2(2000);
begin
open c1;
loop
fetch c1 into p_owner,p_tabname,p_consname,p_r_constraint,p_r_owner;
exit when c1%notfound;
/*内循环*/
p_str :='';
p_str1 :='';
open c2;
loop
fetch c2 into p_str1;
exit when c2%notfound;
p_str :=p_str||p_str1||','; /*生成要加外键的字段*/
end loop;
close c2;
/*内循环结束*/
select substr(p_str,1,length(p_str) - 1) into p_str from dual;
/*去多余的逗号*/
insert into db_dmzh.db_constraint (constraint,tab_name,cons_name,del_constraint)
select ' alter table '||p_owner||'.'||p_tabname||' add constraint '||p_consname||
' foreign key ('||p_str||') references '||p_r_owner||'.'||p_r_constraint
,p_r_constraint
,p_consname
,'ALTER TABLE '||p_owner||'.'||p_tabname||' drop constraint '||p_consname
from dual;
commit;
end loop;
close c1;
end;
/