Oracle完全复制表结构的存储过程

于 2023-07-26 14:08:57 发布
阅读量261 收藏
点赞数
分类专栏： 数据库(mysql/Oracle/sqlserver) 文章标签： oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/richard2t/article/details/131909571
版权

处理Oracle分表的问题，需要为程序创建一个自动分表的存储过程，需要保证所有表结构，约束，索引等等一致。
说明：此Oracle为19c版本。

create or replace procedure COPYTABLE									      --存储过程名称
(tableName in varchar2, newTable in varchar2, dateStr in varchar2)			  --相关参数
  authid current_user as							                          --调用者权限
  v_drop_table_sql varchar2(200);                                             --
  v_create_table_sql varchar2(4000);                                          --
  v_add_default_sql varchar2(4000);                                           --
  i_table_count number;                                                       --
  cursor default_cols is                                                      --默认值游标
    select COLUMN_NAME, DATA_DEFAULT                                          --
      from user_tab_columns                                                   --
     where DATA_DEFAULT is not null                                           --
       and TABLE_NAME = tableName;                                            --
  v_add_primary_sql clob;                                                     --
  cursor primary_cols is                                                      --主键游标，虽然主键只能有一个，但为统一起见还是用了游标
    select distinct tmp.TABLE_NAME,                                           --
                    tmp.INDEX_NAME,                                           --
                    listagg(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;                                           --
  v_add_unique_sql clob;                                                      --唯一约束游标
  cursor unique_cons is                                                       --
    select distinct tmp.TABLE_NAME,                                           --
                    tmp.INDEX_NAME,                                           --
                    listagg(tmp.COLUMN_NAME)                                  --
                            over(partition by tmp.TABLE_NAME,                 --
                                 tmp.INDEX_NAME)) as uni_cols,                --
                    replace(listagg(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;                                           --
  v_create_index_sql clob;                                                    --非唯一非主键索引游标
  cursor normal_indexes is                                                    --
    select distinct tmp.TABLE_NAME,                                           --
                    tmp.INDEX_NAME,                                           --
                    listagg(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;                                           --
  v_create_unique_index_sql clob;                                             --不是由唯一约束生成的唯一索引游标
  cursor unique_cols is                                                       --
    select distinct tmp.TABLE_NAME,                                           --
                    tmp.INDEX_NAME,                                           --
                    listagg(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                                                                                               --
  execute immediate  'select count(*) from tabs where table_name = '''||newTable||'''' into  i_table_count;                                       --
  if i_table_count > 0 then                                                                         --
      v_drop_table_sql := 'drop table '||newTable||' cascade constraints';                          --
      execute immediate v_drop_table_sql;                                                           --
  end if;                                                                                           --
  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 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;                                                                                         --
  for c5 in normal_indexes loop                                                                     --创建非主键且非唯一的索引，索引名字直接继承自主表，后缀dateStr以示不同
    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 COPYTABLE;                                                                                      --
运维护航员
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
在oracle中复制表结构,Oracle中复制表结构和表数据
weixin_26704651的博客
04-07 1649
一、复制表结构及其数据create table new_table as (select * from old_table);二、只复制表结构create table new_table as (select * from old_table where 1=2);三、只复制表数据如果两个表结构一样：insert into new_table select * from old_table;如果两...
oracle复制存储过程SQL,oracle存储过程迁移达梦心得
weixin_32015301的博客
04-03 433
这几天把项目的存储过程从oracle迁移到了达梦8，记录一下心得。国产数据库做到这样，已经算很了不起了，跟oracle兼容性确实很高。但还是有一些细节没做好，主要是出错提示不友好，另外一个网上的资料也很少，出问题不好定位。(达梦的错误码比较简单，就是一个负数，不像oracle是ora-错误码，在百度时输入一个负数搜索信息，跟一长串错误码搜索效率差很远)准备工作：1 达梦的数据库管理工具默认不显示行...
如何在Oracle中复制表结构和表数据
kkk109A的博客
03-11 7360
1. 复制表结构及其数据： create table table_name_new as select * from table_name_old 2. 只复制表结构： create table table_name_new as select * from table_name_old where 1=2; 或者： create table table_name_new like table_name_old 3. 只复制表数据： 如果两个表结构一样： insert into table_
ORACLE 存储过程 输出数据到 指定文件
qq_29461579的博客
07-23 2284
1 使用dba 角色登录到oracle 赋予用户（hly201907） 使用UTL_FILE 的权限 GRANT EXECUTE ON UTL_FILE TO hly201907; 2 创建工作文件夹（D:/TEMP） 并为它起一个别名 （D_OUTPUT ） create or replace directory D_OUTPUT as 'D:/TEMP'; 3使用dba...
整体拷贝oracle数据库的一种方法实践
门加玉
11-14 1687
前提：最近需要对项目数据库做一次拷贝，也可以理解做一个测试数据库。不是很想用oracle的imp命令，就想把所有的建表语句之类的都保存下来。数据库中有900+表，二十多张视图，十几个序列，一个存储过程（去存储过程化，把存储过程都去掉了），一个函数，所以还是可以尝试的。 第一步：先看一下数据库表字段有多少种类型 SELECT DISTINCT DATA_type FROM USER_TAB_COLS; TIMESTAMP(6) NVARCHAR2 NUMBER CHAR CLOB DATE VARCHAR2
oracle数据库中复制表结构图,oracle复制表数据，复制表结构
weixin_34870207的博客
04-12 248
1.不同用户之间的表数据复制对于在一个数据库上的两个用户A和B，假如需要把A下表old的数据复制到B下的new，请使用权限足够的用户登入sqlplus：insert into B.new(select * from A.old);如果需要加条件限制，比如复制当天的A.old数据insert into B.new(select * from A.old where date=GMT);蓝色斜线处为选...
oracle 存储过程和触发器复制数据
09-11
Oracle数据库中的存储过程和触发器是数据库编程的重要组成部分，它们被广泛用于数据处理、业务逻辑控制以及自动化数据复制等任务。在这个场景中，我们看到如何使用存储过程来复制数据，特别是涉及备案信息的表之间的...
SqlServer表结构转Oracle
09-24
总之，"SqlServer表结构转Oracle"是一个实用的工具，它简化了跨数据库系统迁移表结构的过程，减少了手动操作的复杂性和错误风险。在数据库迁移过程中，理解这两个系统的差异并借助专业工具，可以大大提高迁移的效率...
DB2和ORACLE数据库导出表结构空间和存储过程脚本。
12-11
它可能调用了上述提到的DB2和Oracle的命令行工具，以导出表结构、存储过程和表空间信息。用户运行这个文件时，应该会生成相应的DDL和PL/SQL脚本文件。 3. **CODE**: 这可能包含具体的SQL脚本或存储过程代码，用于更...
Oracle存储过程之数据库中获取数据实例
09-14
首先，如果表A和表B具有相同的列结构，你可以使用简单的`INSERT INTO SELECT`语句来一次性将所有数据或指定列的数据从一个表复制到另一个表。例如： ```sql -- 全部列 INSERT INTO B SELECT * FROM A; -- 指定列 ...
oracle复制表结构和复制表数据语句分享
01-19
1. 复制表结构及其数据： 代码如下:create table table_name_new as select * from table_name_old2. 只复制表结构： 代码如下:create table table_name_new as select * from table_name_old where 1=2;或者： 代码如下:create table table_name_new like table_name_old3. 只复制表数据： 如果两个表结构一样： 代码如下:insert into table_name_new select * from table_name_o
Oracle存储过程导入导出数据
08-28
Oracle存储过程导入导出数据,希望大家共同提高。
oracle存储过程学习经典入门
03-05
Oracle存储过程是数据库管理系统中的一种重要编程工具，它允许开发者编写复杂的SQL和PL/SQL代码块，以便在数据库中执行多次或按需执行。Oracle存储过程的学习经典入门涉及多个核心概念和技术，包括性能调优、数据库...
oracle复制存储过程SQL,Oracle完全复制表结构的存储过程
weixin_39955700的博客
04-03 200
最近在处理一个Oracle分表的问题时，需要为程序创建一个自动分表的存储过程，需要保证所有表结构，约束，索引等等一致，此外视图，存储过程，权限等等问题暂不用考虑。在MySQL中，创建分表的存储过程，相当简单：create table if not exists like ;即可，约束，索引一应俱全。但是在Oracle中貌似没有，所以只能自己写，需要考虑的情况比较多，脚本如下：CREATE OR ...
oracle 复制表方法,Oracle 数据库之表的复制
weixin_26736139的博客
04-04 1万+
Oracle 数据库 表的复制方式一：复制表结构及表数据CREATE TABLE table_new AS SELECT * FROM TABLE_old ; 方式二：只复制表结构CREATE TABLE table_new AS SELECT * FROM table_old WHERE 1 = 2 ;[注]: 写 WHERE 1 = 2 是让查询的结果为空，若写成 WHERE 1 = 1 ，...
oracle复制表数据，复制表结构
热门推荐
Visant Blog
03-19 1万+
在oracle中复制表结构和表数据：   1、复制表结构及数据：   create table new_table as select * from old_table   2、只复制表结构：   create table new_table 
oracle中存储过程复制表,Oracle完全复制表结构的存储过程
weixin_29081703的博客
04-05 324
最近在处理一个分表的问题时，需要为程序创建一个自动分表的存储过程，需要保证所有表结构，约束，索引等等一致，此外视图，存储过程，权限等等问题暂不用考虑。在Mysql中，创建分表的存储过程，相当简单：create table if not exists like ;即可，约束，索引一应俱全。但是在Oracle中貌似没有，所以只能自己写，需要考虑的情况比较多，脚本如下：CREATE OR REPLAC...
CREATE TABLE A LIKE B
tianlianchao1982的专栏
09-28 2161
CREATE TABLE A LIKE B 此种方式在将表B复制到A时候会将表B完整的字段结构和索引复制到表A中来。 2. CREATE TABLE A AS SELECT x,x,x,xx FROM B LIMIT 0 此种方式只会将表B的字段结构复制到表A中来，但不会复制表B中的索引到表A中来。这种方式比较灵活可以在复制原表表结构的同时指定要复制哪些字
oracle直接表结构和索引完全相同的表
最新发布
07-13
您好！如果您想创建一个与现有表的结构和索引完全相同的表，可以使用以下方法： 1. 使用 CREATE TABLE AS SELECT（CTAS）语句： ``` CREATE TABLE new_table AS SELECT * FROM existing_table WHERE 1=0; ``` 这将创建一个名为 "new_table" 的新表，其结构与 "existing_table" 完全相同，但不包含任何数据。 2. 使用 "LIKE" 子句： ``` CREATE TABLE new_table LIKE existing_table; ``` 这将创建一个名为 "new_table" 的新表，其结构与 "existing_table" 完全相同，但不包含任何数据。 无论您选择哪种方法，新表都将具有与现有表相同的列、数据类型、长度和约束。但请注意，索引、触发器、存储过程等其他对象不会被复制到新表中。如果需要复制这些对象，请单独进行复制或使用相应的工具。 希望这可以帮助到您！如果您有其他问题，请随时提问。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值