最近在處理一個分表的問題時，需要為程式創建一個自動分表的存儲過程，需要保證所有表結構，約束，索引等等一致，此外視圖，存儲過程，許可權等等問題暫不用考慮。 在Mysql中，創建分表的存儲過程，相當簡單：create table if not exists <new_table_name> like <o ...
最近在處理一個分表的問題時，需要為程式創建一個自動分表的存儲過程，需要保證所有表結構，約束，索引等等一致，此外視圖，存儲過程，許可權等等問題暫不用考慮。
在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;
/
