为 PolarDB 开发选择合适的工具

开发者学堂课程【7天突破PolarDB for PostgreSQL 2022版：为 PolarDB 开发选择合适的工具】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/992/detail/14981

内容介绍：

一、常用的 IDE 工具简介

二、迁移 Oracle 数据到 PolarDB

三、应用 SQL 语句迁移

四、调试 PolarDB 存储过程

一、常用 IDE 工具简介

1. 如何选择

简单易用(安装简单，使用简单)

帮助你最大限度的减轻工作量

持续提供版本升级

最好能够响应你的需求

2. 常用 IDE 工具

能够连接 PolarDB，并且进行管理

名称	性质	官网
PG admin 4	自由下载免费	https://www.pgadmin.org/
Navicat	商业软件，需要许可	https://www.navicat.com/
DB Beaver	社区版免费，其他版收费	https://dbeaver.com/
HHDBCS	个人免费，企业收费	https://www.deskui.com/

3.对查询窗口的简单比较

打开 PG admin 4、DB Beaver、HHDBCS 的3个窗口

对 IDE 来说，最重要的功能是查询窗口，查询窗口是最常使用的，HHDBCS、PG admin 4 、DB Beaver都有查询窗口。验证查询功能先建两个表格：

查询窗口能减轻工作量，重要的的原因是其具备智能弹窗的功能（尤其是对表格对象的弹出），如下

通过这种智能弹窗的方式，可以快速的编写 SQL ，这样可以减轻之前没有弹窗的工作量。但是在 PG admin 4 中，实际上不支持智能弹框。，只能通过自己记忆来编写。

对于 DB Beaver 来说，也是支持弹框的。但是有时候根据别名的列无法弹出。

在整个管理过程中，PG admin 4 不能将外部的两个表格完全删除，只能一个一个表格删除。在 HHDBCS 中，只需要要 shift 键就能选中两个表格然后删除。

4.查询窗口的其他功能及对比

找到一个简单的 SQL，对该 SQL 来说，其可读性很差，下图为部分代码：

查询器另外的功能是格式化，以 PG admin 4 为例：

但是格式化后结果并不理想，排版非常混乱。

下面再以 DB Beaver 为例：

但是格式化后也没有明显的提高，如图

最后再对比 HHDBCS ，格式化后如下图：

可以看出 HHDBCS 格式后效果最好，极大的提高了 SQL 的可读性。

5.命令窗口的对比

最常用的窗口是语句窗口和命令窗口，DB Beaver 没有命令窗口，PG admin 4 的命令窗口是需要通过配置找到二进制的路径，然后把 PostgreSQL 配置上才能生效。对于 PG admin 4 这个单独的 IDE 软件来说，是不支持命令窗口，必须和 pg 相结合，配置好 pg 的命令行，才可以使用命令窗口。

命令窗口非常有用，下图为 PG admin 4 的命令窗口

因为调用的是 pg 的命令行工具，所以其功能和 pg 的命令行功能完全一样。但是也带来了一定的问题，当数据多时，会自动停止，必须不停的按回车键才可以浏览更多的数据。（如果有很大的查询，必须有人工在不断的按回车键）

另外，不能对某一部分数据单独取出，可以高量选择复制取出大量数据。

相反，对于 HHDBCS，可以自动查询所有数据，并且可以通过 ctrl+A 复制数据导出或者直接导出。

上面对 IDE 做了一个简单的比较，IDE 最常见的就是窗口，窗口功能的强大与否直接决定了 IDE 是否好用。

二、迁移Oracle数据到 PolarDB

数据迁移分为两种，宏观上是脚本迁移，把 Oracle 本来存在的建表语句，SQL 的一些脚本迁移到相应的 PolarDB，第二种是数据的迁移，把 Oracle 表格中已经存在的数据迁移到 PolarDB 上。

1. Oracle 表结构迁移

Oracle 独特的数据类型给迁移造成很多障碍

（迁移要考虑到迁移的成功性，还要考虑到迁移的性能）

比较容易犯的错误如下：

（1）PolarDB 的 Numeric 类型

However, arithmetic on numeric values is veryslow compared to the integer types, or to thefloating-point types(numeric 主要是为了兼容二设计，其性能是非常低的)

（2）Oralce number 类型

性能非常高，算法和 PolarDB 的 numeric 不一样

