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

245次阅读

共计 4874 个字符，预计需要花费 13 分钟才能阅读完成。

最近在处理一个 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;

/