Oracle完全複製表結構的預存程序

Oracle完全複製表結構的預存程序

最近在處理一個Oracle分表的問題時，需要為程式建立一個自動分表的預存程序，需要保證所有表結構，約束，索引等等一致，此外視圖，預存程序，許可權等等問題暫不用考慮。

在MySQL中，建立分表的預存程序，相當簡單：create table if not exists <new_table_name> like <old_table_name>;即可，約束，索引一應俱全。

但是在Oracle中貌似沒有，所以只能自己寫，需要考慮的情況比較多，指令碼如下：

CREATE OR REPLACE PROCEDURE CREATETABLE(tableName in varchar2,
                                        dateStr  in varchar2)
  AUTHID CURRENT_USER as
  newTable varchar2(32) := tableName || '_' || dateStr;
 
  v_create_table_sql clob;
  --c1，預設值遊標
  v_add_default_sql clob;
  cursor default_cols is
    select COLUMN_NAME, DATA_DEFAULT
      from user_tab_columns
    where DATA_DEFAULT is not null
      and TABLE_NAME = tableName;
  --c2 主鍵的not null不會繼承，但not null約束的會繼承,因此c2全部注釋
  /*v_add_notnull_sql clob;
  cursor notnull_cols is select COLUMN_NAME from user_tab_columns t where t.NULLABLE='N' and  and t.TABLE_NAME=tableName;*/
  --c3，主鍵遊標，雖然主鍵只能有一個，但為統一起見還是用了遊標
  v_add_primary_sql clob;
  cursor primary_cols is
    select distinct tmp.TABLE_NAME,
                    tmp.INDEX_NAME,
                    to_char(wm_concat(tmp.COLUMN_NAME)
                            over(partition by tmp.TABLE_NAME)) as pri_cols
      from (select i.TABLE_NAME,
                  i.INDEX_NAME,
                  i.COLUMN_NAME,
                  i.COLUMN_POSITION
              from user_ind_columns i
              join user_constraints c
                on i.INDEX_NAME = c.index_name
            where c.CONSTRAINT_TYPE = 'P'
              and i.TABLE_NAME = tableName
            order by 1, 2, 4) tmp;
  --c4，唯一約束遊標
  v_add_unique_sql clob;
  cursor unique_cons is
    select distinct tmp.TABLE_NAME,
                    tmp.INDEX_NAME,
                    to_char(wm_concat(tmp.COLUMN_NAME)
                            over(partition by tmp.TABLE_NAME,
                                tmp.INDEX_NAME)) as uni_cols,
                    replace(to_char(wm_concat(tmp.COLUMN_NAME)
                                    over(partition by tmp.INDEX_NAME)),
                            ',',
                            '_') as new_indexname
      from (select i.TABLE_NAME,
                  i.INDEX_NAME,
                  i.COLUMN_NAME,
                  i.COLUMN_POSITION
              from user_ind_columns i
              join user_constraints c
                on i.INDEX_NAME = c.index_name
            where c.CONSTRAINT_TYPE = 'U'
              and i.TABLE_NAME = tableName
            order by 1, 2, 4) tmp;
  --c5，非唯一非主鍵索引遊標
  v_create_index_sql clob;
  cursor normal_indexes is
    select distinct tmp.TABLE_NAME,
                    tmp.INDEX_NAME,
                    to_char(wm_concat(tmp.COLUMN_NAME)
                            over(partition by tmp.TABLE_NAME,
                                tmp.INDEX_NAME)) as index_cols
      from (select i.TABLE_NAME,
                  i.INDEX_NAME,
                  c.COLUMN_NAME,
                  c.COLUMN_POSITION
              from user_indexes i
              join user_ind_columns c
                on i.INDEX_NAME = c.INDEX_NAME
            where index_type = 'NORMAL'
              and i.TABLE_NAME = tableName
              and i.uniqueness = 'NONUNIQUE'
            order by 1, 2, 4) tmp;
  --c6，不是由唯一約束產生的唯一索引遊標
  v_create_unique_index_sql clob;
  cursor unique_cols is
    select distinct tmp.TABLE_NAME,
                    tmp.INDEX_NAME,
                    to_char(wm_concat(tmp.COLUMN_NAME)
                            over(partition by tmp.TABLE_NAME,
                                tmp.INDEX_NAME)) as index_cols
      from (select u_i.TABLE_NAME,
                  u_i.INDEX_NAME,
                  c.COLUMN_NAME,
                  c.COLUMN_POSITION
              from (select *
                      from user_indexes
                    where table_name = tableName
                      and index_type = 'NORMAL'
                      and index_name not in
                          (select index_name
                              from user_constraints
                            where table_name = tableName
                              and index_name is not null)) u_i
              join user_ind_columns c
                on u_i.INDEX_NAME = c.INDEX_NAME
            where u_i.TABLE_NAME = tableName
              and u_i.uniqueness = 'UNIQUE'
            order by 1, 2, 4) tmp;
begin
  --建立表結構
  v_create_table_sql := 'create table ' || newTable || ' as select * from ' ||
                        tableName || ' where 1=2';
  execute immediate v_create_table_sql;
  --添加預設值
  for c1 in default_cols loop
    v_add_default_sql := 'alter table ' || newTable || ' modify ' ||
                        c1.column_name || ' default ' || c1.DATA_DEFAULT;
    execute immediate v_add_default_sql;
  end loop;
  --添加非空約束
  /*  for c2 in notnull_cols loop
    v_add_notnull_sql:='alter table '||newTable||' modify '||c2.column_name||' not null';
    execute immediate v_add_notnull_sql;
  end loop;*/
  --添加主鍵約束
  for c3 in primary_cols loop
    v_add_primary_sql := 'alter table ' || newTable ||
                        ' add constraint Pk_' || newTable ||
                        ' primary key(' || c3.pri_cols || ')';
    execute immediate v_add_primary_sql;
  end loop;
  --添加唯一性限制式，由於原約束名可能由於建立約束的方法不同，存在系統自訂的名字，因此這裡直接命名唯一約束
  for c4 in unique_cons loop
    v_add_unique_sql := 'alter table ' || newTable || ' add constraint U_' ||
                        c4.new_indexname || ' unique(' || c4.uni_cols || ')';
    execute immediate v_add_unique_sql;
  end loop;
  --建立非主鍵且非唯一的索引，索引名字直接繼承自主表，尾碼dateStr以示不同
  for c5 in normal_indexes loop
    v_create_index_sql := 'create index ' || c5.index_name || '_' ||
                          dateStr || ' on ' || newTable || '(' ||
                          c5.index_cols || ')';
    execute immediate v_create_index_sql;
  end loop;
  --建立不是由於約束產生的唯一索引
  for c6 in unique_cols loop
    v_create_unique_index_sql := 'create unique index ' || c6.index_name || '_' ||
                                dateStr || ' on ' || newTable || '(' ||
                                c6.index_cols || ')';
    execute immediate v_create_unique_index_sql;
  end loop;
end createTable;
 
/