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

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

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

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

/