Oracle應用遷移到AnalyticDB for PostgreSQL指導

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/PGSQL

1.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的function

1.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 = 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的複合類型pair
Table 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外做rollback
ADB for PG支持的error，可以參考: https://www.postgresql.org/docs/8.3/errcodes-appendix.html

1.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 < 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示例：

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);