通过 DBLink 访问远端数据库中的数据

您可以通过 DBLink 访问远端数据库的对象，对象包括表、视图、同义词、序列、存储过程、UDF 等。

前提条件

• 请确认已创建 DBLink，创建 DBLink 的详细操作，参见 创建 DBLink。

• 对于 OceanBase V4.2.1 及以上版本，使用 DBLink 功能访问远端 Oracle 数据库时，需要在集群的所有 OBServer 节点上安装并配置 OCI-12.2。此外，对于 OceanBase V4.2.1 以下版本升级到 V4.2.1 及以上版本的场景，也需要您将原来配置的 OCI-11.2 重新配置为 OCI-12.2。

  安装和配置 OCI-12.2 的详细操作，请参见 安装和配置 OCI。

访问远端数据库中表的数据

您可以使用 SELECT... FROM 表名@DBLink 名 语法来访问远端数据库中表的数据，SQL 语句如下：

SELECT select_expr_list 
      FROM table_name@dblink_name 
      WHERE where_conditions;
 
select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]

语句使用说明：

• select_expr_list：指定待查询的表达式或列名，不同的列之间用英文逗号（,）隔开，星号（*）表示所有列。

  • table_name.*：指定从指定的表、视图中选择所有的列。

  • table_alias_name.*：指定表、视图的别名。

  • expr [[AS] column_alias_name]：指定查询列或表达式的别名，AS 为可选。

• table_name@dblink_name：指定待查询的远端数据库的表，多个数据库之间用英文逗号（,）隔开。

• where_conditions：指定筛选条件，查询结果中仅包含满足条件的数据，为可选项。

更多 DBLink 相关的查询语句，参见 SIMPLE SELECT。

通过名为 my_link 的 DBLink 查询远端数据库中表 tbl1 的数据的示例如下：

obclient [SYS]> SELECT * FROM tbl1@my_link;

访问远端数据库中的序列

OceanBase 数据库从 V4.2.1 版本开始，支持通过 DBLink 访问远端数据库（包括 OceanBase 数据库和 Oracle 数据库）中的序列值。

示例如下：

1. 在本地数据库中，创建到远端 OceanBase 数据库 Oracle 租户的 DBLink my_link 和一个数据库表 tbl1。

   • 创建 DBLink my_link

     obclient [SYS]> CREATE DATABASE LINK my_link CONNECT TO ob_user@oracle IDENTIFIED BY ****** HOST 'xx.xx.xx.xx:2881';
     

   • 创建表 tbl1

     obclient [SYS]> CREATE TABLE tbl1 (C1 int,C2 int);
     

2. 在远端的 OceanBase 数据库中，创建序列 seq。

   obclient [SYS]> CREATE SEQUENCE seq START WITH 10000 INCREMENT BY 1 CACHE 50 NOCYCLE;
   

   创建成功后，确认序列的下一个值是 10000。

   obclient [SYS]> SELECT seq.nextval FROM DUAL;
   

   结果如下：

   +---------+
   | NEXTVAL |
   +---------+
   |   10000 |
   +---------+
   1 row in set
   

   更多序列相关的操作，请参见 管理序列。

3. 在本地数据库中，通过 DBLink 查看远端序列的下一个值和当前值。

   • 查询下一个值

     obclient [SYS]> SELECT seq.nextval@my_link FROM DUAL;
     

     查询结果如下：

     +---------+
     | NEXTVAL |
     +---------+
     |   10001 |
     +---------+
     1 row in set
     

   • 查询当前值

     obclient [SYS]> SELECT seq.currval@my_link FROM DUAL;
     

     查询结果如下：

     +---------+
     | CURRVAL |
     +---------+
     |   10001 |
     +---------+
     1 row in set
     

4. 将远端序列的值通过 DBLink 插入到本地数据库的表中。

   obclient [SYS]> INSERT INTO tbl1(C1,C2) VALUES (1,seq.nextval@my_link);
   

   插入成功后，查询表中的数据：

   obclient [SYS]> SELECT * FROM tbl1;
   

   结果如下：

   +------+-------+
   | C1   | C2    |
   +------+-------+
   |    1 | 10002 |
   +------+-------+
   1 row in set
   

