导出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  ;