登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›【磐维数据库】PanWeiDB2.0异构数据库访问测试 ...

【磐维数据库】PanWeiDB2.0异构数据库访问测试

屋稷删 2025-6-1 21:05:01
中国移动磐维数据是基于openGauss定制开发的中国移动自用版OLTP数据库。自去2023年年12月发布以来，受到广泛关注，目前已成功上线百余套。 在产品落地的过程中，我们积累了大量的迁移、适配，以及问题分析诊断的经验。 北京海量数据技术股份有限公司，作为移动磐维集中式数据库外协厂商，对集中式磐维数据库的运维、管理、开发等均有深入了解。在江西移动现场运维整理汇总经验。
PanWeiDB2.0异构数据库访问测试

异构数据库访问兼容性测试一览表

No访问路径多维度结果备注1PanWeiDB（集中式）—访问—PanWeiDB（集中式）√支持复杂SQL2PanWeiDB（集中式）—访问—Oracle√支持3Oracle—访问—PanWeiDB（集中式）√受Oracle默认大写影响，dblink涉及磐维的对象及字段，需要用双引号""括起来。不支持求平均值，偶发性出现链接错误，重新登录正常4PanWeiDB（集中式）—访问—PanWeiDB（分布式）×基础select不支持，缺少get_typmod_with_unit系统基础函数5PanWeiDB（分布式）—访问—PanWeiDB（集中式）×不支持database link语法，厂商反馈：不支持database link访问磐维集中式6PanWeiDB（集中式）—访问—GoldenDB（分布式）√支持简单SQL，需注意与GoldenDB数据交互或者修改时，外部表定义的数据类型可能会出现转换异常的情况7PanWeiDB（集中式）—访问—AntDB（集中式）×认证交互错误：DETAIL: expected authentication request from server, but received v8AntDB（集中式）—访问—PanWeiDB（集中式）×外部表映射错误，ERROR: user mapping not found for “antdb”测试过程

1、PanWeiDB访问PanWeiDB

测试结果

序号复杂度具体操作结果1基本SQL操作查询远程数据（SELECT）验证通过2 插入数据到本地表（INSERT）验证通过3 更新本地表数据（UPDATE）验证通过4 删除数据（DELETE）验证通过5复杂SQL操作多表关联（4表JOIN）验证通过6 笛卡尔积与过滤条件验证通过7 嵌套循环（NESTED LOOP）验证通过8 哈希连接（HASH JOIN）验证通过9 聚合函数与GROUP BY验证通过10 子查询与EXISTS验证通过11 窗口函数与RANK验证通过12 复杂条件与函数验证通过2、PanWeiDB访问Oracle

3、Oracle访问PanWeiDB

3.1 多表关联（4表JOIN）
  1. -- 左连接 + 右连接 + 窗口函数
  2. select
  3.     "ee"."emp_name",
  4.     d.dept_name,
  5.     "pp"."project_name",
  6.     pa.role,
  7.     "ee"."salary"
  8. from "employees"@pg_link "ee"
  9. left join departments d on "ee"."department" = d.dept_name
  10. right join "projects"@pg_link "pp" on d.dept_id = "pp"."dept_id"
  11. join project_assignments pa on pa.project_id = "pp"."project_id"
  12. where "pp"."start_date" > sysdate - 365
  13. and "ee"."salary" > 6000;
复制代码
database link涉及磐维对象及字段，需要用双引号""括起来，这受Oracle默认大写影响
1.png

 不支持求平均值
2.png

 可以查询
3.png

4.png

偶发性出现链接错误
5.png

3.2 笛卡尔积与过滤条件
  1. -- 笛卡尔积 + CASE判断
  2. SELECT
  3.     "ee"."emp_name",
  4.     p.project_name,
  5.     CASE
  6.         WHEN "ee"."salary" > 10000 THEN 'High'
  7.         WHEN "ee"."salary" BETWEEN 5000 AND 10000 THEN 'Medium'
  8.         ELSE 'Low'
  9.     END AS salary_level
  10. FROM "employees"@pg_link "ee", projects p
  11. WHERE p.dept_id = 1
  12. AND "ee"."department" = 'IT';
