单向DBLINK导表存储过程SP_DB

最新推荐文章于 2023-07-04 09:59:19 发布

sqlboy-yuzhenc

最新推荐文章于 2023-07-04 09:59:19 发布

阅读量482

点赞数 2

分类专栏： ORACLE PL/SQL ORACLE工具文章标签： sql 数据库大数据

本文链接：https://blog.csdn.net/qq_33445829/article/details/112310690

版权

ORACLE 同时被 3 个专栏收录

21 篇文章 2 订阅

订阅专栏

ORACLE工具

5 篇文章 1 订阅

订阅专栏

PL/SQL

3 篇文章 1 订阅

订阅专栏

应用场景

有2个 ORACLE 数据库，分别为 数据库A 和 数据库B ，可以在 数据库A 建立 数据库B 的 DBLINK ，但是出于安全，禁止在 数据库B 建立 数据库A 的 DBLINK 。在这种情况，需要实现从 数据库A 向 数据库B 导表，并且索引，备注， 主键约束 需要一同导过去。

存储过程实现

以 sys 用户作为 sysdba 登录 数据库B ，在 数据库B中 创建一个 tool（这里用以tool为例）用户，赋予建连接、建表、建视图、建存储过程、查询表 的权限；

--建立tool用户，密码为tool13579
CREATE USER "TOOL" IDENTIFIED BY "tool13579"
--默认表空间为 TS_ALLCOM_DATA ，这里根据实际情况设置默认表空间
DEFAULT TABLESPACE "TS_ALLCOM_DATA"
--表空间限额
QUOTA 100G ON "TS_ALLCOM_DATA";

--授权
GRANT CREATE SESSION,CREATE ANY TABLE,CREATE ANY VIEW,CREATE ANY PROCEDURE,SELECT ANY TABLE TO tool;

以 tool 用户登录 数据库B ；
创建存储过程 droptable ，该存储过程是用来删除表的；

CREATE OR REPLACE PROCEDURE droptable (
  tablename VARCHAR2
)
AS
  num NUMBER;
BEGIN
  SELECT
    COUNT(1)
  INTO num
  FROM user_tables
  WHERE table_name = upper(tablename);

  IF num <> 0 THEN
    EXECUTE IMMEDIATE 'DROP TABLE "' || upper(tablename) || '"';
  END IF;

END;
/

创建 dropview 存储过程，该存储过程是用来删除视图的；

CREATE OR REPLACE PROCEDURE dropview (
  viewname VARCHAR2
)
AS
  num NUMBER;
BEGIN
  SELECT
    COUNT(1)
  INTO num
  FROM user_views
  WHERE view_name = upper(viewname);

  IF num <> 0 THEN
    EXECUTE IMMEDIATE 'DROP VIEW "' || upper(viewname) || '"';
  END IF;

END;
/

创建 dropindex 存储过程，该存储过程是用来删除索引的；

CREATE OR REPLACE PROCEDURE dropindex (
  indexname VARCHAR2
)
AS
  num NUMBER;
BEGIN
  SELECT
    COUNT(1)
  INTO num
  FROM user_indexes
  WHERE index_name = upper(indexname);

  IF num <> 0 THEN
    EXECUTE IMMEDIATE 'DROP INDEX "' || upper(indexname) || '"';
  END IF;

END;
/

创建 createobject 存储过程，该存储过程用来执行CREATE动作的动态SQL；

CREATE OR REPLACE PROCEDURE createobject(create_sql VARCHAR2) AS
BEGIN

  --这里只允许create动作，不允许其他动态sql
  IF substr(upper(REPLACE(REPLACE(create_sql,chr(10),''),chr(32),'')),1,6) <> 'CREATE' THEN
    raise_application_error(-20200,'请检查CREATE动作是否正确！');
  END IF;

  EXECUTE IMMEDIATE create_sql;

  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('执行语句出错，请检查CREATE语句是否正确，注意最后不需要分号！');
END;
/

创建 commentobject 存储过程，该存储过程用来执行加备注的动态SQL；

CREATE OR REPLACE PROCEDURE commentobject(comment_sql VARCHAR2) AS
BEGIN

  --这里只允许COMMENTON动作，不允许其他动态sql
  IF substr(upper(REPLACE(REPLACE(comment_sql,chr(10),''),chr(32),'')),1,9) <> 'COMMENTON' THEN
    raise_application_error(-20200,'请检查COMMENT动作是否正确！');
  END IF;

  EXECUTE IMMEDIATE comment_sql;

  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('执行语句出错，请检查COMMENT语句是否正确，注意最后不需要分号！');
END;
/