Oracle 迁移到 PolarDB ，很多情况下为了兼容数据类型，把 Oracle 的数据类型都迁移为 numeric ，但是实际上是错误的。因为 numeric 这种算法在 pgsql 中性能非常低。并且在Oracle 的数据类型中的 integer 和 long 与 PolarDB 的数据类型中的 integer 和 long 不一样。所以对于 Oracle 迁移到 PolarDB，一般来说，还是需要一种好的工具帮助完成一次性迁移的工作，特别是脚本迁移。

2.脚本迁移演示

（打开 PolarDB 后还可以再打开 HHDBCS 的窗口，这也是 HHDBCS 的一个特点。对于 PG admin 4 来说，不支持同时打开多个窗口。）

先插入一些模拟数据，在 Oracle 中进行创建执行。

创建成功后，将表格打开，看到其数据已经加入。

要将该脚本迁移到 PolarDB 中，通过工具 —SQL 转换

选择 Oracle

再选择其目标数据：选择 pgsql

进行全量的转换，转换完成后将所有数据复制，再打开语句窗口粘贴，执行。

执行完成后，看到数据已经加入

并且该数据与 Oracle 的数据完全相同，迁移完成。（脚本迁移对很多业务开发来说非常有用）

3.Oracle 数据迁移（数据量非常大时）

把 Oracle 数据表迁移到 PolarDB

读取原始库数据，转换成 Insert 文件，把文件插入到 PolarDB 数据库

（1）注意:

添加目标表的名称

根据模板调整到理想的插入语句

（2）演示

用功能生成模拟数据：

创建表格

Create table mytab(id int, name varchar(200));

#foreach(si in[1..1000] )

Insert into mytab(id,name) values(si,”姓名si”);

#end

执行,可以将执行数据变成 Insert 语句，把 Insert 语句复制，在语句窗口中打开，行数1000，然后执行、提交。

对已经插入的数据进行查询：

Select count(1) from MYTAB;

行数为1000，执行结果如下图：

现在将这1000行数据迁移至 PolarDB

第一步，在查询窗口写：

SELECT * from MYTAB

然后执行，将查询后的数据进行导出

导出时注意：

数据模板可以更改，（迁移不仅可以迁移数据，还可以迁移查询语句的结果）改为mytab(ID,NAME)

然后可以查看相应的日志：

下一步要将这个1000行的表格的数据全部迁移到相应的 PolarDB

先迁移表格结构

要先 SQL 转换，然后执行，这样表格才创建成功

通过 SQL 导入的任务管理，可以将 SQL 导入，先添加 SQL 文件：

选择 SQL 的位置，

选择完后开始运行

执行完成后打开表格 mytab，对数据的行数进行统计

CREATL TABLE "mytab'

（”id” numeric,"name" vercher）

SELECT COUNT(1) from mytab;

执行结果为1000行，说明数据迁移的完整性没有问题。

三、应用 SQL 语句迁移

1.把 Oracle 的 SQL 和 PolarDB 有差异

应用的迁移往往指开发的程序员，一开始是面对对象 Oracle 的，所以都利用了对象 Oracle 的语法，当要迁移数据到 PolarDB 时非常麻烦，因为他们有简单语法差异（差异较小），Oracle 会有独有语法（差异较大）。

简单语法差异：

名称	Oracle	PolarDB
delete	delete [from] tab	delete from tab
daul	select 1*4 from dual;	select 1*4
Select	select name from (select * from person)	select name from (select * from person) tab
update	update person p set p.name='张三'	update person set name='张三'

Oracle 独有语法

Oralce 语法	PG 替代方案
start ... connect by	with 替代
merge	with 结合 insert
insert all	多个 insert
＋连接	join 替代

2.示例

（1）先检测 MERGE INTO,语句如下

