Oracle完全复制表结构的存储过程
程序员文章站
2022-03-18 14:02:05
最近在处理一个分表的问题时,需要为程序创建一个自动分表的存储过程,需要保证所有表结构,约束,索引等等一致,此外视图,存储过程,权限等等问题暂不用考虑。 在Mysql中,创建分表的存储过程,相当简单:create table if not exists like
最近在处理一个分表的问题时,需要为程序创建一个自动分表的存储过程,需要保证所有表结构,约束,索引等等一致,此外视图,存储过程,权限等等问题暂不用考虑。
在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; /