复制代码
执行计划
6.png

7.png

8.png

3.3 嵌套循环（NESTED LOOP）
  1. -- 强制使用NESTED LOOP（提示方式）
  2. SELECT /*+ USE_NL(e "dd") */
  3.     e.emp_id,
  4.     "dd"."dept_name"
  5. FROM employees e
  6. JOIN "departments"@pg_link "dd" ON e.department = "dd"."dept_name"
  7. WHERE "dd"."budget" > 500000;
复制代码
9.png

10.png

11.png

3.4 哈希连接（HASH JOIN）
  1. -- 强制使用HASH JOIN
  2. SELECT /*+ USE_HASH(pa "pp") */
  3.     pa.assignment_id,
  4.     "pp"."project_name"
  5. FROM project_assignments pa
  6. JOIN "projects"@pg_link "pp" ON pa.project_id = "pp"."project_id"
  7. WHERE "pp"."end_date" > SYSDATE;
复制代码
12.png

13.png

14.png

3.5 聚合函数与GROUP BY
  1. -- 聚合函数 + HAVING
  2. SELECT
  3.     "dd"."dept_name",
  4.     COUNT(e.emp_id) AS total_employees,
  5.     SUM(e.salary) AS total_salary
  6. FROM "departments"@pg_link "dd"
  7. LEFT JOIN employees e ON "dd"."dept_name" = e.department
  8. GROUP BY "dd"."dept_name"
  9. HAVING SUM(e.salary) > 100000;
复制代码
15.png

16.png

17.png

3.6 子查询与EXISTS
  1. -- EXISTS子查询
  2. SELECT
  3.     e.emp_name
  4. FROM employees e
  5. WHERE EXISTS (
  6.     SELECT 1
  7.     FROM "project_assignments"@pg_link "pa"
  8.     WHERE "pa"."emp_id" = e.emp_id
  9.     AND "pa"."role" = 'Manager'
  10. );
复制代码
18.png

19.png

20.png

3.7 窗口函数与RANK
  1. -- RANK()窗口函数
  2. SELECT
  3.     "emp_name",
  4.     "salary",
  5.     RANK() OVER (ORDER BY "salary" DESC) AS salary_rank
  6. FROM "employees"@pg_link
  7. WHERE "department" = 'IT';
复制代码
21.png

22.png

23.png

3.8 复杂条件与函数
  1. -- 字符串函数 + 日期计算
  2. SELECT
  3.     "ee"."emp_name",
  4.     UPPER(d.dept_name) AS dept_upper,
  5.     ADD_MONTHS("ee"."hire_date", 12) AS hire_anniversary
  6. FROM "employees"@pg_link "ee"
  7. JOIN departments d ON "ee"."department" = d.dept_name
  8. WHERE TO_CHAR("ee"."hire_date", 'YYYY') = '2023'
  9. AND LENGTH("ee"."emp_name") > 5;
复制代码

25.png

26.png

4、集中式PanWeiDB访问分布式PanWeiDB

PanWeiDB（PanWeiDB版本升级测试库），访问，PanWeiDB分布式（计费系统）
测试结果

序号复杂度具体操作结果1基本SQL操作查询远程数据（SELECT）测试未通过测试账户
  1. create user testuser2 with sysadmin password '3GahEH271';
  2. create database testdb1 owner testuser2;
  3. \q
  4. gsql -r -p 17700 -d testdb1 -U testuser2 -W 3GahEH271
复制代码
1.1 创建dblink
  1. create database link dblink_jfjsxt19098 connect to testuser2 identified by '3GahEH271' using postgres_fdw(host '10.183.190.98',port '17700',dbname 'testdb1');