需要注意，在某些特殊场景下，OceanBase 数据库为了提升远端数据库对象查询的性能，可能会导致系统无法获取到远端序列的 CURRVAL 最新的值。例如，以下查询语句中，OceanBase 数据库会将整个 SELECT 部分的语句交给远端执行，而本地仅负责拉取数据及插入数据，待语句执行成功后，再通过 SELECT seq.currval#my_link FROM DUAL; 语句查询 CURRVAL 值时，可能无法获取到最新的值。

INSERT INTO local_tbl1 SELECT remote_tbl1.NAME, seq.nextval@my_link FROM remote_tbl1@my_link;

调用远端数据库中的存储过程

OceanBase 数据库支持通过 DBLink 调用远端 Oracle 数据库或 OceanBase Oracle 租户下的存储过程。 当前版本 OceanBase 数据库 Oracle 模式下的 DBLink 支持传输复杂的数据类型。这种类型通常不仅仅是基本类型（如整数、字符串等），还包括嵌套的复杂结构。支持的嵌套方式如下：

• record(basic)：一个记录类型，其中包含基本类型的字段。
• array(basic)：一个数组类型，数组的元素是基本类型。
• array(record(basic))：一个数组类型，数组的元素是记录类型，而记录类型的字段是基本类型。

通过 DBLink 调用远端 OceanBase Oracle 租户中含复杂类型参数的存储过程时，需要注意以下事项：

• 只支持 record(basic),array(basic),array(record(basic)) 复杂类型，且必须定义在直接调用的 package 中。
• 目前不支持 BINARY_DOUBLE 类型作为 array(basic)，array(record(basic)) 方式调用。
• 目前不支持 float/timestamp/timestmap with time zone/timestamp with local time zone 几种类型作为数组类型的出参使用。
• 调用远端原生 OceanBase 数据库存储过程支持 char 类型参数。

通过 DBLink 调用远端 Oracle 数据库中的存储过程前，需要注意以下事项：

• 调用远端原生 Oracle 数据库存储过程不支持 char 类型参数。

• 调用存储过程时，支持以下基础类型的出入参：

  • VARCHAR2、VARCHAR、CHAR 等字符串类型

  • NUMBER、INT、INTEGER、FLOAT、DOUBLE 等数值类型

  • DATE 类型

  • TIMESTAMP 类型

• 调用包存储过程时，支持包类型为以下复合数据类型的出入参：

  • 记录类型

    同时，还要求记录类型的元素类型必须是以下基础类型：

    • VARCHAR2、VARCHAR、CHAR 等字符串类型

    • NUMBER、INT、INTEGER、FLOAT、DOUBLE 等数值类型

    • DATE 类型

    • TIMESTAMP 类型

  但是，需要注意，对于远端 Oracle 数据库为 oracle-12c 及以下版本的场景，包类型必须是出现在包中 Routine 参数位置的类型。如果仅在包中定义，未在包中任何 Routine 参数中使用，则不支持调用。

  • 嵌套表、关联数组

    同时，要求嵌套表和关联数组的元素类型必须是记录类型和以下基础类型：

    • VARCHAR2、VARCHAR、CHAR 等字符串类型

    • NUMBER、INT、INTEGER、FLOAT、DOUBLE 等数值类型

    • DATE 类型

    • TIMESTAMP 类型

• 支持调用远端 Oracle 数据库内部的存储过程或包的同义词，同时也支持调用本地数据库中创建的远端存储过程或包的同义词。

• 不支持远程包类型的构造函数

下面以几个简单的示例进行说明。假设本地 OceanBase 数据库中创建了到远端 Oracle 数据库的 DBLink orcl_link。

obclient [SYS]> CREATE DATABASE LINK orcl_dblink CONNECT TO orcl_user@oracle IDENTIFIED BY ****** OCI HOST 'xx.xx.xx.xx:1521/ORCL';

