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