复制代码
2.1 基本SQL操作
  1. testdb1=> SELECT * FROM employees@dblink_jfjsxt19098 WHERE department = 'IT';
  2. ERROR:  function get_typmod_with_unit(integer, text[]) does not exist
  3. HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  4. CONTEXT:  Remote SQL command: SELECT a.attname,
  5.   (case pg_catalog.format_type(a.atttypid, get_typmod_with_unit(a.atttypmod, a.attoptions)) when 'oradate' then 'date' else pg_catalog.format_type(a.atttypid, get_typmod_with_unit(a.atttypmod, a.attoptions)) end)
  6.   FROM pg_catalog.pg_attribute a
  7.   WHERE a.attrelid = (select oid from pg_class where relname='employees' and relnamespace=(select oid from pg_namespace where nspname='testuser2'))
  8.   AND a.attnum > 0 AND NOT a.attisdropped AND a.attkvtype != 4 AND a.attname <> 'tableoid' AND a.attname <> 'tablebucketid'
  9.   ORDER BY a.attnum;
  10. referenced column: format_type
  11. testdb1=>
复制代码
27.png

磐维集中式数据库-SQL执行正常
28.png

磐维分布式数据库-SQL无法执行
29.png

磐维分布式数据库缺少函数“get_typmod_with_unit”，导致集中式数据库访问分布式数据库时，无法执行查询：
查询 pg_attribute 系统表，检索有关表 employees 的列信息，以获取列名和数据类型信息，
get_typmod_with_unit 是一个系统函数，用于从类型修饰符中提取单位信息。
它的作用是将类型修饰符转换为更易读的格式
手动创建函数get_typmod_with_unit
  1. CREATE OR REPLACE FUNCTION get_typmod_with_unit(typmod integer, options text[])
  2. RETURNS integer
  3. AS $$
  4. DECLARE
  5.     unit integer;
  6. BEGIN
  7.     -- 从类型修饰符中提取单位信息
  8.     -- 这里是一个示例逻辑，实际逻辑可能需要根据你的数据库系统进行调整
  9.     unit := typmod % 1000; -- 假设单位信息存储在 typmod 的低三位
  11.     -- 返回提取的单位信息
  12.     RETURN unit;
  13. END;
  14. $$ LANGUAGE plpgsql;
复制代码
30.png

磐维集中式dblink访问
31.png

查询报错，依然缺少函数“get_typmod_with_unit”函数，暂无方案解决（20250305）。
5、分布式PanWeiDB访问集中式PanWeiDB

PanWeiDB分布式（计费系统），访问，PanWeiDB（PanWeiDB版本升级测试库）
32.png

分布式磐维数据库，暂不支持dblink
6、PanWeiDB访问GoldenDB

PanWeiDB（PanWeiDB版本升级测试库），访问，GoldenDB分布式（中台中心）
数据类型略微调整
  1. create extension mysql_fdw;
  2. grant usage on foreign data wrapper mysql_fdw to testuser1;
  3. create server goldendb_fdw_server foreign data wrapper mysql_fdw options(HOST '10.183.200.193',PORT '8880');
  4. create user mapping for testuser1 server goldendb_fdw_server options(username 'testuser1',password '3GahEH271!');
  5. create foreign table employees_fdw_goldendb (
  6.     emp_id NUMBER(10) ,
  7.     emp_name VARCHAR2(50) NOT NULL,
  8.     hire_date DATE,
  9.     salary NUMBER(10, 2),
  10.     department VARCHAR2(50),
  11.     resume CLOB,
  12.     photo BLOB
  13. ) server goldendb_fdw_server options(DBNAME 'testdb1',table_name 'employees');
  14. create foreign table departments_fdw_goldendb (
  15.     dept_id NUMBER(10),
  16.     dept_name VARCHAR2(50) ,
  17.     manager_id NUMBER(10),
  18.     budget NUMBER(15, 2)
  19. ) server goldendb_fdw_server options(DBNAME 'testdb1',table_name 'departments');
  20. create foreign table projects_fdw_goldendb (
  21.     project_id NUMBER(10),
  22.     project_name VARCHAR2(100),
  23.     start_date DATE,
  24.     end_date DATE,
  25.     dept_id NUMBER(10)
  26. ) server goldendb_fdw_server options(DBNAME 'testdb1',table_name 'projects');
  27. create foreign table project_assignments_fdw_goldendb (
  28.     assignment_id NUMBER(10) ,
  29.     emp_id NUMBER(10),
  30.     project_id NUMBER(10),
  31.     role VARCHAR2(50)
  32. ) server goldendb_fdw_server options(DBNAME 'testdb1',table_name 'project_assignments');