• 调用一个简单的存储过程。

  1. 准备数据环境。

     远端 Oracle 数据库中存在着一张客户信息表 customer_info:

     SQL> CREATE TABLE customer_info
            (id NUMBER,
            name VARCHAR2(20));
     

     并向表中插入如下所示数据：

     SQL> INSERT INTO customer_info (id, name) VALUES (2, 'Jane Smith');
     

     SQL> INSERT INTO customer_info (id, name) VALUES (3, 'Mike Johnson');
     

     SQL> INSERT INTO customer_info (id, name) VALUES (4, 'Sarah Davis');
     

     执行 SELECT * FROM customer_info; 语句查询表中的数据，结果如下：

             ID NAME
     ---------- --------------------
             2 Jane Smith
             3 Mike Johnson
             4 Sarah Davis
     

     同时远端数据库中还创建了一个存储过程，该存储过程用于接收一个 customer_id 参数，并返回该客户的 ID。

     SQL> CREATE OR REPLACE PROCEDURE get_customer_id (
          customer_id IN NUMBER
      )
      IS
      BEGIN
          DBMS_OUTPUT.PUT_LINE('Customer ID is: ' || customer_id);
      END;
      /
     

  2. 在本地 OceanBase 数据库中调用存储过程。

     obclient [SYS]> CALL get_customer_id@orcl_dblink(2);
     

  但是，当前暂不支持通过以下方式调用该存储过程，如果使用以下语句调用上面的存储过程就会报错：

  SELECT get_customer_id@orcl_dblink(2) FROM DUAL;
  

  除此之外，如果您为远端 Oracle 数据库上的存储过程 get_customer_id 创建了以下同义词：

  • 在远端 Oracle 数据库上创建存储过程 get_customer_id 的同义词 syn_remote_customer_id。

    obclient> CREATE OR REPLACE SYNONYM syn_remote_customer_id FOR get_customer_id;
    

  • 在本地 OceanBase 数据库上通过 DBLink 创建存储过程 get_customer_id 的同义词 syn_local_customer_id。

    obclient [SYS]> CREATE OR REPLACE SYNONYM syn_local_customer_id FOR get_customer_id@orcl_dblink;
    

  您都可以调用这些同义词，调用语句如下：

  • 调用远端 Oracle 数据库上的同义词。

    obclient [SYS]> CALL syn_remote_customer_id@orcl_dblink(2);
    

  • 调用本地 OceanBase 数据库上的同义词。

    obclient [SYS]> CALL syn_local_customer_id(2);
    

• 调用一个包含出入参的存储过程。

  如下所示，假设远端 Oracle 数据库中存在着一张员工表 employees:

  SQL> CREATE TABLE employees
        (name VARCHAR2(20),
        id NUMBER);
  

  创建了一个存储过程，该存储过程用于接受一个 p_employee_id 作为输入参数，然后返回该员工的姓名和 ID。如果找不到对应的员工，则返回 Employee not found 的信息，如果发生其他异常，则返回 Error occurred。

  SQL> CREATE OR REPLACE PROCEDURE get_employee_info (
      p_employee_id IN NUMBER,
      p_employee_name OUT VARCHAR2,
      p_employee_id_out OUT NUMBER
  )
  IS
  BEGIN
      SELECT name, id INTO p_employee_name, p_employee_id_out
      FROM employees
      WHERE id = p_employee_id;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          p_employee_name := 'Employee not found';
          p_employee_id_out := NULL;
      WHEN OTHERS THEN
          p_employee_name := 'Error occurred';
          p_employee_id_out := NULL;
  END;
  /
  

  入参 employee_id 和出参 employee_name 的数据类型均符合要求。您可以在本地数据库中，再通过以下语句调用存储过程。

  obclient [SYS]> SET SERVEROUTPUT ON;
  

  obclient [SYS]> DECLARE
      v_employee_name VARCHAR2(20);
      v_employee_id NUMBER;
  BEGIN
      get_employee_info@orcl_dblink(1, v_employee_name, v_employee_id);
      DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
  END;
  /
  

  以上语句中，声明了两个变量 v_employee_name 和 v_employee_id 用于接收存储过程的输出结果，输出结果如下：

  Employee Name: Employee not found
  Employee ID:
  

