oracle迁移到mysql工具，oracle数据迁移到pg

AnalyticDB for PostgreSQL（简称ADB for PG）与Oracle语法高度兼容，并向您展示如何将Oracle应用迁移到AnalyticDB for PostgreSQL。

1 PL/SQL PL/SQL（Procedural Language/SQL）是一种过程式SQL语言。 Oracle 对SQL 语句的扩展。这赋予了SQL 通用编程语言的特性。用于实现复杂业务的逻辑。 PL/SQL对应PG的ADB中的PL/PGSQL

1.1 封装

ADB for PG 的plpgsql 不支持包。您必须将包转换为架构，并将包内的所有过程和函数转换为ADB for PG 函数。

例如：

您可以转换为创建或替换包pkg is …end;

使用create schema pkg; 定义的过程/函数的局部变量保持不变，全局变量可以使用ADB for PG 中的临时表保存。更多信息请参见第1.4.5 节。如果包初始化块可以去掉，就去掉，如果不能，则使用函数封装，根据需要主动调用函数。 Package中定义的过程/函数会转换为pg的adb函数，并且函数在package对应的schema中定义。例如，有一个名为pkg 的包，其中包含以下函数： FUNCTION test_func (args int) RETURN int is var number :=10;BEGIN. END; 它将转换为PG 函数的以下ADB：创建或替换函数pkg.test_func( args int) 返回int AS $$ … … $$ LANGUAGE plpgsql;1.2 过程/函数

Oracle 过程和函数，无论是打包的还是全局的，都会转换为adb for pg 函数。

例如：

创建或替换函数test_func (v_name varchar2, v_version varchar2)RETURN varchar2 IS ret varchar(32);BEGIN IF v_version IS NULL THEN ret :=v_name;ELSE ret :=v_name || '/' || v_version; END IF; RETURN ret;END; 翻译如下：

创建或替换函数test_func (v_name varchar, v_version varchar)RETURNS varchar AS $$DECLARE ret varchar(32);BEGIN IF v_version IS NULL THEN ret :=v_name;ELSE ret :=v_name || '/' || v_version; END IF; RETURN ret;END;$$ LANGUAGE plpgsql;过程/函数转换要点：

RETURN关键字被翻译成RETURNS函数体，并使用$;\$ . $;\$进行封装。函数语言语句中的子过程必须转换为ADB for PG 中的function1.3 PL 语句。

1.3.1 for语句

带有REVERSE 的整数FOR 循环的行为有所不同。 PL/SQL 从第二个数字到第一个数字递减计数，但PL/pgSQL 从第一个数字到第二个数字递减计数，因此移植时需要交换循环边界。

例子：

FOR i IN REVERSE 1.3 LOOP DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));END LOOP; 转换为：

FOR i IN REVERSE 3.1 LOOP RAISE ‘%’ ,i;END LOOP;1.3.2 PRAGMA 语句

ADB for PG 没有PRAGMA 语句，因此将其删除。

1.3.3 交易处理

ADB for PG 函数中不允许使用事务控制语句，例如开始、提交和回滚。

怎么修：

删除函数体内的事务控制语句，将事务控制放在函数体外，并根据提交/回滚将函数拆分为多个部分。 1.3.4 EXECUTE语句

ADB for PG支持与Oracle类似的动态SQL语句，但有以下区别：

不支持使用语法。解决方案是将参数组合成一个SQL字符串，使用quote_ident包装数据库标识符，使用quote_literal包装值。例子：

EXECUTE 'UPDATEemployees_temp SET Commission_pct=:x' USING a_null; 转换为：

EXECUTE 'UPDATEemployees_temp SET Commission_pct=' || quote_literal(a_null);1.3.5 管道

管排功能。将pg的表函数替换为adb。

例子：

类型对是RECORD(a int, b int);类型numset_t 是对的表；函数f1(x int) 返回numset_t 管道ISDECLARE v_p 对；开始在1.x 循环中的i v_p.a :=i; v_p. b :=i+10；管道行(v_p)；结束循环；返回；结束；从f1(10)中选择*；

创建类型对为(a int, b int)；创建或替换函数f1(x int) 将setof 对返回为$$declareec 对；begin for i in 1.x 循环rec :=row(i, i+10 ) ; 返回下一条记录； 结束循环； 返回；$$ Language 'plpgsql';select * from f1(10);说明：

