查看: 2114|回复: 0

[Oracle数据库] Oracle完全复制表结构的存储过程

发表于 2018-1-10 08:00:01

最近在处理一个分表的问题时,需要为程序创建一个自动分表的存储过程,需要保证所有表结构,约束,索引等等一致,此外视图,存储过程,权限等等问题暂不用考虑。

在Mysql中,创建分表的存储过程,相当简单:create table if not exists like ;即可,约束,索引一应俱全。

但是在Oracle中貌似没有,所以只能自己写,需要考虑的情况比较多,脚本如下:

  1. CREATE OR REPLACE PROCEDURE CREATETABLE(tableName in varchar2,
  2. dateStr in varchar2)
  3. AUTHID CURRENT_USER as
  4. newTable varchar2(32) := tableName || '_' || dateStr;
  5. v_create_table_sql clob;
  6. --c1,默认值游标
  7. v_add_default_sql clob;
  8. cursor default_cols is
  9. select COLUMN_NAME, DATA_DEFAULT
  10. from user_tab_columns
  11. where DATA_DEFAULT is not null
  12. and TABLE_NAME = tableName;
  13. --c2 主键的not null不会继承,但not null约束的会继承,因此c2全部注释
  14. /*v_add_notnull_sql clob;
  15. cursor notnull_cols is select COLUMN_NAME from user_tab_columns t where t.NULLABLE='N' and and t.TABLE_NAME=tableName;*/
  16. --c3,主键游标,虽然主键只能有一个,但为统一起见还是用了游标
  17. v_add_primary_sql clob;
  18. cursor primary_cols is
  19. select distinct tmp.TABLE_NAME,
  20. tmp.INDEX_NAME,
  21. to_char(wm_concat(tmp.COLUMN_NAME)
  22. over(partition by tmp.TABLE_NAME)) as pri_cols
  23. from (select i.TABLE_NAME,
  24. i.INDEX_NAME,
  25. i.COLUMN_NAME,
  26. i.COLUMN_POSITION
  27. from user_ind_columns i
  28. join user_constraints c
  29. on i.INDEX_NAME = c.index_name
  30. where c.CONSTRAINT_TYPE = 'P'
  31. and i.TABLE_NAME = tableName
  32. order by 1, 2, 4) tmp;
  33. --c4,唯一约束游标
  34. v_add_unique_sql clob;
  35. cursor unique_cons is
  36. select distinct tmp.TABLE_NAME,
  37. tmp.INDEX_NAME,
  38. to_char(wm_concat(tmp.COLUMN_NAME)
  39. over(partition by tmp.TABLE_NAME,
  40. tmp.INDEX_NAME)) as uni_cols,
  41. replace(to_char(wm_concat(tmp.COLUMN_NAME)
  42. over(partition by tmp.INDEX_NAME)),
  43. ',',
  44. '_') as new_indexname
  45. from (select i.TABLE_NAME,
  46. i.INDEX_NAME,
  47. i.COLUMN_NAME,
  48. i.COLUMN_POSITION
  49. from user_ind_columns i
  50. join user_constraints c
  51. on i.INDEX_NAME = c.index_name
  52. where c.CONSTRAINT_TYPE = 'U'
  53. and i.TABLE_NAME = tableName
  54. order by 1, 2, 4) tmp;
  55. --c5,非唯一非主键索引游标
  56. v_create_index_sql clob;
  57. cursor normal_indexes is
  58. select distinct tmp.TABLE_NAME,
  59. tmp.INDEX_NAME,
  60. to_char(wm_concat(tmp.COLUMN_NAME)
  61. over(partition by tmp.TABLE_NAME,
  62. tmp.INDEX_NAME)) as index_cols
  63. from (select i.TABLE_NAME,
  64. i.INDEX_NAME,
  65. c.COLUMN_NAME,
  66. c.COLUMN_POSITION
  67. from user_indexes i
  68. join user_ind_columns c
  69. on i.INDEX_NAME = c.INDEX_NAME
  70. where index_type = 'NORMAL'
  71. and i.TABLE_NAME = tableName
  72. and i.uniqueness = 'NONUNIQUE'
  73. order by 1, 2, 4) tmp;
  74. --c6,不是由唯一约束生成的唯一索引游标
  75. v_create_unique_index_sql clob;
  76. cursor unique_cols is
  77. select distinct tmp.TABLE_NAME,
  78. tmp.INDEX_NAME,
  79. to_char(wm_concat(tmp.COLUMN_NAME)
  80. over(partition by tmp.TABLE_NAME,
  81. tmp.INDEX_NAME)) as index_cols
  82. from (select u_i.TABLE_NAME,
  83. u_i.INDEX_NAME,
  84. c.COLUMN_NAME,
  85. c.COLUMN_POSITION
  86. from (select *
  87. from user_indexes
  88. where table_name = tableName
  89. and index_type = 'NORMAL'
  90. and index_name not in
  91. (select index_name
  92. from user_constraints
  93. where table_name = tableName
  94. and index_name is not null)) u_i
  95. join user_ind_columns c
  96. on u_i.INDEX_NAME = c.INDEX_NAME
  97. where u_i.TABLE_NAME = tableName
  98. and u_i.uniqueness = 'UNIQUE'
  99. order by 1, 2, 4) tmp;
  100. begin
  101. --创建表结构
  102. v_create_table_sql := 'create table ' || newTable || ' as select * from ' ||
  103. tableName || ' where 1=2';
  104. execute immediate v_create_table_sql;
  105. --添加默认值
  106. for c1 in default_cols loop
  107. v_add_default_sql := 'alter table ' || newTable || ' modify ' ||
  108. c1.column_name || ' default ' || c1.DATA_DEFAULT;
  109. execute immediate v_add_default_sql;
  110. end loop;
  111. --添加非空约束
  112. /* for c2 in notnull_cols loop
  113. v_add_notnull_sql:='alter table '||newTable||' modify '||c2.column_name||' not null';
  114. execute immediate v_add_notnull_sql;
  115. end loop;*/
  116. --添加主键约束
  117. for c3 in primary_cols loop
  118. v_add_primary_sql := 'alter table ' || newTable ||
  119. ' add constraint Pk_' || newTable ||
  120. ' primary key(' || c3.pri_cols || ')';
  121. execute immediate v_add_primary_sql;
  122. end loop;
  123. --添加唯一性约束,由于原约束名可能由于创建约束的方法不同,存在系统自定义的名字,因此这里直接命名唯一约束
  124. for c4 in unique_cons loop
  125. v_add_unique_sql := 'alter table ' || newTable || ' add constraint U_' ||
  126. c4.new_indexname || ' unique(' || c4.uni_cols || ')';
  127. execute immediate v_add_unique_sql;
  128. end loop;
  129. --创建非主键且非唯一的索引,索引名字直接继承自主表,后缀dateStr以示不同
  130. for c5 in normal_indexes loop
  131. v_create_index_sql := 'create index ' || c5.index_name || '_' ||
  132. dateStr || ' on ' || newTable || '(' ||
  133. c5.index_cols || ')';
  134. execute immediate v_create_index_sql;
  135. end loop;
  136. --创建不是由于约束生成的唯一索引
  137. for c6 in unique_cols loop
  138. v_create_unique_index_sql := 'create unique index ' || c6.index_name || '_' ||
  139. dateStr || ' on ' || newTable || '(' ||
  140. c6.index_cols || ')';
  141. execute immediate v_create_unique_index_sql;
  142. end loop;
  143. end createTable;
  144. /
复制代码

  




上一篇:英特尔漏洞
下一篇:oracle database link
回复

使用道具 举报