CREATE OR REPLACE FUNCTION GETREFERENCESCOLUMN
(
i_table_name varchar2 , --表名
i_constraint_name varchar2 --列名
)
RETURN nvarchar2
AS
v_table_name nvarchar2(50);
BEGIN
v_table_name:='';
for cur in (
select distinct
b.column_name
from
(select a.constraint_name,b.table_name,b.column_name,a.r_constraint_name
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and a.constraint_name=b.constraint_name
order by b.position
) a,
(select distinct a.r_constraint_name,b.table_name,b.column_name,b.position
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and
a.r_constraint_name=b.constraint_name
order by b.position)
b
where a.r_constraint_name=b.r_constraint_name and b.table_name = i_table_name
and b.r_constraint_name = i_constraint_name
) loop
v_table_name := v_table_name||cur.column_name ||',';
end loop;
v_table_name := trim(',' from v_table_name);
RETURN v_table_name;
END;
/***----------------------------外键表-------------------***/
CREATE OR REPLACE FUNCTION GETFOREIGNCOLUMN
(
i_table_name varchar2 , --表名
i_constraint_name varchar2 --列名
)
RETURN nvarchar2
AS
v_table_name nvarchar2(50);
BEGIN
v_table_name:='';
for cur in (
select distinct
a.column_name
from
(select a.constraint_name,b.table_name,b.column_name,a.r_constraint_name
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and a.constraint_name=b.constraint_name
order by b.position
) a,
(select distinct a.r_constraint_name,b.table_name,b.column_name,b.position
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and
a.r_constraint_name=b.constraint_name
order by b.position)
b
where a.r_constraint_name=b.r_constraint_name and a.table_name = i_table_name
and a.constraint_name = i_constraint_name
) loop
v_table_name := v_table_name||cur.column_name ||',';
end loop;
v_table_name := trim(',' from v_table_name);
RETURN v_table_name;
END;
/***-------------------导出外键------------------***/
select
'ALTER TABLE ' ||
a.table_name
|| ' ADD CONSTRAINT ' ||
a.constraint_name
|| ' FOREIGN KEY ('
|| a.column_name||')'
||'
REFERENCES ' ||b.table_name || ';'
from
(select a.constraint_name,b.table_name,b.column_name,a.r_constraint_name
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and a.constraint_name=b.constraint_name
) a,
(select distinct a.r_constraint_name,b.table_name,b.column_name
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and
a.r_constraint_name=b.constraint_name)
b
where a.r_constraint_name=b.r_constraint_name
order by a.table_name ;