复制代码
33.png

34.png

基本SQL操作

35.png

36.png

37.png

根据错误信息，问题出在数据类型转换上。具体来说，磐维无法将常量值的数据类型（1399）转换为GoldenDB 的数据类型。这通常发生在使用外部表（FDW，Foreign Data Wrapper）时，数据类型不匹配。
错误分析


  • 数据类型不匹配

    • 错误信息 cannot convert constant value to MySQL value 表明，磐维 无法将某个常量值转换为 GoldenDB 的数据类型。
    • Constant value data type: 1399 表示常量值的数据类型是 1399，这可能是 GoldenDB 中的 DECIMAL 类型。

  • 外部表定义问题

    • 外部表 employees_fdw_goldendb 的定义可能与GoldenDB 表的结构不完全匹配。

解决方案


  • 检查外部表定义

    • 确保外部表 employees_fdw_goldendb 的定义与GoldenDB 表的结构完全匹配。
    • 使用 SHOW CREATE TABLE 或 DESCRIBE 命令检查 GoldenDB 表的结构。

  • 修改外部表定义

    • 如果数据类型不匹配，修改外部表的定义，使其与 GoldenDB 表的结构一致。
    • 例如，如果 GoldenDB 表中的 emp_id 是 INT 类型，确保外部表中的 emp_id 也是 INT 类型。

  • 检查常量值的数据类型

    • 确保插入或查询时使用的常量值的数据类型与外部表的定义匹配。
    • 如果需要，使用显式类型转换。

MySQL_FDW功能描述

外部表，支持select查询、dml操作。
支持创建外部数据封装器mysql_fdw，连接MariaDB或MySQL或者GoldenDB数据库，并能在外部表上进行查询、插入、更新和删除操作。
mysql_fdw插件默认参与编译，用户可直接使用mysql_fdw，无须其他操作。
38.png

7、GoldenDB访问PanWeiDB

GoldenDB分布式（中台中心），访问，PanWeiDB（PanWeiDB版本升级测试库）
8、PanWeiDB访问AntDB

