AnalyticDB for PostgreSQL（簡稱：ADB for PG）對Oracle文法有着較好的相容，本文介紹如何将Oracle 應用遷移到AnalyticDB for PostgreSQL。

1 開源工具ora2pg使用

在開始轉換Oracle pl之前可以使用開源工具ora2pg（

）進行最初始的轉換。具體的使用，使用者可以閱讀其使用者文檔，在此不做贅述。使用者可以使用ora2pg，将oracle的表DDL，view，package等轉換成PG相容的文法。但是，由于腳本轉換後的PG文法版本比ADB for PG使用的PG核心版本高，而且ora2pg依賴規則進行轉換，難免有所疏漏和錯誤，是以使用者還需要人工去對轉換後的sql腳本做糾正。具體推薦的做法是：可以将轉換後的sql腳本在ADB for PG上試運作，觀察是否有文法報錯或者結果不符預期，然後在依據本文接下來幾個章節的内容，進行人工糾正。

2 Orafunc插件使用

ADB for PG提供了Orafunc插件的使用，該插件提供了一些相容Oracle的函數。對于這些函數，使用者無需任何修改轉換就可以直接在ADB for PG中使用。

在使用Orafunc插件前，隻需執行create extension orafunc；指令即可。

postgres=> create extension orafunc;
CREATE EXTENSION

Orafunc插件提供的相容函數如下表所示：