• 调用一个包类型为复合数据类型的出入参的包存储过程。

  远程 Oracle 数据库中有一个表 tbl1_v 和包 pkg1，其包类型为嵌套表。

  SQL> CREATE TABLE tbl1_v(v varchar2(1000));
  

  SQL> CREATE OR REPLACE PACKAGE pkg1 AS
      TYPE ty1 IS TABLE OF varchar2(32767);
      TYPE ty2 IS TABLE OF NUMBER;
      v varchar2(1000);
  
      PROCEDURE pro1(param1 ty1);
      PROCEDURE pro1(param1 ty2);
      PROCEDURE pro1_out(param1 out ty1);
      PROCEDURE pro1_out(param1 out ty2);
      PROCEDURE pro1_inout(param1 in out ty1);
      PROCEDURE pro1_inout(param1 in out ty2);
  END;
  /
  

  对应的包主体如下：

  SQL> CREATE OR REPLACE PACKAGE BODY pkg1 IS
      PROCEDURE pro1(param1 ty1) IS
      v varchar2(1024);
      BEGIN
        DELETE FROM tbl1_v;
        FOR idx IN param1.first .. param1.last LOOP
          v := 'param1(' || idx || ')=' || param1(idx) || ';';
          INSERT INTO tbl1_v values(v);
        END LOOP;
      END;
      PROCEDURE pro1(param1 ty2) IS
      BEGIN
        DELETE FROM tbl1_v;
        FOR idx in param1.first .. param1.last LOOP
          v := 'param1(' || idx || ')=' || param1(idx) || ';';
          INSERT INTO tbl1_v values(v);
        END LOOP;
      END;
     PROCEDURE pro1_out(param1 out ty1) IS
     BEGIN
        param1 := ty1();
        param1.extend(10);
        FOR idx in 1..10 LOOP
         param1(idx) := idx;
        END LOOP;
      END;
     PROCEDURE pro1_out(param1 out ty2) IS
     BEGIN
        param1 := ty2();
        param1.extend(10);
        FOR idx in 1..10 LOOP
         param1(idx) := idx;
        END LOOP;
      END;
   PROCEDURE pro1_inout(param1 IN OUT ty1) IS BEGIN pro1(param1); pro1_out(param1); END;
   PROCEDURE pro1_inout(param1 IN OUT ty2) IS BEGIN pro1(param1); pro1_out(param1); END;
   END;
  /
  

  您可以在本地 OceanBase 数据库中通过以下方式，调用包存储过程：

  obclient [SYS]> CREATE TABLE tbl_local (c1 int);
  

  obclient [SYS]> INSERT INTO tbl_local VALUES(1);
  

  obclient [SYS]> DECLARE
      param pkg1.ty2@orcl_dblink;
    BEGIN
      SELECT C1 bulk collect INTO param FROM tbl_local;
      pkg1.pro1@orcl_dblink(param);
    END;
    /
  

  但是对于以下包存储过程 pkg2：

  SQL> CREATE OR REPLACE PACKAGE pkg2 AS
      TYPE ty1 IS RECORD(c1 varchar2(10), c2 int);
      TYPE ty2 IS TABLE OF varchar2(10);
      TYPE ty3 IS TABLE OF ty1;
      TYPE ty4 IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
      TYPE ty5 IS TABLE OF ty1 INDEX BY PLS_INTEGER;
      TYPE ty6 IS TABLE OF varchar2(10);
      PROCEDURE pro2(param1 ty1, param2 ty2, param3 ty3 , param4 ty4, param5 ty5);
  END;
  /
  

  包 pkg2 对应的包主体：

  SQL> CREATE OR REPLACE PACKAGE BODY pkg2 AS
      PROCEDURE pro2 (param1 ty1, param2 ty2, param3 ty3 , param4 ty4, param5 ty5) IS
      BEGIN
          DBMS_OUTPUT.PUT_LINE('Parameter 1: ' || param1.c1 || ', ' || param1.c2);
          FOR i IN param2.FIRST .. param2.LAST LOOP
              DBMS_OUTPUT.PUT_LINE('Parameter 2(' || i || '): ' || param2(i));
          END LOOP;
          FOR i IN param3.FIRST .. param3.LAST LOOP
              DBMS_OUTPUT.PUT_LINE('Parameter 3(' || i || '): ' || param3(i).c1 || ', ' || param3(i).c2);
          END LOOP;
      END pro2;
  END pkg2;
  /
  

  通过以下方式进行调用时，会报错：

  obclient [SYS]> DECLARE
      param1 pkg2.ty1@orcl_dblink;
      param2 pkg2.ty2@orcl_dblink;
      param3 pkg2.ty3@orcl_dblink;
      param4 pkg2.ty4@orcl_dblink;
      param5 pkg2.ty5@orcl_dblink;
    BEGIN
      param1.c1 := 'a';
      param1.c2 := 123;
      param2 := pkg2.ty2@orcl_dblink('b', 'c');
      param3 := pkg2.ty3@orcl_dblink();
      param3.EXTEND(2);
      param3(1).c1 := 'd';
      param3(1).c2 := 456;
      param3(2).c1 := 'e';
      param3(2).c2 := 789;
  
      pkg2.pro2@orcl_dblink(param1, param2, param3, param4, param5);
    END;
    /
  

  此外，对于远端数据库为 oracle-12c 及以下版本的场景，该示例中由于 ty6 没有出现在包 pkg2 内部 Routine 的参数列表位置，故通过以下语句调用包存储过程就会报错：

  obclient [SYS]> DECLARE
    v1 pkg2.ty6@orcl_dblink;
  BEGIN
    null;
  END;
  /
  

  有关包的更多使用及说明，请参见 创建程序包。