PanWeiDB（PanWeiDB版本升级测试库），访问，AntDB（财务系统）
创建扩展
  1. -- 本地数据库创建表
  2. -- 表1: employees (员工表)
  3. CREATE TABLE employees (
  4.     emp_id int PRIMARY KEY,
  5.     emp_name VARCHAR(50) NOT NULL,
  6.     hire_date DATE,
  7.     salary int,
  8.     department VARCHAR(50),
  9.     resume text,
  10.     photo text
  11. );
  13. -- 表2: departments (部门表)
  14. CREATE TABLE departments (
  15.     dept_id int PRIMARY KEY,
  16.     dept_name VARCHAR(50) UNIQUE,
  17.     manager_id int,
  18.     budget int,
  19.     CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
  20. );
  22. -- 表3: projects (项目表)
  23. CREATE TABLE projects (
  24.     project_id int PRIMARY KEY,
  25.     project_name VARCHAR(100),
  26.     start_date DATE,
  27.     end_date DATE,
  28.     dept_id int,
  29.     CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
  30. );
  32. -- 表4: project_assignments (项目分配表)
  33. CREATE TABLE project_assignments (
  34.     assignment_id int PRIMARY KEY,
  35.     emp_id int,
  36.     project_id int,
  37.     role VARCHAR(50),
  38.     CONSTRAINT fk_emp FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
  39.     CONSTRAINT fk_project FOREIGN KEY (project_id) REFERENCES projects(project_id)
  40. );
  42. -- 创建索引
  43. CREATE INDEX idx_emp_dept ON employees(department);
  44. CREATE INDEX idx_proj_dept ON projects(dept_id);
  45. CREATE INDEX idx_assign_role ON project_assignments(role);
  46. 插入数据
  48. -- 插入员工数据
  49. DO $$
  50. BEGIN
  51.     FOR i IN 1..1000 LOOP
  52.         INSERT INTO employees VALUES (
  53.             i,
  54.             'Employee' || i,
  55.             CURRENT_DATE - MOD(i, 365),
  56.             5000 + MOD(i, 10000),
  57.             CASE MOD(i, 4)
  58.                 WHEN 0 THEN 'IT'
  59.                 WHEN 1 THEN 'HR'
  60.                 WHEN 2 THEN 'Finance'
  61.                 ELSE 'Sales'
  62.             END,
  63.             'aaaaaaaaaa',
  64.             'bbbbbbbbbb'
  65.         );
  66.     END LOOP;
  67. END;
  68. $$;
  71. -- 插入部门数据
  72. INSERT INTO departments VALUES (1, 'IT', 100, 1000000);
  73. INSERT INTO departments VALUES (2, 'HR', 200, 500000);
  75. -- 插入项目数据
  76. INSERT INTO projects VALUES (101, 'ERP System', CURRENT_DATE-100, CURRENT_DATE+200, 1);
  77. INSERT INTO projects VALUES (102, 'HR Portal', CURRENT_DATE-50, CURRENT_DATE+100, 2);
  79. -- 插入项目分配数据
  80. DO $$
  81. BEGIN
  82.     FOR i IN 1..500 LOOP
  83.         INSERT INTO project_assignments VALUES (
  84.         i,
  85.         MOD(i, 1000) + 1,
  86.         CASE MOD(i, 2) WHEN 0 THEN 101 ELSE 102 END,
  87.         CASE MOD(i, 3) WHEN 0 THEN 'Developer' WHEN 1 THEN 'Tester' ELSE 'Manager' END
  88.         );
  89.     END LOOP;
  90. END;
  91. $$;
复制代码
39.png

40.png

41.png

不支持使用dblink直接访问
  1. testdb1=> SELECT * FROM employees@dblink_antdb WHERE department = 'IT';
  2. ERROR: could not connect to server "dblink_antdb"
  3. DETAIL: expected authentication request from server, but received v
  4. testdb1=>
复制代码
认证错误
使用外部表的方式访问
  1. create extension postgres_fdw;
  2. grant usage on foreign data wrapper postgres_fdw to testuser1;
  3. create server antdb_fdw_server foreign data wrapper postgres_fdw options(HOST '10.183.103.130',PORT '5432');
  4. create user mapping for testuser1 server antdb_fdw_server options(user 'testuser1',password '3GahEH271!');
  5. create foreign table employees_fdw_antdb (
  6.     emp_id NUMBER(10) ,
  7.     emp_name VARCHAR2(50) NOT NULL,
  8.     hire_date DATE,
  9.     salary NUMBER(10, 2),
  10.     department VARCHAR2(50),
  11.     resume CLOB,
  12.     photo BLOB
  13. ) server antdb_fdw_server options(schema_name 'public',table_name 'employees');
  14. create foreign table departments_fdw_antdb (
  15.     dept_id NUMBER(10),
  16.     dept_name VARCHAR2(50) ,
  17.     manager_id NUMBER(10),
  18.     budget NUMBER(15, 2)
  19. ) server antdb_fdw_server options(schema_name 'public',table_name 'departments');
  20. create foreign table projects_fdw_antdb (
  21.     project_id NUMBER(10),
  22.     project_name VARCHAR2(100),
  23.     start_date DATE,
  24.     end_date DATE,
  25.     dept_id NUMBER(10)
  26. ) server antdb_fdw_server options(schema_name 'public',table_name 'projects');
  27. create foreign table project_assignments_fdw_antdb (
  28.     assignment_id NUMBER(10) ,
  29.     emp_id NUMBER(10),
  30.     project_id NUMBER(10),
  31.     role VARCHAR2(50)
  32. ) server antdb_fdw_server options(schema_name 'public',table_name 'project_assignments');