函數	作用	示例
nvl(anyelement, anyelement)	如果第一個參數為null，那麼會傳回第二個參數；否則傳回第一個參數。注意：兩個參數必須是相同類型。	postgres=# select nvl(null,1); nvl ----- 1 (1 row) postgres=# select nvl(0,1); postgres=# select nvl(0,null);
add_months(day date, value int) RETURNS date	在第一個月份參數上加上第二個參數月數，傳回一個date	postgres=# select add_months(current_date, 2); add_months ------------ 2019-08-31
last_day(value date)	傳回某年某個月份的最後一個天，傳回類型為date	postgres=# select last_day('2018-06-01'); last_day 2018-06-30
next_day(value date, weekday text)	參數一：開始的日期參數二：包含星期幾的英文字元串，如Friday 傳回開始日期後的第二個星期幾的日期，如第二個Friday	postgres=# select next_day(current_date, 'FRIDAY'); next_day 2019-07-05
next_day(value date, weekday integer)	參數二：星期幾的數字，取值為1到7，1為星期日，2為星期1，以此類推傳回日期加天數之後的日期	postgres=# select next_day('2019-06-22', 1); 2019-06-23 postgres=# select next_day('2019-06-22', 2); 2019-06-24
months_between(date1 date, date2 date)	傳回date1和date2之間的月數如果 date1晚于date2，結果為正如果date1早于date2，結果為負	postgres=# select months_between('2019-01-01', '2018-11-01'); months_between ---------------- 2 postgres=# select months_between('2018-11-01', '2019-01-01'); -2
trunc(value timestamp with time zone, fmt text)	參數一：要被截斷的timestamp 參數二：應用于截斷的度量機關，如年，月，日，周，時，分，秒等，Y:會截斷成日期年份的第一天，Q傳回季度的第一天。	postgres=# SELECT TRUNC(current_date,'Q'); trunc 2019-04-01 postgres=# SELECT TRUNC(current_date,'Y'); 2019-01-01
trunc(value timestamp with time zone)	截斷timestamp，預設截斷時分秒	postgres=# SELECT TRUNC('2019-12-11'::timestamp); trunc ------------------------ 2019-12-11 00:00:00+08
trunc(value date)	截斷日期	postgres=# SELECT TRUNC('2019-12-11'::timestamp,'Y'); 2019-01-01 00:00:00+08
round(value timestamp with time zone, fmt text)	将timestamp圓整到最近的unit_of_measure(日，周等)	postgres=# SELECT round('2018-10-06 13:11:11'::timestamp, 'YEAR'); round
round(value timestamp with time zone)	預設圓整到天	postgres=# SELECT round('2018-10-06 13:11:11'::timestamp); 2018-10-07 00:00:00+08
round(value date, fmt text)	參數為date	postgres=# SELECT round(TO_DATE('27-OCT-00','DD-MON-YY'), 'YEAR'); round 2001-01-01 postgres=# SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'), 'YEAR'); 2000-01-01
round(value date)		ostgres=# SELECT round(TO_DATE('27-FEB-00','DD-MON-YY')); 2000-02-27
instr(str text, patt text, start int, nth int)	在一個string中搜尋一個substring，如果找到傳回substring在string中位置，沒找到，則傳回0 start表示搜尋的起始位置 nth表示搜尋第幾次出現的位置	postgres=# SELECT instr('Greenplum', 'e',1,2); instr ------- 4 postgres=# SELECT instr('Greenplum', 'e',1,1); 3
instr(str text, patt text, start int)	沒給nth，預設是第一次出現的位置	postgres=# SELECT instr('Greenplum', 'e',1);
instr(str text, patt text)	沒給start，預設從頭開始搜尋	postgres=# SELECT instr('Greenplum', 'e');
reverse(str text, start int, _end int)	str為輸入的字元串 start，end分别為對字元串從start到end這一段進行逆序	postgres=> select reverse('adb4pg', 5,6); reverse --------- gp
reverse(str text, start int)	從start開始到字元串結束進行逆序	ostgres=> select reverse('adb4pg', 4); gp4
reverse(str text)	逆序整個字元串	postgres=> select reverse('adb4pg'); gp4bda (1 行記錄)
concat(text, text)	将兩個字元串拼接在一起	postgres=> select concat('adb','4pg'); concat -------- adb4pg
concat(text, anyarray)/concat(anyarray, text)/concat(anyarray, anyarray)	可以拼接任意類型的資料	postgres=> select concat('adb4pg', 6666); concat adb4pg6666 postgres=> select concat(6666, 6666); concat ---------- 66666666 postgres=> select concat(current_date, 6666); concat 2019-06-306666
nanvl(float4, float4) /nanvl(float4, float4)/nanvl(numeric, numeric)	如果第一個參數是數值類型的，則傳回第一個參數。如果不是，則傳回第二參數	postgres=> select nanvl('NaN', 1.1); nanvl 1.1 postgres=> select nanvl('1.2', 1.1); 1.2
bitand(bigint, bigint)	将兩個整形的二進制做and操作，并傳回and之後的結果，隻輸出一行	postgres=# select bitand(1,3); bitand 1 postgres=# select bitand(2,6); 2 postgres=# select bitand(4,6); 4
listagg1_transfn(text, text)	最後輸出一個數組（多行），第二個參數會放在每行結果的最後，相當于分割符	postgres=> SELECT listagg1_transfn(t, '.') FROM (VALUES('abc'), ('def')) as l(t); listagg1_transfn ------------------ abc. def.
listagg2_transfn(text, text, text)	最後輸出一個數組（多行），第二個參數會放在每行結果的最後，相當于分割符，第三個參數會和第一個參數做聚集
listagg(text)	将文本值聚內建一個串	postgres=> SELECT listagg(t) FROM (VALUES('abc'), ('def')) as l(t); listagg abcdef
listagg(text, text)	将文本值聚內建一個串，第二個參數執行了分割符	postgres=> SELECT listagg(t, '.') FROM (VALUES('abc'), ('def')) as l(t); abc.def
nvl2(anyelement, anyelement, anyelement)	如果第一個參數不為null，那麼傳回第二個參數，如果為null，則傳回第三個參數	postgres=> select nvl2(null, 1, 2); nvl2 ------ 2 postgres=> select nvl2(0, 1, 2); 1
lnnvl(bool)	如果參數為null或者false，則傳回true，如果為true，則傳回false	postgres=> select lnnvl(null); lnnvl t postgres=> select lnnvl(false); postgres=> select lnnvl(true); f
dump("any")	傳回一個文本值，該文本中包含第一個參數的資料類型代碼、以位元組計的長度和内部表示。	postgres=> select dump('adb4pg'); dump --------------------------------------- Typ=705 Len=7: 97,100,98,52,112,103,0
dump("any", integer)	第二個參數表示傳回文本值的内部表示是使用10進制還是16進制，目前隻支援10和16	postgres=> select dump('adb4pg', 10); postgres=> select dump('adb4pg', 16); dump ------------------------------------ Typ=705 Len=7: 61,64,62,34,70,67,0 postgres=> select dump('adb4pg', 2); ERROR: unknown format (others.c:430)
nlssort(text, text)	指定排序規則的排序資料函數	create table t1 (name text); INSERT INTO t1 VALUES('Anne'), ('anne'), ('Bob'), ('bob'); postgres=> select from t1 order by nlssort(name, 'en_US.UTF-8'); name anne Anne bob Bob (4 行記錄) postgres=> select from t1 order by nlssort(name, 'C');
substr(str text, start int)	擷取參數一中字元串的子串。參數二表示擷取子串的起始位置 >= start	postgres=> select substr('adb4pg', 1); substr postgres=> select substr('adb4pg', 4); 4pg
substr(str text, start int, len int)	參數三會指定子串的結束位置 >= start and <= end	postgres=> select substr('adb4pg', 5,6); pg
pg_catalog.substrb(varchar2, integer, integer)	varchar2類型的擷取子串函數，參數二為start pos，參數三為end pos	postgres=> select substr('adb4pg'::varchar2, 5,6) ;
pg_catalog.substrb(varchar2, integer)	varchar2類型的擷取子串函數，參數二為start pos，從start pos一直取到字元串結束	postgres=> select substr('adb4pg'::varchar2, 4) ;
pg_catalog.lengthb(varchar2)	擷取varchar2類型字元串占的位元組數，如果輸入為null傳回null，輸入為空字元，則傳回0	ostgres=> select lengthb('adb4pg'::varchar2) ; lengthb 6 postgres=> select lengthb('分析型'::varchar2) ; 9

