oracle拷贝表

CREATE OR REPLACE PROCEDURE CREATETABLE(tableName in varchar2,

                                        dateStr   in varchar2)

  AUTHID CURRENT_USER as

  newTable varchar2(32) := tableName || '_' || dateStr;

 

  v_create_table_sql clob;

  --c1，默认值游标

  v_add_default_sql clob;

  cursor default_cols is

    select COLUMN_NAME, DATA_DEFAULT

      from user_tab_columns

     where DATA_DEFAULT is not null

       and TABLE_NAME = tableName;

  --c2 主键的not null不会继承，但not null约束的会继承,因此c2全部注释

  /*v_add_notnull_sql clob;

  cursor notnull_cols is select COLUMN_NAME from user_tab_columns t where t.NULLABLE='N' and  and t.TABLE_NAME=tableName;*/

  --c3，主键游标，虽然主键只能有一个，但为统一起见还是用了游标

  v_add_primary_sql clob;

  cursor primary_cols is

    select distinct tmp.TABLE_NAME,

                    tmp.INDEX_NAME,

                    to_char(wm_concat(tmp.COLUMN_NAME)

                            over(partition by tmp.TABLE_NAME)) as pri_cols

      from (select i.TABLE_NAME,

                   i.INDEX_NAME,

                   i.COLUMN_NAME,

                   i.COLUMN_POSITION

              from user_ind_columns i

              join user_constraints c

                on i.INDEX_NAME = c.index_name

             where c.CONSTRAINT_TYPE = 'P'

               and i.TABLE_NAME = tableName

             order by 1, 2, 4) tmp;

  --c4，唯一约束游标

  v_add_unique_sql clob;

  cursor unique_cons is

    select distinct tmp.TABLE_NAME,

                    tmp.INDEX_NAME,

                    to_char(wm_concat(tmp.COLUMN_NAME)

                            over(partition by tmp.TABLE_NAME,

                                 tmp.INDEX_NAME)) as uni_cols,

                    replace(to_char(wm_concat(tmp.COLUMN_NAME)

                                    over(partition by tmp.INDEX_NAME)),

                            ',',

                            '_') as new_indexname

      from (select i.TABLE_NAME,

                   i.INDEX_NAME,

                   i.COLUMN_NAME,

                   i.COLUMN_POSITION

              from user_ind_columns i

              join user_constraints c

                on i.INDEX_NAME = c.index_name

             where c.CONSTRAINT_TYPE = 'U'

               and i.TABLE_NAME = tableName

             order by 1, 2, 4) tmp;

  --c5，非唯一非主键索引游标

  v_create_index_sql clob;

  cursor normal_indexes is

    select distinct tmp.TABLE_NAME,

                    tmp.INDEX_NAME,

                    to_char(wm_concat(tmp.COLUMN_NAME)

                            over(partition by tmp.TABLE_NAME,

                                 tmp.INDEX_NAME)) as index_cols

      from (select i.TABLE_NAME,

                   i.INDEX_NAME,

                   c.COLUMN_NAME,

                   c.COLUMN_POSITION

              from user_indexes i

              join user_ind_columns c

                on i.INDEX_NAME = c.INDEX_NAME

             where index_type = 'NORMAL'

               and i.TABLE_NAME = tableName

               and i.uniqueness = 'NONUNIQUE'

             order by 1, 2, 4) tmp;

  --c6，不是由唯一约束生成的唯一索引游标

  v_create_unique_index_sql clob;

  cursor unique_cols is

    select distinct tmp.TABLE_NAME,

                    tmp.INDEX_NAME,

                    to_char(wm_concat(tmp.COLUMN_NAME)

                            over(partition by tmp.TABLE_NAME,

                                 tmp.INDEX_NAME)) as index_cols

      from (select u_i.TABLE_NAME,

                   u_i.INDEX_NAME,

                   c.COLUMN_NAME,

                   c.COLUMN_POSITION

              from (select *

                      from user_indexes

                     where table_name = tableName

                       and index_type = 'NORMAL'

                       and index_name not in

                           (select index_name

                              from user_constraints

                             where table_name = tableName

                               and index_name is not null)) u_i

              join user_ind_columns c

                on u_i.INDEX_NAME = c.INDEX_NAME

             where u_i.TABLE_NAME = tableName

               and u_i.uniqueness = 'UNIQUE'

             order by 1, 2, 4) tmp;

begin

  --创建表结构

  v_create_table_sql := 'create table ' || newTable || ' as select * from ' ||

                        tableName || ' where 1=2';

  execute immediate v_create_table_sql;

  --添加默认值

  for c1 in default_cols loop

    v_add_default_sql := 'alter table ' || newTable || ' modify ' ||

                         c1.column_name || ' default ' || c1.DATA_DEFAULT;

    execute immediate v_add_default_sql;

  end loop;

  --添加非空约束

  /*  for c2 in notnull_cols loop

    v_add_notnull_sql:='alter table '||newTable||' modify '||c2.column_name||' not null';

    execute immediate v_add_notnull_sql;

  end loop;*/

  --添加主键约束

  for c3 in primary_cols loop

    v_add_primary_sql := 'alter table ' || newTable ||

                         ' add constraint Pk_' || newTable ||

                         ' primary key(' || c3.pri_cols || ')';

    execute immediate v_add_primary_sql;

  end loop;

  --添加唯一性约束，由于原约束名可能由于创建约束的方法不同，存在系统自定义的名字，因此这里直接命名唯一约束

  for c4 in unique_cons loop

    v_add_unique_sql := 'alter table ' || newTable || ' add constraint U_' ||

                        c4.new_indexname || ' unique(' || c4.uni_cols || ')';

    execute immediate v_add_unique_sql;

  end loop;

  --创建非主键且非唯一的索引，索引名字直接继承自主表，后缀dateStr以示不同

  for c5 in normal_indexes loop

    v_create_index_sql := 'create index ' || c5.index_name || '_' ||

                          dateStr || ' on ' || newTable || '(' ||

                          c5.index_cols || ')';

    execute immediate v_create_index_sql;

  end loop;

  --创建不是由于约束生成的唯一索引

  for c6 in unique_cols loop

    v_create_unique_index_sql := 'create unique index ' || c6.index_name || '_' ||

                                 dateStr || ' on ' || newTable || '(' ||

                                 c6.index_cols || ')';

    execute immediate v_create_unique_index_sql;

  end loop;

end createTable;