复制代码
42.png

43.png

44.png

依然无法访问。
9、AntDB访问PanWeiDB

AntDB（财务系统），访问，PanWeiDB（PanWeiDB版本升级测试库）
  1. create extension postgres_fdw;
  2. grant usage on foreign data wrapper postgres_fdw to testuser1;
  3. create server panweidb_fdw_server foreign data wrapper postgres_fdw options(HOST '10.183.162.150',PORT '17700',dbname 'testdb1');
  4. create user mapping for testuser1 server panweidb_fdw_server options(user 'testuser1',password '3GahEH271');
  5. create foreign table employees_fdw_panweidb (
  6.     emp_id int ,
  7.     emp_name VARCHAR(50) NOT NULL,
  8.     hire_date DATE,
  9.     salary int,
  10.     department VARCHAR(50),
  11.     resume text,
  12.     photo text
  13. ) server panweidb_fdw_server options(schema_name 'public',table_name 'employees');
复制代码
45.png

不支持number、varchar2、clob、blob
46.png

47.png

48.png

 无法访问外部表
49.png

50.png

测试用例

以下是一个基于Oracle数据库使用DBLink的详细案例，包含测试用例、完整SQL语句及复杂查询示例：
1. 测试用例设计

1.1 创建测试表及约束
  1. -- 本地数据库创建表
  2. -- 表1: employees (员工表)
  3. CREATE TABLE employees (
  4.     emp_id NUMBER(10) PRIMARY KEY,
  5.     emp_name VARCHAR2(50) NOT NULL,
  6.     hire_date DATE,
  7.     salary NUMBER(10, 2),
  8.     department VARCHAR2(50),
  9.     resume CLOB,
  10.     photo BLOB
  11. );
  13. -- 表2: departments (部门表)
  14. CREATE TABLE departments (
  15.     dept_id NUMBER(10) PRIMARY KEY,
  16.     dept_name VARCHAR2(50) UNIQUE,
  17.     manager_id NUMBER(10),
  18.     budget NUMBER(15, 2),
  19.     CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
  20. );
  22. -- 表3: projects (项目表)
  23. CREATE TABLE projects (
  24.     project_id NUMBER(10) PRIMARY KEY,
  25.     project_name VARCHAR2(100),
  26.     start_date DATE,
  27.     end_date DATE,
  28.     dept_id NUMBER(10),
  29.     CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
  30. );
  32. -- 表4: project_assignments (项目分配表)
  33. CREATE TABLE project_assignments (
  34.     assignment_id NUMBER(10) PRIMARY KEY,
  35.     emp_id NUMBER(10),
  36.     project_id NUMBER(10),
  37.     role VARCHAR2(50),
  38.     CONSTRAINT fk_emp FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
  39.     CONSTRAINT fk_project FOREIGN KEY (project_id) REFERENCES projects(project_id)
  40. );
  42. -- 创建索引
  43. CREATE INDEX idx_emp_dept ON employees(department);
  44. CREATE INDEX idx_proj_dept ON projects(dept_id);
  45. CREATE INDEX idx_assign_role ON project_assignments(role);
