Stored Procedures for completely copying table structures in Oracle

Stored Procedures for completely copying table structures in Oracle

Recently, when dealing with an Oracle 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
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;
-- The not null of the c2 primary key will not be inherited, but the not null constraint will be inherited, 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 is still 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 constrained 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 generated by the 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 a table structure
V_create_table_ SQL: = 'create table' | newTable | 'as select * from' |
TableName | 'where 1 = 2 ';
Execute immediate v_create_table_ SQL;
-- Add 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 primary key constraints
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 custom name due to different creation constraints, the unique constraint is directly named 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 from the independent table, and the suffix dateStr is 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 not generated due to constraints
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;
 
/