oracle两个约束,Oracle中的约束介绍(2)

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  ;