Recently, when dealing with a table splitting problem, we need to create a stored procedure for the program to automatically split tables. We need to ensure that all table structures, constraints, indexes, etc. are consistent. In addition, views, stored procedures, permissions, etc. are not considered temporarily.
In Mysql, the procedure of creating a sub table is quite simple: create table if not exists < new table name > like < old table name >; that is, constraints and indexes are all available.
However, it seems that it does not exist in Oracle, so it can only be written by itself. There are many situations to consider. The script is as follows:
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，Default cursor
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 Primary key not null Will not inherit, but not null Constraint will inherit,therefore c2 All notes
/*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，Primary key cursors. Although there can only be one primary key, cursors are used for unification
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，Unique constraint cursor
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，Non unique non primary key index cursor
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，Not a unique index cursor generated by a unique constraint
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
--Create table structure
v_create_table_sql := 'create table ' || newTable || ' as select * from ' ||
tableName || ' where 1=2';
execute immediate v_create_table_sql;
--Add default
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;
--Add non empty constraint
/* 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;*/
--Add primary key constraint
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;
--Add a unique constraint. Because the original constraint name may have a system defined name due to different methods of creating constraints, the unique constraint is named directly here
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;
--Create a non primary key and non unique index. The index name directly inherits the independent table and suffix dateStr To make a difference
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;
--Create a unique index that is not generated by a constraint
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;
/