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

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

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

但是在Oracle中貌似没有，所以只能自己写，需要考虑的情况比较多，脚本如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146

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;   /