一個根據已有表結構來建立新表的儲存過程

最近開發人員頻繁要求參照舊錶結構來建立新表，每次人工操作比較繁瑣，寫了一個儲存過程來自動執行，如下：
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/，如需轉載，請註明出處，否則將追究法律責任。