mysql根据已有的表创建新表_⼀个根据已有表结构来创建新表
的存储过程
最近开发⼈员频繁要求参照旧表结构来创建新表，每次⼈⼯操作⽐较繁琐，写了⼀个存储过程来⾃动执⾏，如下：
create or replace procedure p_auto_create_tab(i_old_tab_name varchar2,i_new_tab_name varchar2)
as
--作者:
--创建时间:20170320
--适⽤数据库版本:oracle 11g及以上版本
--功能：输⼊已有表的表名和新建表的表名，⾃动创建新表及索引、序列，⽬前只⽀持⾮分区表和按天的范围分区表，对于分区表，创建的是按天⾃动分区。
--输⼊参数:i_old_tab_name 旧表名，i_new_tab_name 新表名
v_ddl_sql VARCHAR2(32000);
v_tab_ddl CLOB;
v_ind_ddl CLOB;
v_create_sql CLOB;
v_create_ind_sql CLOB;
v_str_loc number;
v_part_key varchar2(30);
v_old_primary_key varchar2(30);
v_new_primary_key varchar2(30);
v_part_flag varchar2(30);
v_today date := sysdate;
v_index_name varchar2(30);
v_create_seq_sql varchar2(32000);
BEGIN
--获取建表语句
dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM, 'SQLTERMINATOR', false);
v_ddl_sql := 'select  _ddl(''TABLE'','''||i_old_tab_name||''') from dual';
EXECUTE IMMEDIATE v_ddl_sql INTO v_tab_ddl;
--确认是否是分区表
select partitioned into v_part_flag from user_tables where table_name=i_old_tab_name;
--将建表语句中表名替换为新的表名
v_tab_ddl := dbms_lob.SUBSTR(v_tab_ddl,32767);
v_create_sql := replace(v_tab_ddl,i_old_tab_name,i_new_tab_name);
--获取并修改主键名称
BEGIN
--获取主键名称
select CONSTRAINT_NAME into v_old_primary_key from user_constraints where table_name=i_old_tab_name and CONSTRAINT_TYPE='P';
--⽣成新的主键名称
v_new_primary_key := substr('PK_'||i_new_tab_name,0,30);
--替换主键名称
v_create_sql := replace(v_create_sql,v_old_primary_key,v_new_primary_key);
exception when no_data_found then
NULL;
END;
--如果是分区表，加上创建分区的信息
if (v_part_flag = 'YES') then
--获取分区键
select column_name into v_part_key from user_part_key_columns where name=i_old_tab_name;
--去掉partition by后⾯的内容
v_str_loc := instr(v_create_sql,'PARTITION BY');
v_create_sql := substr(v_create_sql,0,v_str_loc-1);
--添加分区信息
v_create_sql := v_create_sql||'partition by range ('||v_part_key||') interval(NUMTODSINTERVAL(1,''DAY''))(partition p'||to_char(v_today,'yyyymmdd')||' values less than(to_date('''||to_char(v_today,'yyyy-mm-dd')||''', ''yyyy-mm-dd'')))';
--else
--v_create_sql := substr(v_create_sql,1,length(v_create_sql)-1);
end if;
dbms_output.put_line(v_create_sql);
sql语句替换表中内容--创建表
execute immediate v_create_sql;
--开始创建索引
for item in (select index_name from user_indexes
where
index_name not in
(select index_name from user_constraints
where
table_name=i_old_tab_name and CONSTRAINT_TYPE='P')
and table_name=i_old_tab_name
)
loop
v_ddl_sql := 'select  _ddl(''INDEX'','''||item.index_name||''') from dual';
EXECUTE IMMEDIATE v_ddl_sql INTO v_ind_ddl;
--⽣成新的索引名称
v_index_name := 'IDX_'||substr(i_new_tab_name,0,20)||'_'||to_char(trunc(dbms_random.value(100000,1000000))); v_create_ind_sql := replace(v_ind_ddl,item.index_name,v_index_name);
v_create_ind_sql := replace(v_create_ind_sql,i_old_tab_name,i_new_tab_name);
--v_create_ind_sql := substr(v_create_ind_sql,1,length(v_create_ind_sql)-1);
dbms_output.put_line(v_create_ind_sql);
execute immediate v_create_ind_sql;
end loop;
--开始创建序列
v_create_seq_sql := 'create sequence seq_'||i_new_tab_name||' start with 1';
dbms_output.put_Line(v_create_seq_sql);
execute immediate v_create_seq_sql;
END;
/
---测试
set serveroutput on
exec p_auto_create_tab('GW_95555_RLOG','GW_12345_RLOG');
exec p_auto_create_tab('TT3','T333');

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。