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

更新时间：2023-12-11 17:46:20编辑分享创建 DBLink 后，可以使用 DBLink 修改远端数据库中的数据。当前支持通过 DBLink 向 OceanBase 数据库中 Oracle 模式的数据库和 Oracle 数据库中写入数据。

回到未来望过去

235人浏览 · 2024-03-28 11:31:27

回到未来望过去 · 2024-03-28 11:31:27 发布

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

更新时间：2023-12-11 17:46:20

编辑

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

使用限制

仅 OceanBase 数据库的 Oracle 模式支持 DBLink 功能，OceanBase 数据库的 MySQL 模式暂不支持。
使用 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。
使用 DBLink 功能访问远端 Oracle 数据库时，需要在集群的所有 OBServer 节点上安装并配置 OCI-11.2。安装和配置 OCI-11.2 的详细操作，请参见安装和配置 OCI。

注意事项

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

有关 XA 事务的详细说明，参见 XA 事务。
通过 DBLink 向远端数据库写入数据的操作完成后，需要手动使用 COMMIT 或者 ROLLBACK 语句提交回滚本地和远端的 XA 事务。

更多事务控制语句的详细说明信息，参见事务管理概述。
通过 DBLink 向远端数据库写入数据时，暂不支持通过设置 autocommit = True 来开启事务的自动提交，并且通过 DBLink 写入数据的操作一旦开启 XA 事务，系统就会将本 Session 的 autocommit 值强制置为 False，直到 XA 事务提交或者回滚后，才会恢复 autocommit 原本的值。

系统变量 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。