通过 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 事务。
通过 DBLink 向远端数据库写入数据的操作完成后，需要手动使用 COMMIT 或者 ROLLBACK 语句提交回滚本地和远端的 XA 事务。

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

系统变量 autocommit 用于设置是否开启自动提交事务，默认值为 True，更多系统变量 autocommit 的说明信息，参见 [autocommit]https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002015966)。
如果预期 XA 事务耗时较长，为了防止 XA 事务超时出现非预期的错误，可以使用 set ob_trx_timeout = 1000000000; 语句提前调大事务的超时时间。

系统变量 ob_trx_timeout 用于设置事务的超时时间，默认值为 86400000000，单位为微秒。更多系统变量 ob_trx_timeout 相关的信息，参见 ob_trx_timeout。
通过 DBLink 调用远程 UDF（用户自定义函数）后，在 SQL 中需要显示使用 COMMIT 语句提交本地和远端的 XA 事务。暂不支持通过设置 autocommit = True 来开启事务的自动提交。在 PL 中，可以通过设置 autocommit = True 来开启事务的自动提交。

修改远端数据库中的数据

通过 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 将本地表数据写入远端表的基本语法如下：

INSERT INTO table_name@dblink_name[(list_of_columns)] SELECT * FROM table_name_list [WHERE query_condition];

table_name_list:
    table_name_local [, table_name_local ...] [, table_name_dblink@dblink_name_select ...]

相关参数说明如下：

table_name@dblink_name：目标表的名称和 DBLink 的名称。
- table_name：表示需要插入数据的远端表的名称。
- dblink_name：表示在本地数据库创建的指向远端数据库的数据库链接（DBLink）的名称。
list_of_columns：可选项，指定目标表中需要插入数据的列的列表。如果省略，SQL 语句将源表的列顺序和目标表的列匹配，并且包括目标表中 SELECT 子句选中的所有列。
table_name_local：表示本地数据库中的源表名称。
table_name_dblink@dblink_name：通过 DBLink 在远端数据库中选取的源表。
- table_name_dblink：表示远端源表的名称。
- dblink_name_select：表示用来访问远端源表的 DBLink 的名称。
WHERE query_condition：可选项，表示 SELECT 语句的条件部分。query_condition 指定了哪些行应该从源表被选取来执行插入操作。

示例如下：

查看本地表 tbl1 的数据。

obclient [SYS]> SELECT * FROM tbl1;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AAA  |
|    2 | BBB  |
|    3 | CCC  |
+------+------+
3 rows in set

查看远端数据库 A 中表 a_tbl1 的数据。
```
obclient [SYS]> SELECT * FROM a_tbl1@ob_dblink_a;
```
返回结果如下：
```
Empty set
```

查看远端数据库 B 中表 b_tbl1 的数据。

obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AA   |
|    2 | BBB  |
|    3 | CC   |
+------+------+
3 rows in set

通过数据库链接 ob_dblink_b 访问远程数据库表 b_tbl1，并从本地表 tbl1 选取与 b_tbl1 的 col1 和 col2 列匹配的记录，然后将这些选中的 col1 和 col2 值通过另一个数据库链接 ob_dblink_a 插入到远程数据库的表 a_tbl1 中。
```
obclient [SYS]> INSERT INTO a_tbl1@ob_dblink_a
  SELECT t1.col1, t1.col2 FROM tbl1 t1, b_tbl1@ob_dblink_b t2
  WHERE t1.col1 = t2.col1
  AND t1.col2 = t2.col2;
```
返回结果如下：
```
Query OK, 1 row affected
```

再次查看远端数据库 A 中表 a_tbl1 的数据。

obclient [SYS]> SELECT * FROM a_tbl1@ob_dblink_a;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    2 | BBB  |
+------+------+
1 row in set

更新数据

通过 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。

将本地表数据更新至远端表

示例如下：

查看本地表 tbl1 的数据。

obclient [SYS]> SELECT * FROM tbl1;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AAA  |
|    2 | BBB  |
|    3 | CCC  |
+------+------+
3 rows in set

查看远端数据库 B 中表 b_tbl1 的数据。

obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AA   |
|    2 | BBB  |
|    3 | CC   |
+------+------+
3 rows in set

通过 DBLink ob_dblink_b 更新远程表 b_tbl1，将其 col2 列的值更改为与本地表 tbl1 中 col1 列值匹配的行的 col2 值，但是只更新那些在 tbl1 中存在匹配 col1 值的 b_tbl1 的行。

