Oracle完全复制表结构的存储过程

最近在处理一个Oracle分表的问题时，需要为程序创建一个自动分表的存储过程，需要保证所有表结构，约束，索引等等一致，此外视图，存储过程，权限等等问题暂不用考虑。

在MySQL中，创建分表的存储过程，相当简单：create table if not exists <new_table_name> like <old_table_name>;即可，约束，索引一应俱全。

但是在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;
 
/