Oracle中的约束介绍(2)

导出CHECK约束：从现有用户中导出CHECK约束

SELECT  'ALTER   table'||
  A.TABLE_NAME
  || ' add constraint  '  || b.constraint_name  || '  check ('  , a.search_condition ,');'
from USER_CONSTRAINTS a,USER_CONS_COLUMNS b
where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and a.CONSTRAINT_TYPE =  'C' 

导出外键约束：从现有用户中导出外键约束,其中一个外键名,可能由多个字段组成,在这里用循环导出.

/***----------------------------主键表-------------------***/

 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  ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17066567/viewspace-631792/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17066567/viewspace-631792/