UPDATE b_tbl1@ob_dblink_b t1
    SET t1.col2 = (SELECT t2.col2
                  FROM tbl1 t2
                  WHERE t2.col1 = t1.col1)
    WHERE EXISTS (SELECT 1 
                  FROM tbl1 t2
                  WHERE t2.col1 = t1.col1);

返回结果如下：

Query OK, 3 rows affected
Rows matched: 0  Changed: 0  Warnings: 0

再次查看远端数据库 B 中表 b_tbl1 的数据。

obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AAA  |
|    2 | BBB  |
|    3 | CCC  |
+------+------+
3 rows in set

替换数据

通过 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。

使用本地表数据替换远端表数据

示例如下：

查看本地表 tbl1 的数据。

obclient [SYS]> SELECT * FROM tbl1;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AAA  |
|    2 | BBB  |
|    3 | CCC  |
+------+------+
3 rows in set

查看远端数据库 B 中表 b_tbl1 的数据。

obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AAA  |
|    2 | B2   |
|    5 | E5   |
+------+------+
3 rows in set

通过 DBLink ob_dblink_b 更新远程表 b_tbl1，将其 col2 列的值更改为与本地表（源表） tbl1 中 col1 列值匹配的行的 col2 值，但是只更新那些在 tbl1 中存在匹配 col1 值的 b_tbl1 的行。如果源表 tbl1 有行在目标表 b_tbl1 中没有匹配的行（基于 col1 值），则执行 INSERT 操作，插入新的行到目标表 b_tbl1 中，其 col1 和 col2 的值来源于源表 tbl1 的对应列。
```
MERGE INTO b_tbl1@ob_dblink_b t1
    USING tbl1 t2
        ON (t2.col1 = t1.col1)
    WHEN MATCHED THEN UPDATE SET t1.col2 = t2.col2
    WHEN NOT MATCHED THEN INSERT (t1.col1, t1.col2) VALUES(t2.col1, t2.col2);
```
返回结果如下：
```
Query OK, 3 rows affected
```

再次查看远端数据库 B 中表 b_tbl1 的数据。

obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AAA  |
|    2 | BBB  |
|    5 | E5   |
|    3 | CCC  |
+------+------+
4 rows in set

删除数据

通过 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。

根据本地表数据删除远端表数据

示例如下：

查看本地表 tbl1 的数据。

obclient [SYS]> SELECT * FROM tbl1;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AAA  |
|    2 | BBB  |
|    3 | CCC  |
+------+------+
3 rows in set

查看远端数据库 A 中表 a_tbl1 的数据。

obclient [SYS]> SELECT * FROM a_tbl1@ob_dblink_a;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    2 | BBB  |
|    1 | AA1  |
|    2 | BB2  |
|    3 | CC3  |
+------+------+
4 rows in set

删除远程表 a_tbl1 中所有在本地表 tbl1 中找到匹配 col1 和 col2 列值的行。

DELETE FROM a_tbl1@ob_dblink_a t1
    WHERE EXISTS (SELECT 1
                  FROM tbl1 t2
                  WHERE t2.col1 = t1.col1
                  AND t2.col2 = t1.col2
                );

返回结果如下：

Query OK, 1 row affected

再次查看远端数据库 A 中表 a_tbl1 的数据。

obclient [SYS]> SELECT * FROM a_tbl1@ob_dblink_a;

返回结果如下：

+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AA1  |
|    2 | BB2  |
|    3 | CC3  |
+------+------+
3 rows in set

DBLink 写支持 SAVEPOINT 功能

说明

DBLink 写支持 SAVEPOINT 功能，允许执行事务回滚到指定的保存点。

示例如下：

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

向远程表 tbl1 中插入行 1。
```
INSERT INTO tbl1@dblink_o2 VALUES(1);
```
创建名为 sp1 的 Savepoint。
```
SAVEPOINT sp1;
```
向远程表 tbl1 中插入行 2。
```
INSERT INTO tbl1@dblink_o2 VALUES(2);
```
创建名为 sp2 的 Savepoint。
```
SAVEPOINT sp2;
```
将修改回滚到 sp1。
```
ROLLBACK TO sp1;
```
向远程表 tbl1 中插入行 3。
```
INSERT INTO tbl1@dblink_o2 VALUES(3);
```
提交事务。
```
COMMIT;
```

查询表 tbl1 的数据。

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

返回结果如下：

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

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

使用限制

前提条件

注意事项

修改远端数据库中的数据

插入数据

将本地表数据插入远端表

更新数据

将本地表数据更新至远端表

替换数据

使用本地表数据替换远端表数据

删除数据

根据本地表数据删除远端表数据

DBLink 写支持 SAVEPOINT 功能

说明

相关文档