insert into depl values(4,’事业部');

MERGE INTO emp1 emp USTNG (select dep_id from dep1) dep ON (emp.dep_id=dep_id)

WHEN NOT MATCHED THEN

INSERP(emp.emp_id, emp . dep_id ,emp .emp_name , emp.salary）VALUES(100，dep.der_id,’待招员工'，'1000');

该语句在 Oracle 中执行成功。

但该语句在 PG 执行不成功，需要利用 SQL 转换。

转换成功后生成两句语句:

insert into depl values(4,’事业部');

WITH HH_13 AS (SELECT *FROM emp1)insert

INTO emp1（emp_id,dep_id,emp_name,salary）

SELECT

100,dep.dep_id,’待招员工’,’1000’

FROM

(SELECT dep_id FROM dep1) dep

WHERE

NOT EXISTS

(SELECT 1 FROM HH_13 WHERE dep_id=dep.dep_id);

将转换成功后的语句放到 PG 中执行.

这时再回到 Oracle ，校验结果，用下面的语句进行检测

select * from empl where emp_id=100;

结果如下：

再在 PG 进行检测

对比结果相同

（2）再对 INSERT ALL INTO 操作

在 Oracle 中执行下列语句：

insert all

into emp_name(emp_id,emp name) values (emp_ id, emp_ name)

into emp_salary(emp_id,salary) values (emp_id , salary)

select * from emp1 where emp1.emp id=1;

再到 PG 进行检测使用之前同样要进行转换)转换后的语句为：

WITH

tmp AS

（SELECT * FROM emp1 WHERE emp1.emp_id::numeric-1）,

Hh_14 AS(

INSERT INTO emp_name (emp-id,emp_name)SELECT emp_id,emp_name FROM tmp

)SELECT

INTO emp_salary(emp_id,salary)

SELECT

Emp_id,salary

FROM

Tmp;

--校验结果

select * from emp name;

select * from emp_salary,

在 Oracle ：

在 PG 中的结果与之相同

（3）STARTWITH CONNECT BY 树形结构

执行下面语句

select *

from emp1

start with emp id=6

connect by prior boss id=emp _id;

在 Oracle 执行结果为

在 PG 中执行前转换，得到相同的结果。

UPDATE 加别名示例

UPDATE emp1 T SET T.salary = 15000 WHERE T.emp_id=1;

因为加了别名，PG 不能识别，所以要先转换，得到结果：

UPDATE emp1 T

SET salary =15000

WHERE t.emp_id::numeric=1;

--校验结果

select * from emp1 where emp_id=1;

在 Oracle 中：结果相同。

（5）子查询不带别名示例

SELECT emp_name

FROM (SELECT * from emp1

)

进行转换后在 PG 中执行：

SELECT emp_name FROM(select * from emp1) HH_18;

在 Oracle 中：

结果相同

（6）ORACLE+号转换示例

SELECT emp.emp_id, emp.emp name, dep.dep name

FROM emp1 emp, dep1 dep

WHERE emp.dep id =dep.dep id(+);

在 Oracle 中：

进行转换后 PG 中的语句为：

SELECT emp_id,emp.emp_name,dep.dep_name

FROM emp1 emp

LEFT JOIN dep1 dep ON emp.dep_id=dep.dep_id;

执行结果相同

以上示例，就完成了一个复杂语句的迁移，这种迁移对很多开发人员来说非常有用。

四、PolarDB 存储过程调试

1.调试插件（必须先安装插件）

从 github 上下载调试插件

放入 PolarDB 的 contrib 目录（该过程需要进行手动操作）

编译安装

2.编译完成后进行安装可以通过 HHDBCS 来进行

可以进行卸载和安装

安装成功后会有许多调试的函数,此时就可以开始进行调试工作

这里有一个内置的存储过程：

存储过程的语法为（实际上就是进行简单的加减乘除，另外把变量进行输出）：

CREATE OR REPLACE PROCEOURE”public”,:triple”(INOUT x integer) AS SBOOYS

BEGIN

X := x * 3;

RAISE NOTICE ‘plus 3 result = %’, x;

X := x + 2;

RAISE NOTICE ‘add 2 result = %’, x;

X := x - 2;

RAISE NOTICE ‘minus 2 result = %’, x;

END;

SBOOYS LANGUAGE plpgsql;

以该存储过程为例，看调试过程：

进行调试时将初始化的值设为4

调试后：

在 x:=x+2; 处设一断点，点击下一断点，添加一个局部变量 x ,则该 x 变为12.

再进行下一行，x 为14，查看相应消息输出为12.

再执行一行，输出另一消息：

最后完成调试，调试结果为

3.对比 DB Beaver（DB Beaver 不支持存储过程调试，但是支持运行）

CALL public.triple(4);

可以得到结果为12，但是不能进行相应的调试

4.PG admin 4 可以进行调试：

调试选择值为4：运行代码如下：