Oracle fully copies the Table Structure Stored Procedures, oracle stored procedures

Oracle fully copies the Table Structure Stored Procedures, oracle stored procedures

Recently, when dealing with a table sharding problem, we need to create an automatic table sharding Stored Procedure for the program. We need to ensure that all the table structures, constraints, indexes, and so on are consistent. In addition, views, stored procedures, permissions and so on.

In Mysql, the stored procedure for creating sub-tables is quite simple: create table if not exists <new_table_name> like <old_table_name>;, constraints, and indexes.

But it does not seem to exist in Oracle, so you can only write it by yourself. There are many cases 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; -- not null of the c2 primary key will not be inherited, but not null constraints will inherit, so c2 all comments/* v_add_notnull_ SQL clob; cursor notnull_cols is select COLUMN_NAME from user_tab_columns t where t. NULLABLE = 'N' and t. TABLE_NAME = tableName; */-- c3: Primary Key cursor. Although only one primary key exists, the cursor v_add_primary_ SQL clob is used for unification. 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 the unique index cursor v_create_unique_index_ SQL clob generated by the unique constraint; 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 the default value 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 a 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 the 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 uniqueness constraint. Because the original constraint name may have a custom name due to different creation constraints, therefore, we can directly name the unique constraint 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 from the independent table. The suffix dateStr is used to show different 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 for c6 in unique_cols loop v_create_unique_index_ SQL not generated due to constraints: = 'create unique Index' | c6.index _ name | '_' | dateStr | 'on' | newTable | '(' | c6.index _ cols |') '; execute immediate v_create_unique_index_ SQL; end loop; end createTable ;/