自定义类型对在adb for pg 中转换为复杂类型。无需为pairTable 定义类型。使用adb for pg的setof替换Pipe row语句，将其转换为两条语句：rec :=row(i); return next rec; 上面的oracle函数可以转换为： 也可以：创建或替换函数f1(x int) 将setof Record 返回为$$declareec Record;begin for i in 1.x LOOP REC :=row(i, i+10); return next record ; end循环； return ;end$$ language 'plpgsql';与第一个变化的不同之处在于数据类型numset_t不需要提前定义。因此，执行查询时必须指定返回类型： select * from f1(10) as (a int, b int);

1.3.6 异常处理

使用raise抛出异常并捕获异常后，将无法回滚事务。只有UDF 之外的PG 中ADB 支持的错误才能回滚。 https://请参阅www.postgresql.org/docs/8.3/errcodes-appendix。html1.3.7 函数。返回和OUT 参数

在adb pg中，一个函数不能同时有return和out参数，所以可以将需要返回的参数重写为out类型参数。

例子：

CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int) returns varchar(10)AS $body$BEGIN out_id :=id + 1; return name;end$body$LANGUAGE PLPGSQL; 如下可以重写。

创建或替换函数test_func(id int, name varchar(10), out_id out int, out_name out varchar(10))AS $body$BEGIN out_id :=id + 1; out_name :=name;end$body$LANGUAGE PLPGSQL ;然后select * from test_func(1,'1') into rec; 从rec中获取对应字段的返回值即可。

1.4 PL数据类型

1.4.1 记录

使用ADB for PG 替换复杂数据类型

例子：

TYPE rec IS RECORD (a int, b int); 重写为：

CREATE TYPE rec AS(a int, b int);1.4.2 嵌套表

可以将PG的嵌套表替换为ADB的数组类型作为PL变量。示例：DECLARE TYPE Roster IS TABLE OF VARCHAR2(15);names Roster :=Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');BEGIN FOR i IN名称.FIRST .名称.LAST LOOP IF names(i)='J Hamil' THEN DBMS_OUTPUT.PUT_LINE(names(i)); END IF; END LOOP;END; 可以重写为：

创建或替换函数f1() 返回void 作为$$declare name varchar(15)[] :='{\'D Caruso\', \'J Hamil\', \'D Piro\', \' R Singh\ '}'; len int :=array_length(names, 1);begin for i in 1.len 循环if names[i]='J Hamil' then raise 注意'%', names[i]; end if; end Loop; return ;end$$ language 'plpgsql';select f(); 您可以使用表函数来替换函数的返回值（参见第1.3.5 节）。 1.4.3 关联数组

没有替代类型。

1.4.4 可变大小的数组

与嵌套表类似，我们使用数组类型替换。

1.4.5 全局变量

目前ADB for PG不支持全局变量，所以一种方法是将包中的所有全局变量存储在临时表（Temporary Table）中，并定义修改和检索全局变量的函数。

例子：

创建临时表global_variables (id int, g_count int, g_set_id varchar(50), g_err_code varchar(100)); Insert into global_variables value(0, 1, null, null); CREATE OR REPLACE FUNCTION get_variable() 返回一组global_variables AS$$DECLARE rec global_variables%rowtype;BEGIN 'select * from global_variables' into rec; 返回下一个rec;END;$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION set_variable(in param varchar (50), in value anyelement) returns void AS $$BEGINexecute 'update global_variables set ' || quote_ident(param) || '=' || quote_literal(value);END;$$ LANGUAGE plpgsql; 字段id是因为PG的ADB不允许改变分布列，所以我们需要再添加一个这样的字段。

tmp_rec记录；

更改全局变量时，使用select * from set_variable(‘g_error_code’, ‘error’:varchar) into tmp_rec;

获取全局变量时，使用select * from get_variable() into tmp_rec; error_code :=tmp_rec.g_error_code;

1.5SQL

1.5.1 连接方法

Oracle 的分层查询adb for pg 没有等效的替换SQL 语句。变换的思想是利用循环进行层次移动。

例子：

创建表employee(emp_id numeric(18), Lead_id numeric(18), emp_name varchar(200),salary numeric(10,2), dept_no varchar(8));插入员工值('1',0,'king ) ','1000000.00','001');插入员工值('2',1,'jack','50500.00','002');插入员工值('3',1,'arise' , '60000.00','003');插入员工值('4',2,'Scott','30000.00','002');插入员工值('5',2,'Tiger',' 25000.00) ','002');插入员工值('6',3,'wudde','23000.00','003');员工值插入('7',3,'joker','21000.00 ', '003');插入员工值('3',7,'joker','21000.00','003');员工从工资中选择emp_id,lead_id,emp_name,之前的emp_name作为lead_name并以lead_id开头=0。之前的emp_id=Lead_id 转换为：