复制代码
1.2 插入测试数据（通过PL/SQL循环）
  1. -- 插入员工数据
  2. BEGIN
  3.   FOR i IN 1..1000 LOOP
  4.     INSERT INTO employees VALUES (
  5.       i,
  6.       'Employee' || i,
  7.       SYSDATE - MOD(i, 365),
  8.       5000 + MOD(i, 10000),
  9.       CASE MOD(i, 4) WHEN 0 THEN 'IT' WHEN 1 THEN 'HR' WHEN 2 THEN 'Finance' ELSE 'Sales' END,
  10.       EMPTY_CLOB(),
  11.       EMPTY_BLOB()
  12.     );
  13.   END LOOP;
  14. END;
  15. /
  17. -- 插入部门数据
  18. INSERT INTO departments VALUES (1, 'IT', 100, 1000000);
  19. INSERT INTO departments VALUES (2, 'HR', 200, 500000);
  21. -- 插入项目数据
  22. INSERT INTO projects VALUES (101, 'ERP System', SYSDATE-100, SYSDATE+200, 1);
  23. INSERT INTO projects VALUES (102, 'HR Portal', SYSDATE-50, SYSDATE+100, 2);
  25. -- 插入项目分配数据
  26. BEGIN
  27.   FOR i IN 1..500 LOOP
  28.     INSERT INTO project_assignments VALUES (
  29.       i,
  30.       MOD(i, 1000) + 1,
  31.       CASE MOD(i, 2) WHEN 0 THEN 101 ELSE 102 END,
  32.       CASE MOD(i, 3) WHEN 0 THEN 'Developer' WHEN 1 THEN 'Tester' ELSE 'Manager' END
  33.     );
  34.   END LOOP;
  35. END;
  36. /
复制代码
2. DBLink配置与基本SQL

2.1 创建DBLink
  1. -- 创建到远程数据库的DBLink（假设远程数据库名为remote_db）
  2. -- CREATE DATABASE LINK remote_db_link
  3. -- CONNECT TO remote_user IDENTIFIED BY remote_password
  4. -- USING 'remote_db';
  6. grant all on database testdb1 to testuser1;
  7. create extension postgres_fdw;
  8. grant usage on foreign data wrapper postgres_fdw to testuser1;
  10. # 创建到oracle的映射，执行此语句需预先使用 gs_guc generate 命令生成 datasource 文件，此处以如下命令为例：
  11. gs_guc generate -S 'Gs@123456' -D $GAUSSHOME/bin -o usermapping
  13. create database link dblink_pwcrm149 connect to testuser1 identified by '3GahEH271' using postgres_fdw(host '10.183.162.149',port '17700',dbname 'testdb1');
复制代码
2.2 基本SQL操作
  1. -- 查询远程数据（SELECT）
  2. SELECT * FROM employees@dblink_pwcrm149 WHERE department = 'IT';
  4. -- 插入数据到本地表（INSERT）
  5. INSERT INTO employees
  6. SELECT * FROM employees@dblink_pwcrm149 WHERE emp_id > 500;
  8. -- 更新本地表数据（UPDATE）
  9. UPDATE employees e
  10. SET e.salary = e.salary * 1.1
  11. WHERE EXISTS (
  12.   SELECT dept_id FROM departments@dblink_pwcrm149 d
  13.   WHERE d.dept_name = e.department AND d.dept_name = 'IT'
  14. );
  16. -- 删除数据（DELETE）
  17. DELETE FROM project_assignments
  18. WHERE project_id IN (
  19.   SELECT project_id FROM projects@dblink_pwcrm149 WHERE dept_id = 1
  20. );
复制代码
3. 复杂SQL示例

3.1 多表关联（4表JOIN）
  1. -- 左连接 + 右连接 + 窗口函数
  2. SELECT
  3.     e.emp_name,
  4.     d.dept_name,
  5.     p.project_name,
  6.     pa.role,
  7.     AVG(e.salary) OVER (PARTITION BY d.dept_id) AS avg_dept_salary
  8. FROM employees@dblink_pwcrm149 e
  9. LEFT JOIN departments d ON e.department = d.dept_name
  10. RIGHT JOIN projects@dblink_pwcrm149 p ON d.dept_id = p.dept_id
  11. JOIN project_assignments pa ON pa.project_id = p.project_id
  12. WHERE p.start_date > SYSDATE - 365
  13. AND e.salary > 6000;
