ITPub博客

首页 > 数据库 > Oracle > 一个根据已有表结构来创建新表的存储过程

一个根据已有表结构来创建新表的存储过程

原创 Oracle 作者:lsq_008 时间:2017-04-21 16:41:07 0 删除 编辑
最近开发人员频繁要求参照旧表结构来创建新表,每次人工操作比较繁琐,写了一个存储过程来自动执行,如下:
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  dbms_metadata.get_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);
--创建表
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  dbms_metadata.get_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');

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-2137769/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1307254