创建或替换函数f1(tablename text, Lead_id int, nocycle boolean) setofEmployee $$declare idx int :=0; res_tbl varchar(265) :='result_table'; prev_tbl varchar(265) :='tmp_prev' 返回为； curr_tbl varchar(256) :='tmp_curr'; current_result_sql varchar(4000); tbl_count int; 记录记录；开始运行'truncate' || prev_tbl; 运行'truncate' || curr_tbl; 运行'truncate' | | res_tbl; 循环- -- 查询当前level的结果，插入到tmp_curr表中current_result_sql :='insert into ' || curr_tbl || ' select t1.* from ' || tablename || ' t1' ; if idx 0 then current_result_sql :=current_result_sql | if; use current_result_sql; -- if 循环，删除如果nocycle 为false，则执行遍历到的数据'delete from ' || curr_tbl || ' where (lead_id, emp_id) in (select Lead_id, emp_id from ' || res_tbl || ) ') '; end if; -- 如果没有数据则退出执行'select count(*) from ' || curr_tbl into tbl_count; 当tbl_count=0 时退出； -- tmp_curr 数据保存到结果表执行'insert into ' || res_tbl || ' select * from ' || curr_tbl; run '截断' || curr_tbl; idx :=idx + 1; 结束循环； --返回结果current_result_sql :='select * from ' || res_tbl; for rec in run current_result_sql 循环返回下一个记录； 结束循环； 返回；end$$ 语言plpgsql;1.5.2 Rownum

要限制查询结果集的大小，可以使用limit 来代替示例： select * from t where rownum 10; 转换为：

select * from t limit 10；使用row_number() over() 生成rownum。示例：select rownum, * from t;

转换为：

select row_number() over() as rownum, * from t;1.5.3 双表

删除Dual 的示例：从Dual 中选择sysdate；转换为：

创建一个名为select current_timestamp;dual 的表。 1.5.4 UDF 选择

ADB for PG支持在select中调用UDF，但UDF中不能包含SQL语句。否则，您将收到以下错误消息：

错误： 无法在段上执行函数，因为它访问关系\'public.t2\' (functions.c:155) (seg1 slip1 127.0.0.1:25433 pid=52153) (cdbdisp.c:1326)

详情：

SQL语句“select b from t2 where a=$1”

转换方法将select中的UDF转换为SQL表达式、子查询等。

例子：

创建或替换FUNCTION f1(arg int) RETURN int IS v int;BEGIN select b into v from t2 where a=arg; return v;END;select a, f1(b) from t1;转换为：

select t1.a, t2.b from t1, t2 where t1.b=t2.a;1.5.5 (+) 多个表的外部链接

ADB for PG 不支持语法形式(+)，因此必须将其转换为标准外连接语法。

例子：

oracleselect * from a,b 其中a.id=b.id(+) 转换为：

如果在select * from a left join b on a.id=b.id (+) 上有三个表的join，首先使用wte 连接两个表，然后使用+ table 进行连接需要执行外连接。与wte 表连接。

例子：

Select * from test1 t1, test2 t2, test3 t3 where t1.col1(+); Between NVL(t2.col1, t3.col1) and NVL(t3.col1, t2.col1) 转换为：

with cte as (select t2.col1 as low, t2.col2, t3.col1 as high, t3.col2 as c2 from t2, t3)select * from t1 right external join cte on t1.col1 between coalesce(cte.low, cte.high) 和coalesce(cte.high,cte.low)；合并到1.5.6

要将合并转换为语法，首先在ADB for PG 中使用update 进行更新，然后使用GET DIAGNOSTICS rowcount :=ROW_COUNT; 语句获取update 更新的行数。然后使用插入语句。

MERGE INTO test1 t1 USING (SELECT t2.col1col1, t3.col2col2, FROM test2 t2, test3 t3) S ON S.col1=1 and S.col2=2 WHEN MATCHED THEN UPDATE SET test1.col1=S.col1+ 1 , test1 .col2=S.col2+2 如果没有匹配到，则转换为INSERT (col1,col2) VALUES (S.col1+1, S.col2+2);

更新test1 t1 SET t1.col1=test2.col1+1, test3.col2=S.col2+2 其中test2.col1=1 且test2.col2=2;获取诊断rowcount :=ROW_COUNT;if rowcount=0 插入test1 值(test2.col1+1,test3.col2+2);2 系统函数转换对照表3 数据类型转换对照表作者：陆峰