最近在處理一個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全部注釋
--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 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;
/
