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

创建 DBLink 后，可以使用 DBLink 修改远端数据库中的数据。当前支持通过 DBLink 向 OceanBase 数据库中 Oracle 模式的数据库和 Oracle 数据库中写入数据。

使用限制

• 使用 DBLink 写功能时，如果远端数据库为 OceanBase 数据库，则要求远端 OceanBase 数据库的版本必须是 V4.1.0 及以上版本；如果远端数据库为 Oracle 数据库，建议使用 Oracle 11g 及以上版本。

• DBLink 写数据功能中有一个反向 Link 特性，反向 Link 特性主要用于远端数据库访问本地数据库的对象，对象包括表、视图和同义词等。如果在使用 DBLink 写数据功能时需要使用反向 Link 特性，则需要在创建 DBLink 时提供本地数据库的 ip、port、user_name、tenant_name、pass_word 等信息，具体操作及详细说明信息，参见 创建 DBLink。

• 当前反向 Link 特性仅支持 OceanBase 数据库的 Oracle 模式到 OceanBase 数据库的 Oracle 模式之间的访问，暂不支持 OceanBase 数据库的 Oracle 模式到 Oracle 数据库之间的访问。

前提条件

• 已创建 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。

注意事项

• 通过 DBLink 向远端数据库中写入数据时，OceanBase 数据库会自动开启本地和远端的 XA 事务，无需用户手动开启。

  有关 XA 事务的详细说明，参见 XA 事务。

• 在 SQL 中通过 DBLink 向远端数据库写入数据的场景，需要显示使用 COMMIT 语句提交本地和远端的 XA 事务。暂不支持通过设置 autocommit = True 来开启事务的自动提交。在 SQL 中通过 DBLink 写入数据时，一旦开启 XA 事务，系统就会将本 Session 的 autocommit 值强制置为 False，直到 XA 事务提交或者回滚后，才会恢复 autocommit 原本的值。

• 在 匿名块 中通过 DBLink 向远端数据库写入数据的场景，事务提交方式和普通事务类似。支持通过设置 autocommit = True 来开启事务的自动提交。设置 autocommit = False 时，需要使用 COMMIT 语句显示提交。

  系统变量 autocommit 用于设置是否开启自动提交事务，默认值为 True，更多系统变量 autocommit 的说明信息，参见 autocommit。

• 如果预期 XA 事务耗时较长，为了防止 XA 事务超时出现非预期的错误，可以使用 set ob_trx_timeout = 1000000000; 语句提前调大事务的超时时间。

  系统变量 ob_trx_timeout 用于设置事务的超时时间，默认值为 86400000000，单位为微秒。更多系统变量 ob_trx_timeout 相关的信息，参见 ob_trx_timeout。

修改远端数据库中的数据

通过 DBLink 修改远端数据库中数据的操作包括插入数据、更新数据、替换数据和删除数据等操作，主要通过 INSERT、UPDATE、MERGE INTO 和 DELETE 四种 DML 语句来实现。

插入数据

通过 DBLink 向表中插入数据的基本语法如下：

obclient> INSERT INTO table_name@dblink_name (list_of_columns) VALUES (list_of_values);

相关参数说明如下：

• table_name@dblink_name：table_name 表示待修改的远端数据库的表名；dblink_name 表示 DBLink 名。

• list_of_columns：指定表中需要插入数据的列。

• list_of_values：ist_of_columns 中指定的列的对应值，必须一一对应。

通过 DBLink 向远端数据库的表 t1 中插入一行数据 (11,11) 的示例如下：

obclient> SELECT * FROM t1@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set

obclient> INSERT INTO t1@ob_dblink VALUES (11,11);
Query OK, 1 row affected

obclient> SELECT * FROM t1@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    1 |    1 |
|   11 |   11 |
+------+------+
2 rows in set

通过 DBLink 向远端数据库的表中插入数据的操作与直接向表中插入数据的操作类似，只需要在语句中待操作的表名后添加 @dblink_name 后缀即可，有关向表中插入数据的详细操作，请参见 插入数据。

更多 INSERT 语句的详细使用及说明，请参见 INSERT。

更新数据

通过 DBLink 更新表数据的基本语法如下：

obclient> UPDATE table_name@dblink_name
SET column_name = value [, column_name = value]...
[WHERE condition];

相关参数说明如下：

• table_name@dblink_name：table_name 表示待修改的远端数据库的表名；dblink_name 表示 DBLink 名。

• column_name = value [, column_name = value]：指定需要更新的列，等号（=）后面的 value 为更新的目标值。

• WHERE condition：条件子句指定要更新的行记录必须满足的条件。如果没有则表示更新表对应列的所有记录。

通过 DBLink 将远端数据库表 t2 中 C1 列的值修改为 3 的示例如下：

obclient> SET ob_trx_timeout = 1000000000;
Query OK, 0 rows affected

obclient> SELECT * FROM t2@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set