复制代码
3.2 笛卡尔积与过滤条件
  1. -- 笛卡尔积 + CASE判断
  2. SELECT
  3.     e.emp_name,
  4.     p.project_name,
  5.     CASE
  6.         WHEN e.salary > 10000 THEN 'High'
  7.         WHEN e.salary BETWEEN 5000 AND 10000 THEN 'Medium'
  8.         ELSE 'Low'
  9.     END AS salary_level
  10. FROM employees@dblink_pwcrm149 e, projects p
  11. WHERE p.dept_id = 1
  12. AND e.department = 'IT';
复制代码
3.3 嵌套循环（NESTED LOOP）
  1. -- 强制使用NESTED LOOP（提示方式）
  2. SELECT /*+ USE_NL(e d) */
  3.     e.emp_id,
  4.     d.dept_name
  5. FROM employees e
  6. JOIN departments@dblink_pwcrm149 d ON e.department = d.dept_name
  7. WHERE d.budget > 500000;
复制代码
3.4 哈希连接（HASH JOIN）
  1. -- 强制使用HASH JOIN
  2. SELECT /*+ USE_HASH(pa p) */
  3.     pa.assignment_id,
  4.     p.project_name
  5. FROM project_assignments pa
  6. JOIN projects@dblink_pwcrm149 p ON pa.project_id = p.project_id
  7. WHERE p.end_date > SYSDATE;
复制代码
3.5 聚合函数与GROUP BY
  1. -- 聚合函数 + HAVING
  2. SELECT
  3.     d.dept_name,
  4.     COUNT(e.emp_id) AS total_employees,
  5.     SUM(e.salary) AS total_salary
  6. FROM departments@dblink_pwcrm149 d
  7. LEFT JOIN employees e ON d.dept_name = e.department
  8. GROUP BY d.dept_name
  9. HAVING SUM(e.salary) > 100000;
复制代码
3.6 子查询与EXISTS
  1. -- EXISTS子查询
  2. SELECT
  3.     e.emp_name
  4. FROM employees e
  5. WHERE EXISTS (
  6.     SELECT 1
  7.     FROM project_assignments@dblink_pwcrm149 pa
  8.     WHERE pa.emp_id = e.emp_id
  9.     AND pa.role = 'Manager'
  10. );
复制代码
3.7 窗口函数与RANK
  1. -- RANK()窗口函数
  2. SELECT
  3.     emp_name,
  4.     salary,
  5.     RANK() OVER (ORDER BY salary DESC) AS salary_rank
  6. FROM employees@dblink_pwcrm149
  7. WHERE department = 'IT';
复制代码
3.8 复杂条件与函数
  1. -- 字符串函数 + 日期计算
  2. SELECT
  3.     e.emp_name,
  4.     UPPER(d.dept_name) AS dept_upper,
  5.     ADD_MONTHS(e.hire_date, 12) AS hire_anniversary
  6. FROM employees@dblink_pwcrm149 e
  7. JOIN departments d ON e.department = d.dept_name
  8. WHERE TO_CHAR(e.hire_date, 'YYYY') = '2023'
  9. AND LENGTH(e.emp_name) > 5;
复制代码
总结


  • 测试用例：覆盖了多表结构、索引、约束及循环插入数据。
  • DBLink操作：实现了跨数据库的增删改查。
  • 复杂SQL：包含多表关联、窗口函数、不同连接算法及优化提示。
通过以上案例，可以从多个维度测试不同数据库DBLink的功能性和性能，同时满足复杂业务场景需求。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
相关推荐
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
屋稷删
2025-6-1 21:05:01

0

粉丝关注

8

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