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

2018-01-04 19:27:21来源:cnblogs.com作者:Leohahah人点击

分享

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

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

  

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台