obclient> UPDATE t2@ob_dblink SET C1 = 3;
Query OK, 1 row affected

obclient> SELECT * FROM t2@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    3 |    2 |
+------+------+
1 rows in set

通过 DBLink 更新远端数据库中表数据的操作与直接更新表数据的操作类似，只需要在语句中待操作的表名后添加 @dblink_name 后缀即可，有关更新表数据的详细操作，可参见 更新数据。

更多 UPDATE 语句的详细使用及说明，请参见 UPDATE。

替换数据

通过 DBLink 替换表数据的基本语法如下：

obclient> MERGE INTO table_name@dblink_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
    UPDATE table_name
    SET col1 = col1_val,
        col2 = col2_val
WHEN NOT MATCHED THEN
    INSERT (column_list) VALUES (column_values);

相关参数说明如下：

• MERGE INTO table_name@dblink_name alias1：指定待修改的远端数据库的表名和别名。dblink_name 表示 DBLink 名。

• USING (table|view|sub_query) alias2：指定源表（视图、子查询）和别名。

• ON (join condition)：指定判断条件。

• WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1 , col2 = col2_val ：当条件匹配时，执行 UPDATE 语句。

• WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);：当条件不匹配时，执行 INSERT 语句。

使用 MERGE INTO 语句替换数据时，如果源表中的记录在目标表中不存在，则向目标表中插入数据；如果源表中的记录在目标表中存在，则更新记录。

通过 DBLink 替换远端数据库表 t3 中的数据的示例如下：

obclient> SELECT * FROM t3@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    3 |    3 |
|    4 |    4 |
+------+------+
2 row in set

obclient> SELECT * FROM t4;
+------+------+
| C1   | C2   |
+------+------+
|    5 |    5 |
|    6 |    6 |
+------+------+
2 row in set

obclient> MERGE INTO t3@orcl_dblink a
 USING (SELECT C1,C2 FROM t4 ) b
 ON (a.C1 = b.C1)
 WHEN MATCHED THEN
     UPDATE SET a.C2 = b.C2
 WHEN NOT MATCHED THEN
     INSERT (a.C1,a.C2) VALUES(b.C1, b.C2);
Query OK, 2 rows affected

obclient> SELECT * FROM t3@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
|    6 |    6 |
+------+------+
4 row in set

通过 DBLink 替换远端数据库中表数据的操作与直接替换数据的操作类似，只需要在语句中待操作的表名后添加 @dblink_name 后缀即可，有关替换表数据的详细操作，可参见 替换数据。

更多 MERGE INTO 语句的详细使用及说明，请参见 MERGE。

删除数据

通过 DBLink 删除表数据的基本语法如下：

obclient> DELETE FROM table_name@dblink_name [WHERE condition];

相关参数说明如下：

• table_name@dblink_name：table_name 表示待修改的远端数据库的表名；dblink_name 表示 DBLink 名。

• WHERE condition：条件子句指定要删除的数据必须满足的条件，如果没有则表示执行全表删除。

通过 DBLink 删除远端数据库表 t5 中 C2 = 7 的数据行的示例如下：

obclient> SET ob_trx_timeout = 1000000000;
Query OK, 0 rows affected

obclient> SELECT * FROM t5@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    7 |    7 |
|    8 |    8 |
+------+------+
2 row in set

obclient> DELETE FROM t5@orcl_dblink WHERE C2 = 7;
Query OK, 1 row affected

obclient> SELECT * FROM t5@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    8 |    8 |
+------+------+
1 row in set

通过 DBLink 删除远端数据库的表数据的操作与直接删除表数据的操作类似，只需要在语句中待操作的表名后添加 @dblink_name 后缀即可，有关删除表数据的详细操作，可参见 删除数据。

更多 DELETE 语句的详细使用及说明，请参见 DELETE。

DBLink 写支持 SAVEPOINT 功能

示例如下：

远端数据库中有空表 tbl1，现通过以下 SQL 语句展示 DBLink 执行事务回滚到指定的保存点。

1. 向远程表 tbl1 中插入行 1。

   INSERT INTO tbl1@dblink_o2 VALUES(1);
   

2. 创建名为 sp1 的 Savepoint。

   SAVEPOINT sp1;
   

3. 向远程表 tbl1 中插入行 2。

   INSERT INTO tbl1@dblink_o2 VALUES(2);
   

4. 创建名为 sp2 的 Savepoint。

   SAVEPOINT sp2;
   

5. 将修改回滚到 sp1。

   ROLLBACK TO sp1;
   

6. 向远程表 tbl1 中插入行 3。

   INSERT INTO tbl1@dblink_o2 VALUES(3);
   

7. 提交事务。

   COMMIT;
   

8. 查询表 tbl1 的数据。

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

   返回结果如下：

   +------+
   | C1   |
   +------+
   |    1 |
   |    3 |
   +------+
   2 rows in set
   

相关文档

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

• 创建 DBLink

• 查看 DBLink

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

• 删除 DBLink