调用远端数据库中的 UDF

OceanBase 数据库从 V4.2.2 版本开始，支持通过 DBLink 调用远端 Oracle 数据库中的 UDF（User-Defined functions）和包函数，当前暂不支持调用远端 OceanBase 数据库的中的 UDF 和包函数。

通过 DBLink 调用远端 Oracle 数据库中的 UDF 或包函数前，需要注意以下事项：

• 调用 UDF 时，支持以下基础类型的出入参：

  • VARCHAR2、VARCHAR、CHAR 等字符串类型

  • NUMBER、INT、INTEGER、FLOAT、DOUBLE 等数值类型

  • DATE 类型

  • TIMESTAMP 类型

• 调用包函数时，支持包类型为以下复合数据类型的出入参：

  • 记录类型

    同时，还要求记录类型的元素类型必须是以下基础类型：

    • VARCHAR2、VARCHAR、CHAR 等字符串类型

    • NUMBER、INT、INTEGER、FLOAT、DOUBLE 等数值类型

    • DATE 类型

    • TIMESTAMP 类型

  但是，需要注意，对于远端 Oracle 数据库为 oracle-12c 及以下版本的场景，包类型必须是出现在包中 Routine 参数位置的类型。如果仅在包中定义，未在包中任何 Routine 参数中使用，则不支持调用。

  • 嵌套表、关联数组

    同时，要求嵌套表和关联数组的元素类型必须是记录类型和以下基础类型：

    • VARCHAR2、VARCHAR、CHAR 等字符串类型

    • NUMBER、INT、INTEGER、FLOAT、DOUBLE 等数值类型

    • DATE 类型

    • TIMESTAMP 类型

• 支持调用远端 Oracle 数据库中函数或包的同义词，同时也支持调用本地数据库中创建的远端函数或包的同义词。

下面以几个简单的示例进行说明。假设本地 OceanBase 数据库中创建了到远端 Oracle 数据库的 DBLink orcl_link。

obclient [SYS]> CREATE DATABASE LINK orcl_dblink CONNECT TO orcl_user@oracle IDENTIFIED BY ****** OCI HOST 'xx.xx.xx.xx:1521/ORCL';