Orafunc插件除了提供上述相容函數，還對Oracle的Varchar2資料類型提供了相容。

另外，對于下面的四個Oracle函數，在ADB for PG中，無需安裝orafunc插件就可以提供相容支援：


sinh(float)	雙曲正弦值	postgres=# select sinh(0.1); sinh ------------------- 0.100166750019844
tanh(float)	雙曲正切值	postgres=# select tanh(3); tanh 0.99505475368673
cosh(float)	雙曲餘弦值	postgres=# select cosh(0.2); cosh 1.02006675561908
decode(expression, value, return [,value, return]... [, default])	在表達是中尋找一個搜尋值，被找到傳回指定的值。如果沒有搜尋到，傳回default值。	create table t1(id int, name varchar(20)); postgres=# insert into t1 values(1,'alibaba'); postgres=# insert into t1 values(2,'adb4pg'); postgres=# select decode(id, 1, 'alibaba', 2, 'adb4pg', 'not found') from t1; case alibaba (2 rows) postgres=# select decode(id, 3, 'alibaba', 4, 'adb4pg', 'not found') from t1; case ----------- not found

3 資料類型轉換對照表

Oracle	ADB for PG
VARCHAR2	varchar or text
DATE	timestamp
LONG	text
LONG RAW	bytea
CLOB
NCLOB
BLOB
RAW
ROWID	oid
FLOAT	double precision
DEC	decimal
DECIMAL
DOUBLE PRECISION
INT	int
INTERGE	integer
REAL	real
SMALLINT	smallint
NUMBER	numeric
BINARY_FLOAT
BINARY_DOUBLE
TIMESTAMP
XMLTYPE	xml
BINARY_INTEGER
PLS_INTEGER
TIMESTAMP WITH TIME ZONE	timestamp with time zone
TIMESTAMP WITH LOCAL TIME ZONE

4 系統函數轉換對照表


sysdate	current timestamp
trunc	trunc/ date trunc
dbms_output.put_line	raise 語句
decode	轉成case when/直接使用decode
NVL	coalesce

5 PL/SQL

PL/SQL（Procedural Language/SQL）是一種過程化的SQL語言，是Oracle對SQL語句的拓展，使得SQL的使用可以具有一般程式設計語言的特點，是以，可以用來實作複雜的業務邏輯。PL/SQL對應了ADB for PG中的PL/PGSQL

5.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/function

Package 内定義的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;

