• 版本
  
  • OS版本
    
  • DB版本
    
• 背景
  
• DBLink函数测试
  
  • 加载扩展
    
  • 打开连接
    
  • 查询
    
    • 如果当前session没有打开连接，无法查询
      
    • 查询时需要清楚返回列的定义
      
    • 不加列定义会报错
      
    • 查询结果可与本地表关联
      
  • 远端执行命令
    
  • 关闭连接
    
  • 删除扩展
    
• 使用总结
  

版本

OS版本

[omm@mogdb-01 ~]$ lsb_release -a
LSB Version:	:core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch
Distributor ID:	CentOS
Description:	CentOS Linux release 7.6.1810 (Core) 
Release:	7.6.1810
Codename:	Core

DB版本

stone=> select version();
                                                                     version                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------
 (MogDB 3.0.1 build eb4f387f) compiled at 2022-08-19 18:14:41 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

背景

MogDB支持dblink的扩展功能，今天来查看下MogDB中dblink函数的功能及其可用性

DBLink函数测试

加载扩展

dblink扩展默认已经安装，直接加载即可

stone=> create extension dblink;
CREATE EXTENSION

打开连接

stone=> select dblink_connect('remote','hostaddr=192.168.56.62 dbname=stone user=scott password=scott@123');
 dblink_connect 
----------------
 OK

查询

如果当前session没有打开连接，无法查询

stone=> select * from dblink('remote','select * from scott.dept') as (deptno numeric,dname varchar,loc varchar);
ERROR:  could not establish connection
DETAIL:  missing "=" after "remote" in connection info string

查询时需要清楚返回列的定义

stone=> select * from dblink('remote','select * from scott.dept') as (deptno numeric,dname varchar,loc varchar);
 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

不加列定义会报错

stone=> select * from dblink('remote','select * from scott.dept');
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select * from dblink('remote','select * from scott.dept');

查询结果可与本地表关联

select e.empno,e.ename,d.dname from 
(
select * from dblink('remote','select * from scott.dept') as (deptno numeric,dname varchar,loc varchar)
)d
inner join emp e on e.deptno = d.deptno;
 empno | ename  |   dname    
-------+--------+------------
  7934 | MILLER | ACCOUNTING
  7839 | KING   | ACCOUNTING
  7782 | CLARK  | ACCOUNTING
  7902 | FORD   | RESEARCH
  7876 | ADAMS  | RESEARCH
  7788 | SCOTT  | RESEARCH
  7566 | JONES  | RESEARCH
  7369 | SMITH  | RESEARCH
  7900 | JAMES  | SALES
  7844 | TURNER | SALES
  7698 | BLAKE  | SALES
  7654 | MARTIN | SALES
  7521 | WARD   | SALES
  7499 | ALLEN  | SALES
(14 rows)

远端执行命令

需要通过函数执行update delete insert等语句

select * from dblink_exec('remote','updaet scott.dept set loc = ''test''');

stone=> select * from dblink('remote','select * from scott.dept') as (deptno numeric,dname varchar,loc varchar);
 deptno |   dname    | loc  
--------+------------+------
     10 | ACCOUNTING | test
     20 | RESEARCH   | test
     30 | SALES      | test
     40 | OPERATIONS | test
(4 rows)

关闭连接

stone=> select dblink_disconnect('remote');
 dblink_disconnect 
-------------------
 OK
(1 row)

删除扩展

stone=> drop extension dblink;
DROP EXTENSION

使用总结

1、 dblink扩展只能连MogDB或Opengauss，无法连Oracle
2、 每次使用dblink都需要在当前session打开连接，密码为明码
3、 无法关联本地表更改远端数据
4、 查询远端数据时需要在语句中增加返回列的定义，增加了使用难度
综上所述dblink扩展只能用于临时处理数据，无法代替Oracle中的DBLink功能