• 调用一个简单的 UDF。

  1. 在远端 Oracle 数据库中创建一个 UDF AddNumbers。

     SQL> CREATE OR REPLACE FUNCTION AddNumbers (num1 IN NUMBER, num2 IN NUMBER)
      RETURN NUMBER
      AS
      BEGIN
        RETURN num1 + num2;
      END;
      /
     

     该函数接受两个数字作为输入并返回这两个数字的和。

  2. 在本地 OceanBase 数据库中调用该函数。

     obclient [SYS]> SELECT AddNumbers@orcl_dblink(10, 20) AS Sum_Result FROM DUAL;
     

     查询结果如下所示：

     +------------+
     | SUM_RESULT |
     +------------+
     |         30 |
     +------------+
     1 row in set
     

  与存储过程一样，如果您为远端 Oracle 数据库的 UDF 或包创建了同义词，您可以在本地 OceanBase 数据库上通过同义词来进行调用。例如，如果您为该 AddNumbers 创建了以下同义词：

  • 在远端 Oracle 数据库上创建函数 AddNumbers 的同义词 syn_remote_AddNumbers。

    SQL> CREATE OR REPLACE SYNONYM syn_remote_AddNumbers FOR AddNumbers;
    

  • 在本地 OceanBase 数据库上通过 DBLink 创建函数 AddNumbers 的同义词 syn_local_AddNumbers。

    obclient [SYS]> CREATE OR REPLACE SYNONYM syn_local_AddNumbers FOR AddNumbers@orcl_dblink;
    

  您都可以在本地 OceanBase 数据库上调用这些同义词，调用语句如下：

  • 调用远端 Oracle 数据库上的同义词。

    obclient [SYS]> SELECT syn_remote_AddNumbers@orcl_dblink(10, 20) AS Sum_Result FROM DUAL;
    

  • 调用本地 OceanBase 数据库上的同义词。

    obclient [SYS]> SELECT syn_local_AddNumbers(10, 20) AS Sum_Result FROM DUAL;
    

  有关更多 UDF 的说明及介绍，请参见 用户自定义函数。

• 调用一个包函数。

  1. 在远程 Oracle 数据库中创建一个包 pac_employee_data，该包中定义了一个函数 calculate_annual_bonus。

     SQL> CREATE OR REPLACE PACKAGE pac_employee_data AS
        -- 定义函数原型
        FUNCTION calculate_annual_bonus(emp_id IN NUMBER, monthly_salary IN NUMBER) RETURN NUMBER;
      END pac_employee_data;
      /
     

     对应的包主体如下。

     SQL> CREATE OR REPLACE PACKAGE BODY pac_employee_data AS
        -- 实现函数
        FUNCTION calculate_annual_bonus(emp_id IN NUMBER, monthly_salary IN NUMBER) RETURN NUMBER IS
          annual_bonus NUMBER;
        BEGIN
          -- 假设员工的年度奖金为年工资的 10%
          annual_bonus := monthly_salary * 12 * 0.1; 
          RETURN annual_bonus;
        END calculate_annual_bonus;
      END pac_employee_data;
      /
     

  2. 在本地 OceanBase 数据库中调用该包函数。

     您可以在 PL 块中远程调用包中的函数，语句如下。

     obclient [SYS]> SET SERVEROUTPUT ON;
     

     obclient [SYS]> DECLARE
        emp_id CONSTANT NUMBER := 100;
        monthly_salary CONSTANT NUMBER := 5000;
        bonus_amount NUMBER;
      BEGIN
        -- 调用包函数并获取结果
        bonus_amount := pac_employee_data.calculate_annual_bonus@orcl_dblink(emp_id, monthly_salary);
     
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Annual Bonus: $' || TO_CHAR(bonus_amount, '999,999.99'));
      END;
      /
     

     返回结果如下：

     Employee ID: 100, Annual Bonus: $   6,000.00
     

     或者，您也可以通过 SQL 查询调用该包函数，语句如下。

     obclient [SYS]> SELECT pac_employee_data.calculate_annual_bonus@orcl_dblink(100, 5000) AS "Annual Bonus" FROM DUAL;
     

     查询结果如下：

     +--------------+
     | Annual Bonus |
     +--------------+
     |         6000 |
     +--------------+
     1 row in set
     

  有关包的更多使用及说明，请参见 创建程序包。

相关文档

更多 DBLink 相关的操作，参见以下信息：

• 创建 DBLink

• 查看 DBLink

• 通过 DBLink 修改远端数据库的数据

• 删除 DBLink