创建 alterobject 存储过程，该存储过程用来执行alter动作的动态SQL；

CREATE OR REPLACE PROCEDURE alterobject(alter_sql VARCHAR2) AS
BEGIN

  --这里只允许ALTER动作，不允许其他动态sql
  IF substr(upper(REPLACE(REPLACE(alter_sql,chr(10),''),chr(32),'')),1,5) <> 'ALTER' THEN
    raise_application_error(-20200,'请检查ALTER动作是否正确！');
  END IF;

  EXECUTE IMMEDIATE alter_sql;

  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('执行语句出错，请检查建表语句是否正确，注意最后不需要分号！');
END;
/

以同样的方式在 数据库A 创建 tool （以此为例）用户并登陆；
建立 数据库B 的DBLINK;

--这里的“数据库连接串”根据实际情况填写
CREATE DATABASE LINK dblink_a
CONNECT TO tool IDENTIFIED BY tool13579
USING '数据库连接串'

创建序列 seq_tmp_db ;

CREATE SEQUENCE seq_tmp_db;

创建 droptable 存储过程；

CREATE OR REPLACE PROCEDURE droptable (
  tablename VARCHAR2
)
AS
  num NUMBER;
BEGIN
  SELECT
    COUNT(1)
  INTO num
  FROM user_tables
  WHERE table_name = upper(tablename);

  IF num <> 0 THEN
    EXECUTE IMMEDIATE 'DROP TABLE "' || upper(tablename) || '"';
  END IF;

END;
/

创建 sp_db 存储过程,存储过程参数的默认值根据实际情况定；

CREATE OR REPLACE PROCEDURE sp_db(
  tname VARCHAR2,                               --表名
  owner_1 VARCHAR2,                             --属主1，要导表的属主
  tname_new VARCHAR2 := NULL,                   --导表后若需改名，新名称
  dblinkname VARCHAR2 := 'DBLINK_A',            --dblink名称
  owner_2 VARCHAR2 := 'TOOL',                   --dblink属主
  lx VARCHAR2 := 'TABLE'                        --TABLE   OR   VIEW
)
/*
  若数据库之间存在编码差异，请先扩大相关字段的长度后再导表
  导表导到dblink的属主的默认表空间
  导视图是指 导“建视图的语句”
  如果实际是传参是视图名，但是类型参数是表类型，则导过去后是导表时查出的那个视图的所有数据，导过去后是表
*/
IS
  v_sql VARCHAR2(32767);
  p_tname VARCHAR2(100) := upper(tname);
  p_owner_1 VARCHAR2(100) := upper(owner_1);
  p_owner_2 VARCHAR2(100) := upper(owner_2);
  p_dblinkname VARCHAR2(100) := upper(dblinkname);
  p_tname_new VARCHAR2(100) := upper(tname_new);
  p_user VARCHAR2(100);    --当前登录用户
  p_pk_cons_name VARCHAR2(100);  --主键名称
  p_pk_cons_col VARCHAR2(200);  --主键列
  table_name VARCHAR2(100);
  existbj NUMBER;
  p_dbid NUMBER := seq_tmp_db.nextval;
  comments_clname all_col_comments.column_name%TYPE;
  comments_cmname all_col_comments.comments%TYPE;
  pkbj VARCHAR2(30);         --是否有主键约束
  create_sql VARCHAR2(32767);    --建表语句
  p_tspace VARCHAR2(30); --表空间
  errorbj NUMBER := 0;