5.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
函數體使用&dollar;\$ ... &dollar;\$封裝起來
函數語言聲明
Subprocedure需要轉換成ADB for PG的function

5.3 PL statement

5.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;

5.3.2 PRAGMA語句

ADB for PG 無PRAGMA語句，删除。

5.3.3 事務處理

ADB for PG 的function 内部無法使用事務控制語句，如begin，commit，rollback等。

修改方法：

删除函數體内的事務控制語句，把事務控制放在函數體外；
把函數按照commit/rollback 拆分成多個。

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

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

5.3.6 異常處理

使用raise抛出異常
Catch異常後，不能rollback事務，隻能在udf外做rollback
ADB for PG支援的error，可以參考: https://www.postgresql.org/docs/8.3/errcodes-appendix.html

5.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中取對應字段的傳回值即可。

5.3.8 字元串連接配接中變量含有單引号

在下面的示例中，變量param2是一個字元串類型。假設param2的值為adb'-'pg。下面的sql_str直接放到ADB for PG中使用會将-識别成一個operator而報錯。需要使用quote_literal函數來進行轉換。

sql_str := 'select * from test1 where col1 = ' || param1 || ' and col2 = '''|| param2 || '''and col3 = 3';

sql_str := 'select * from test1 where col1 = ' || param1 || ' and col2 = '|| quote_literal(param2) || 'and col3 = 3';

5.3.9 擷取兩個timestamp相減後的天數

SELECT to_date('2019-06-30 16:16:16') – to_date('2019-06-29 15:15:15') + 1 INTO v_days from dual;

SELECT extract('days' from '2019-06-30 16:16:16'::timestamp - '2019-06-29 15:15:15'::timestamp + '1 days'::interval)::int INTO v_days;

5.4 PL資料類型

5.4.1 Record

使用ADB for PG的複合資料類型替換

TYPE rec IS RECORD (a int, b int)；

CREATE TYPE rec AS (a int, b int);

5.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節。

5.4.3 Associative Array

無替換類型。

5.4.4 Variable-Size Arrays

與nest table 一樣，使用array類型替換。

5.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;

5.5 SQL

5.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;

5.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;

5.5.3 Dual表

去掉dual

select sysdate from dual;

select current_timestamp;

建立一個叫dual的表。

5.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;

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

5.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);
end if;

5.5.7 Sequence

create sequence seq1; 
select seq1.nextval from dual;

create SEQUENCE seq1;
select nextval('seq1');

5.5.8 Cursor的使用

在oracle中，可以使用下面的語句對cursor進行周遊

FUNCTION test_func() IS
    Cursor data_cursor IS SELECT * from test1;
BEGIN
    FOR I IN data_cursor LOOP
        Do something with I;
END LOOP;
END;

CREATE OR REPLACE FUNCTION test_func()
AS $body$
DECLARE
data_cursor cursor for select * from test1;
I record;
BEGIN
    Open data_cursor;
    LOOP
       Fetch data_cursor INTO I;
      If not found then
            Exit;
      End if;
      Do something with I;
    END LOOP;
    Close data_cursor;
END;
$body$
LANGUAGE PLPGSQL;

ORACLE可以在遞歸調用的函數裡，重複打開名字相同的cursor。但是在ADB for PG中，則不允許，需要改寫成 for I in query的形式。

FUNCTION test_func(level IN numer) IS
    Cursor data_cursor IS SELECT * from test1;
BEGIN
If level > 5 then
        return;
   End if;

    FOR I IN data_cursor LOOP
        Do something with I;
        test_func(level + 1);
END LOOP;
END;

CREATE OR REPLACE FUNCTION test_func(level int) returns void
AS $body$
DECLARE
data_cursor cursor for select * from test1;
I record;
BEGIN
    If level > 5 then
        return;
    End if;
    For I in select * from test1 LOOP
      Do something with I;
       PERFORM test_func(level+1);
    END LOOP;
END;
$body$
LANGUAGE PLPGSQL;

Oracle PL/SQL應用遷移 AnalyticDB for PostgreSQL指導1 開源工具ora2pg使用2 Orafunc插件使用3 資料類型轉換對照表4 系統函數轉換對照表5 PL/SQL