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

最近开发人员频繁要求参照旧表结构来创建新表，每次人工操作比较繁琐，写了一个存储过程来自动执行，如下：
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-2137769/