BEGIN

  --判断导表类型是否正确
  IF lx NOT IN ('TABLE','VIEW') THEN
    raise_application_error(-20200,'类型参数错误，类型只能是TABLE或VIEW!');
  END IF;

  --判断属主1是否存在
  SELECT
    COUNT(1)
  INTO existbj
  FROM all_users
  WHERE username = p_owner_1;

  IF existbj = 0 THEN
    raise_application_error(-20201,'表属主不存在，请检查属主是否输入正确!');
  END IF;
  
  --当前登录用户
  p_user := USER;
  
  --检查dblink是否存在
  SELECT
    COUNT(1)
  INTO existbj
  FROM all_db_links
  WHERE owner IN (p_user,'PUBLIC')
    AND db_link = p_dblinkname;

  IF existbj = 0 THEN
    raise_application_error(-20203,'远程连接名无效！请检查远程连接名称是否正确！');
  END IF;
  
  --检查dblink的属主是否存在
  EXECUTE IMMEDIATE q'[
    SELECT
      COUNT(1)
    FROM all_users@"]' || p_dblinkname || q'["
    WHERE username = ']' || p_owner_2 || q'['
  ]' INTO existbj;

  IF existbj = 0 THEN
    raise_application_error(-20204,'dblink的属主存在！');
  END IF;
  
  --检查导入的属主是否存在
  EXECUTE IMMEDIATE q'[
    SELECT
      COUNT(1)
    FROM all_users@"]' || p_dblinkname || q'["
    WHERE username = ']' || p_owner_2 || q'['
  ]' INTO existbj;

  IF existbj = 0 THEN
    raise_application_error(-20205,'导入表的属主不存在！');
  END IF;
  
  --检查createobject存储过程是否存在
  EXECUTE IMMEDIATE q'[
    SELECT
      COUNT(1)
    FROM all_objects@"]' || p_dblinkname || q'["
    WHERE object_name = 'CREATEOBJECT'
      AND object_type = 'PROCEDURE'
      AND owner = ']' || p_owner_2 || q'['
  ]' INTO existbj;
  IF existbj = 0 THEN
    raise_application_error(-20206,'导入库的CREATEOBJECT存储过程不存在！');
  END IF;
  
  --检查droptable存储过程是否存在
  EXECUTE IMMEDIATE q'[
    SELECT
      COUNT(1)
    FROM all_objects@"]' || p_dblinkname || q'["
    WHERE object_name = 'DROPTABLE'
      AND object_type = 'PROCEDURE'
      AND owner = ']' || p_owner_2 || q'['
  ]' INTO existbj;
  IF existbj = 0 THEN
    raise_application_error(-20207,'导入库的DROPTABLE存储过程不存在！');
  END IF;
  
  --检查dropview存储过程是否存在
  EXECUTE IMMEDIATE q'[
    SELECT
      COUNT(1)
    FROM all_objects@"]' || p_dblinkname || q'["
    WHERE object_name = 'DROPVIEW'
      AND object_type = 'PROCEDURE'
      AND owner = ']' || p_owner_2 || q'['
  ]' INTO existbj;
  IF existbj = 0 THEN
    raise_application_error(-20208,'导入库的DROPVIEW存储过程不存在！');
  END IF;
  
  --检查alterobject存储过程是否存在
  EXECUTE IMMEDIATE q'[
    SELECT
      COUNT(1)
    FROM all_objects@"]' || p_dblinkname || q'["
    WHERE object_name = 'ALTEROBJECT'
      AND object_type = 'PROCEDURE'
      AND owner = ']' || p_owner_2 || q'['
  ]' INTO existbj;
  IF existbj = 0 THEN
    raise_application_error(-20209,'导入库的ALTEROBJECT存储过程不存在！');
  END IF;
  
  --检查commentobject存储过程是否存在
  EXECUTE IMMEDIATE q'[
    SELECT
      COUNT(1)
    FROM all_objects@"]' || p_dblinkname || q'["
    WHERE object_name = 'COMMENTOBJECT'
      AND object_type = 'PROCEDURE'
      AND owner = ']' || p_owner_2 || q'['
  ]' INTO existbj;
  IF existbj = 0 THEN
    raise_application_error(-20210,'导入库的COMMENTOBJECT存储过程不存在！');
  END IF;
  
  --检查dropindex存储过程是否存在
  EXECUTE IMMEDIATE q'[
    SELECT
      COUNT(1)
    FROM all_objects@"]' || p_dblinkname || q'["
    WHERE object_name = 'DROPINDEX'
      AND object_type = 'PROCEDURE'
      AND owner = ']' || p_owner_2 || q'['
  ]' INTO existbj;
  IF existbj = 0 THEN
    raise_application_error(-20211,'导入库的DROPINDEX存储过程不存在！');
  END IF;
  
  --处理新建表表名
  IF tname_new IS NULL THEN
    table_name := p_tname;
  ELSE
    table_name := p_tname_new;
  END IF;
  
  --提示 “若存在相同表名或视图名，将会被覆盖！”，可以在这里做备份
  --这里暂时没做备份处理
  dbms_output.put_line('若存在相同表名或视图名，将会被覆盖！');
  dbms_output.put_line('导表开始：' || to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
  
  --删除已存在的同名表
  v_sql := q'[
    CALL droptable@"]' || p_dblinkname || q'["(']' || table_name || q'[')
  ]';
  EXECUTE IMMEDIATE v_sql;
  
  --删除已存在的同名视图
  v_sql := q'[
    CALL dropview@"]' || p_dblinkname || q'["(']' || table_name || q'[')
  ]';
  EXECUTE IMMEDIATE v_sql;
  
  IF lx = 'TABLE' THEN
    --获取建表语句
    droptable('DB_' || p_dbid || '_TMP');
    v_sql := q'[
      CREATE TABLE DB_]' || p_dbid || q'[_TMP AS SELECT * FROM "]' || p_owner_1 || q'["."]' || p_tname || q'[" WHERE 1 = 2
    ]';

    EXECUTE IMMEDIATE v_sql;

    --表空间
    SELECT tablespace_name
    INTO p_tspace
    FROM all_tables
    WHERE owner = p_user
      AND table_name = q'[DB_]' || p_dbid || q'[_TMP]';
    
    
    --建表语句  默认表空间 不设置表空间
    SELECT
      REPLACE(
        REPLACE(
          dbms_metadata.get_ddl('TABLE',q'[DB_]' || p_dbid || q'[_TMP]'),
          '"' || p_user || '"."DB_' || p_dbid || '_TMP"','"' || p_owner_2 || '"."' || table_name || '"'
        ),'TABLESPACE "' || p_tspace || '"',''
      )
    INTO create_sql
    FROM dual;
    
    --删除临时表
    droptable('DB_' || p_dbid || '_TMP');
    
    --建表
    v_sql := q'[
      CALL createobject@"]' || p_dblinkname || q'["(q'/]' || create_sql || q'[/')
    ]';

    EXECUTE IMMEDIATE v_sql;
    
    --主键约束
    BEGIN
      SELECT
        MAX(constraint_name)
      INTO pkbj
      FROM all_cons_columns
      WHERE table_name = p_tname
        AND owner = p_owner_1;

      IF pkbj IS NOT NULL THEN
        SELECT
          constraint_name,
          listagg('"'||column_name||'"',',')within GROUP(ORDER BY position)
        INTO p_pk_cons_name,p_pk_cons_col
        FROM all_cons_columns
        WHERE table_name = p_tname AND owner = p_owner_1
        GROUP BY constraint_name;
        
        --解决命名冲突问题（只能解决一次）
        EXECUTE IMMEDIATE q'[
          SELECT 
            COUNT(1) 
          FROM all_constraints@"]' || p_dblinkname || q'[" 
          WHERE constraint_name = ']' || p_pk_cons_name || q'[' 
            AND owner = ']' || p_owner_2 || q'['
        ]' INTO existbj;
        
        IF existbj > 0 THEN
          p_pk_cons_name := '"PK_' || p_pk_cons_name || '"';
        ELSE
          p_pk_cons_name := '"' || p_pk_cons_name || '"';
        END IF;
        
        v_sql := q'[
          ALTER TABLE "]' || p_owner_2 || q'["."]' || table_name || q'[" ADD CONSTRAINT ]' || p_pk_cons_name || q'[ PRIMARY KEY (]' || p_pk_cons_col || q'[)
        ]';
        
        v_sql := q'[
          CALL alterobject@"]' || p_dblinkname || q'["(q'/]' || v_sql || q'[/')
        ]';
        
        EXECUTE IMMEDIATE v_sql;
        
      END IF;
      
      --主键约束出现问题不影响导表，不做处理
      --错误标记加1
      EXCEPTION WHEN OTHERS THEN
        errorbj := errorbj + 1;
    END;
    
    --备注信息
    FOR i IN (
      SELECT
        column_name comments_clname,
        comments comments_cmname
      FROM all_col_comments
      WHERE owner = p_owner_1
        AND table_name = p_tname
        AND comments IS NOT NULL
    ) LOOP
    BEGIN
    
      v_sql := q'[
        COMMENT ON COLUMN "]' || p_owner_2 || q'["."]' || table_name || q'["."]' || i.comments_clname || q'[" IS ']' || i.comments_cmname || q'['
      ]';
      
      v_sql := q'[
        CALL commentobject@"]' || p_dblinkname || q'["(q'/]' || v_sql || q'[/')
      ]';
      
      EXECUTE IMMEDIATE v_sql;
      
      --备注信息出现问题不影响导表，不做处理
      --错误标记加1
      EXCEPTION WHEN OTHERS THEN
        errorbj := errorbj + 1;
        
    END;
    END LOOP;
    
    --索引信息
    FOR i IN (
      SELECT index_name,uniqueness,column_name FROM (
        SELECT owner,index_name,index_type,table_owner,table_name,uniqueness,tablespace_name,
          listagg(column_name,',')within GROUP(ORDER BY column_position) column_name
        FROM (
          SELECT a.owner,'"'||a.index_name||'"' index_name,a.index_type,a.table_owner,a.table_name,a.uniqueness,'"'||b.column_name||'"' column_name,a.tablespace_name,b.column_position
          FROM all_indexes a, all_ind_columns b
          WHERE a.owner = b.index_owner AND a.index_name = b.index_name AND a.table_owner = p_owner_1 AND a.table_name = p_tname)
      GROUP BY owner,index_name,index_type,table_owner,table_name,uniqueness,tablespace_name)
      WHERE index_name NOT IN (SELECT constraint_name FROM all_cons_columns WHERE table_name = p_tname AND owner = p_owner_1)
    ) LOOP
    BEGIN
      
      --如果是唯一索引
      IF i.uniqueness = 'UNIQUE' THEN
        v_sql := q'[
          CREATE UNIQUE INDEX "]' || p_owner_2 || q'[".]' || i.index_name || q'[ ON "]' || p_owner_2 || q'["."]' || table_name || q'[" (]' || i.column_name || q'[)
        ]';
      ELSE
        v_sql := q'[
          CREATE INDEX "]' || p_owner_2 || q'[".]' || i.index_name || q'[ ON "]' || p_owner_2 || q'["."]' || table_name || q'[" (]' || i.column_name || q'[)
        ]';
      END IF;

      v_sql := q'[
        CALL createobject@"]' || p_dblinkname || q'["(q'/]' || v_sql || q'[/')
      ]';

      EXECUTE IMMEDIATE q'[
        CALL dropindex@"]' || p_dblinkname || q'["(q'/]' || i.index_name || q'[/')
      ]';
      EXECUTE IMMEDIATE v_sql;
    
      --索引信息出现问题不影响导表，不做处理
      --错误标记加1
      EXCEPTION WHEN OTHERS THEN
        errorbj := errorbj + 1;
    END;
    END LOOP;
    
    --开始插入数据
    BEGIN
      EXECUTE IMMEDIATE '
        INSERT INTO "' || p_owner_2 || '"."' || table_name || '"@"' || p_dblinkname || '" SELECT * FROM "' || p_owner_1 || '"."' || p_tname || '"';
      COMMIT;
      
      dbms_output.put_line('导表成功：' || to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
      
      EXCEPTION 
        WHEN OTHERS THEN
          ROLLBACK;
          dbms_output.put_line('数据导入失败!');
          dbms_output.put_line(SQLERRM);
          
    END;
    
  ELSIF lx = 'VIEW' THEN
  BEGIN
    --获取建视图语句
    SELECT text
    INTO create_sql
    FROM all_views 
    WHERE view_name = p_tname 
      AND owner = p_owner_1;
    
    create_sql := q'[CREATE OR REPLACE VIEW "]' || table_name || q'[" AS ]' || create_sql;
    
    v_sql := q'[
      CALL createobject@"]' || p_dblinkname || q'["(q'/]' || create_sql || q'[/')
    ]';
    
    EXECUTE IMMEDIATE v_sql;
    
    dbms_output.put_line('导视图成功：' || to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
    
    EXCEPTION WHEN OTHERS THEN
      dbms_output.put_line('导视图失败：' || SQLERRM || '-' || SQLCODE);
      
  END;
  END IF;
  
  dbms_output.put_line('导表期间发生无关错误数：' || errorbj);

END;
/

存储过程 sp_db 授权给其他用户（这里以apl为例）使用；

GRANT EXECUTE,DEBUG ON sp_db TO apl;

测试

打开命令窗口；

SQL> set serverout on

当数据库之间存在编码差异时，执行存储过程，这是可能数据长度就够了，就需要在导表前扩大表中字段的长度，如果VARCHAR2的最大值都不够，建议转成 CLOB 类型；

SQL> EXEC sp_db('yb_59791_qd_bdbhs','apl');

数据库之间存在编码差异

当数据库之间编码一致时，执行存储过程；

SQL> EXEC sp_db('yb_59791_qd_bdbhs','apl',dblinkname => 'callfund_tool',owner_2 => 'TOOL');

数据库之间编码一致

创建视图 tmp；

CREATE VIEW tmp AS 
SELECT 1 ID FROM dual;

导视图 tmp；

SQL> EXEC sp_db('tmp',USER,lx => 'VIEW');

导视图

sqlboy-yuzhenc

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
1
评论
单向DBLINK导表存储过程SP_DB

应用场景有2个 ORACLE 数据库，分别为数据库A 和数据库B ，可以在数据库A 建立数据库B 的 DBLINK ，但是出于安全，禁止在数据库B 建立数据库A 的 DBLINK 。在这种情况，需要实现从数据库A 向数据库B 导表，并且索引，备注，主键约束需要一同导过去。存储过程实现以 sys 用户作为 sysdba 登录数据库B ，在数据库B中创建一个 tool（这里用以tool为例）用户，赋予建连接、建表、建视图、建存储过程、查询表的权限；--建立too
复制链接

扫一扫