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对应了ADB for PG中的PL/PGSQL1.1Package
ADB for PG的plpgsql不支援package，需要把package 转换成 schema，并package里面的所有procedure和 function转换成ADB for PG的function。
例如：
create or replace package pkg is
…
end;
可以转换成：
create schema pkg;
Package定义的变数 procedure/function的区域性变数保持不变，全域性变数在ADB for PG中可以使用临时表进行储存。详见1.4.5节。
Package初始化块如果可以删掉，就删掉，删不掉的话，可以使用function封装，在需要的时候主动呼叫该function。
Package 内定义的procedure/functionPackage 内定义的procedure和function 转成adb for pg的function，并把function 定义到package对应的schema内。
例如，有一个Package名为pkg中有如下函式：
FUNCTION test_func (args int) RETURN int is
var number := 10;
BEGIN
… …
END;
转换成如下ADB for PG的function：
CREATE OR REPLACE FUNCTION pkg. test_func(args int) RETURNS int AS
$$
… …
$$
LANGUAGE plpgsql;
1.2 Procedure/function
对于oracle的procedure和function，不论是package的还是全域性的，都转换成adb for pg 的function。
例如：
CREATE OR REPLACE FUNCTION 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;
转化成：
CREATE OR REPLACE FUNCTION 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;
Procedure/function转换的关键点：
RETURN 关键字转成RETURNS函式体使用$$ ... $$封装起来函式语言宣告Subprocedure需要转换成ADB for PG的function1.3 PL statement
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 的function 内部无法使用事务控制语句，如begin，commit，rollback等。
修改方法：
删除函式体内的事务控制语句，把事务控制放在函式体外；把函式按照commit/rollback 拆分成多个。1.3.4 EXECUTE语句
ADB for PG支援类似oracle的动态sql语句，不同之处如下：
不支援using 语法，解决方法是把引数拼接到sql串中；数据库识别符号使用quote_ident包裹，数值使用quote_literal包裹。示例：
EXECUTE \'UPDATE employees_temp SET commission_pct = :x\' USING a_null;
转换成：
EXECUTE \'UPDATE employees_temp SET commission_pct = \' || quote_literal(a_null);
1.3.5 Pipe row
Pipe row函式，使用adb for pg的table function来替换。
示例：
TYPE pair IS RECORD(a int, b int);
TYPE numset_t IS TABLE OF pair;
FUNCTION f1(x int) RETURN numset_t PIPELINED IS
DECLARE
v_p pair;
BEGIN
FOR i IN 1..x LOOP
v_p.a := i;
v_p.b := i+10;
PIPE ROW(v_p);
END LOOP;
RETURN;
END;
select * from f1(10);
转换成：
create type pair as (a int, b int);
create or replace function f1(x int) returns setof pair as
$$
declare
rec pair;
begin
for i in 1..x loop
rec := row(i, i+10);
return next rec;
end loop;
return ;
end
$$
language \'plpgsql\';
select * from f1(10);
说明：
自定义型别pair转换成adb for pg的复合型别pairTable of型别不需要定义，使用adb for pg的setof 替换Pipe row 语句转换成下面两个语句： rec := row(i);
return next rec;
上面的oracle function还可以转换成如下：create or replace function f1(x int) returns setof record as
$$
declare
rec record;
begin
for i in 1..x loop
rec := row(i, i+10);
return next rec;
end loop;
return ;
end
$$
language \'plpgsql\';
与第一种改法的不同支援是，不需要提前定义资料型别numset_t.正因为这一点所以在查询的时候需要指定返回的型别，如下：select * from f1(10) as (a int, b int);
1.3.6 异常处理
使用raise丢掷异常Catch异常后，不能rollback事务，只能在udf外做rollbackADB for PG支援的error，可以参考: https://www.postgresql.org/docs/8.3/errcodes-appendix.html1.3.7 function中同时有Return和OUT引数
在adb pg中，不允许fucntion同时有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;
改写成：
CREATE OR REPLACE FUNCTION 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 Record
使用ADB for PG的复合资料型别替换
示例：
TYPE rec IS RECORD (a int, b int)；
改写成：
CREATE TYPE rec AS (a int, b int);
1.4.2 Nest table
Nest table 作为pl 变数，可以使用ADB for PG的array型别替换。示例：DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15);
names Roster :=
Roster(\'D Caruso\', \'J Hamil\', \'D Piro\', \'R Singh\');
BEGIN
FOR i IN names.FIRST .. names.LAST
LOOP
IF names(i) = \'J Hamil\' THEN
DBMS_OUTPUT.PUT_LINE(names(i));
END IF;
END LOOP;
END;
改写成：
create or replace function f1() returns void as
$$
declare
names varchar(15)[] := \'{"D Caruso", "J Hamil", "D Piro", "R Singh"}\';
len int := array_length(names, 1);
begin
for i in 1..len loop
if names[i] = \'J Hamil\' then
raise notice \'%\', names[i];
end if;
end loop;
return ;
end
$$
language \'plpgsql\';
select f();
作为function返回值，则可以使用table function替换，参考1.3.5节。1.4.3 Associative Array
无替换型别。
1.4.4 Variable-Size Arrays
与nest table 一样，使用array型别替换。
1.4.5 Global variables
目前ADB for PG不支援global variables，一种方法是把一个package中的所有global variables存入一张临时表（temporary table）中, 然后定义修改、获取global variables的函式。
示例：
create temporary table global_variables (
id int,
g_count int,
g_set_id varchar(50),
g_err_code varchar(100)
);
insert into global_variables values(0, 1, null，null);
CREATE OR REPLACE FUNCTION get_variable() returns setof global_variables AS
$$
DECLARE
rec global_variables%rowtype;
BEGIN
execute \'select * from global_variables\' into rec;
return next rec;
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION set_variable(in param varchar(50), in value anyelement) returns void AS
$$
BEGIN
execute \'update global_variables set \' || quote_ident(param) || \' = \' || quote_literal(value);
END;
$$
LANGUAGE plpgsql;
其中，临时表global_variables中，字段id为这个表的分布列，因为ADB for PG中不允许对于分布列的修改，需要多加一个这样的字段。
tmp_rec record;
修改一个全域性变数时，使用：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.5 SQL
1.5.1 Connect by
Oracle 层次查询，adb for pg没有等价替换的sql语句。转换思路是使用循环按层次遍历。
示例：
create table employee(
emp_id numeric(18),
lead_id numeric(18),
emp_name varchar(200),
salary numeric(10,2),
dept_no varchar(8)
);
insert into employee values(\'1\',0,\'king\',\'1000000.00\',\'001\');
insert into employee values(\'2\',1,\'jack\',\'50500.00\',\'002\');
insert into employee values(\'3\',1,\'arise\',\'60000.00\',\'003\');
insert into employee values(\'4\',2,\'scott\',\'30000.00\',\'002\');
insert into employee values(\'5\',2,\'tiger\',\'25000.00\',\'002\');
insert into employee values(\'6\',3,\'wudde\',\'23000.00\',\'003\');
insert into employee values(\'7\',3,\'joker\',\'21000.00\',\'003\');
insert into employee values(\'3\',7,\'joker\',\'21000.00\',\'003\');
select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
from employee
start with lead_id=0
connect by prior emp_id = lead_id
转换成：
create or replace function f1(tablename text, lead_id int, nocycle boolean) returns setof employee as
$$
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;
rec record;
begin
execute \'truncate \' || prev_tbl;
execute \'truncate \' || curr_tbl;
execute \'truncate \' || res_tbl;
loop
-- 查询当前层次结果，并插入到tmp_curr表
current_result_sql := \'insert into \' || curr_tbl || \' select t1.* from \' || tablename || \' t1\';
if idx > 0 then
current_result_sql := current_result_sql || \', \' || prev_tbl || \' t2 where t1.lead_id = t2.emp_id\';
else
current_result_sql := current_result_sql || \' where t1.lead_id = \' || lead_id;
end if;
execute current_result_sql;
-- 如果有环，删除已经遍历过的资料
if nocycle is false then
execute \'delete from \' || curr_tbl || \' where (lead_id, emp_id) in (select lead_id, emp_id from \' || res_tbl || \') \';
end if;
-- 如果没有资料，则退出
execute \'select count(*) from \' || curr_tbl into tbl_count;
exit when tbl_count = 0;
-- 把tmp_curr资料储存到result表
execute \'insert into \' || res_tbl || \' select * from \' || curr_tbl;
execute \'truncate \' || prev_tbl;
execute \'insert into \' || prev_tbl || \' select * from \' || curr_tbl;
execute \'truncate \' || curr_tbl;
idx := idx + 1;
end loop;
-- 返回结果
current_result_sql := \'select * from \' || res_tbl;
for rec in execute current_result_sql loop
return next rec;
end loop;
return;
end
$$
language plpgsql;
1.5.2 Rownum
限定查询结果集大小，可以使用limit替换示例：select * from t where rownum 转换成：
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示例：select sysdate from dual;
转换成：
select current_timestamp;
建立一个叫dual的表。1.5.4 Select中的udf
ADB for PG支援在select中呼叫udf，但是udf中不能有sql语句，否则会收到如下的错误资讯：
ERROR: function cannot execute on segment because it accesses relation "public.t2" (functions.c:155) (seg1 slice1 127.0.0.1:25433 pid=52153) (cdbdisp.c:1326)
DETAIL:
SQL statement "select b from t2 where a = $1 "
转换方法是把select中的udf转换成sql表示式或者子查询等
示例：
create or replace 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 不支援(+)这样的语法形式，需要转换成标准的outer join语法。
示例：
oracle
select * from a,b where a.id=b.id(+)
转换成：
select * from a left join b on a.id=b.id
如果在(+)中有三表的join，需要先用wte做两表的join，再用+号那个表跟wte表做outer join。
示例：
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 outer join cte on t1.col1 between coalesce(cte.low, cte.high) and coalesce(cte.high,cte.low);
1.5.6 Merge into
对于merge into语法的转换，在ADB for PG中先使用update进行更新，然后使用GET DIAGNOSTICS rowcount := ROW_COUNT;语句获取update更新的行数，如果update更新的行数为0，那么再使用insert语句进行插入。
MERGE INTO test1 t1
USING (SELECT t2.col1 col1, t3.col2 col2,
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
WHEN NOT MATCHED THEN
INSERT (col1, col2)
VALUES
(S.col1+1, S.col2+2);
转换成：
Update test1 t1 SET t1.col1 = test2.col1+1, test3.col2 = S.col2+2 where test2.col1 = 1 and test2.col2 = 2;
GET DIAGNOSTICS rowcount := ROW_COUNT;
if rowcount = 0 then
insert into test1 values(test2.col1+1, test3.col2+2);
2 系统函式转换对照表
3 资料型别转换对照表
作者：陆封
