全部產品
Search

搜尋本產品

    AnalyticDB：Oracle應用遷移至AnalyticDB for PostgreSQL

    文件中心

    AnalyticDB：Oracle應用遷移至AnalyticDB for PostgreSQL

    更新時間：Feb 05, 2024

    AnalyticDB PostgreSQL對Oracle文法有著較好的相容，本文介紹如何將Oracle應用遷移到AnalyticDB PostgreSQL。

    基於ora2pg完成初步轉換工作

    可以使用開源工具ora2pg進行最初的Oracle應用轉換。您可以使用ora2pg將Oracle的表DDL，view，package等文法轉換成PostgreSQL相容的文法。具體操作方法請參見ora2pg的使用者文檔。

    說明

    由於指令碼轉換後的PostgreSQL文法版本比AnalyticDB PostgreSQL使用的PostgreSQL核心版本高，而且ora2pg依賴規則進行轉換，難免會存在偏差，因此您還需要手動對轉換後的SQL指令碼做糾正。

    Oracle相容性

    相容模式參數

    因相同函數在不同資料庫中行為有差異、互相不相容，需要通過相容模式參數進行區分配置，目前包括PostgreSQL、Oracle兩種模式。

    AnalyticDB PostgreSQL版提供了如下參數控制相容模式。

    參數名稱

    說明

    adb_compatibility_mode

    相容模式，取值如下：

    • postgres（預設值）：開啟PostgreSQL相容模式。

    • oracle：開啟Oracle相容模式。

    配置參數之前建議先通過SHOW adb_compatibility_mode;查看當前參數的值。

    如果要修改資料庫執行個體層級參數，請聯絡支援人員進行修改。

    字串串連操作符的相容模式

    對於字串串連運算式'abc' || NULL，其中的'abc'及NULL可以是常量，也可以是來自基表或計算結果的資料。

    • 在PostgreSQL模式下，運算式的傳回值為NULL。在PostgreSQL中，與NULL的任何串聯都會產生NULL。

    • 在Oracle模式下，運算式的傳回值為'abc'。在Oracle中，NULL與Null 字元串''等價。

      重要

      使用Oracle相容性下的字串串連功能，需要關閉Laser（laser.enable = off）。

    資料類型支援

    AnalyticDB PostgreSQL版對常量字串提供了資料類型支援，無需設定相容性參數就可以提供支援。

    在CREATE TABLE AS SELECT等情境下，對於常量字串可以自動識別為Text類型，而非unknown類型。

    使用Orafce外掛程式

    AnalyticDB PostgreSQL中提供了Orafce外掛程式，該外掛程式提供了一些相容Oracle的函數。對於這些函數，您無需任何修改轉換即可在AnalyticDB PostgreSQL中使用。

    在使用Orafce外掛程式前，請執行如下命令進行安裝。

    CREATE EXTENSION orafce;

    Orafce外掛程式提供的相容函數如下表所示。

    表 1. Orafce外掛程式相容函數表

    函數

    描述

    樣本

    nvl(anyelement, anyelement)

    • 若第一個參數為null，則會返回第二個參數。

    • 若第一個參數不為null，則返回第一個參數。

    說明

    兩個參數必須是相同類型。

    • SELECT nvl(null,1);

      返回資訊如下：

      nvl
-----
  1
(1 row)
    • SELECT nvl(0,1);

      返回資訊如下：

      nvl
-----
  0
(1 row)
    • SELECT nvl(0,null);

      返回資訊如下：

      nvl
-----
  0
(1 row)

    add_months(day date, value int)RETURNS date

    在第一個月份參數上加上第二個月份參數，傳回型別為date。

    SELECT add_months(current_date, 2);

    返回資訊如下：

    add_months
------------
2019-08-31
(1 row)

    last_day(value date)

    返回某年某個月份的最後一天，傳回型別為date。

    SELECT last_day('2018-06-01');

    返回資訊如下：

     last_day
------------
2018-06-30
(1 row)

    next_day(value date, weekday text)

    • 參數一：開始的日期。

    • 參數二：包含星期幾的英文字串，如Friday。

    返回開始日期後的第二個星期幾的日期，如第二個Friday。

    SELECT next_day(current_date, 'FRIDAY');

    返回資訊如下：

     next_day
------------
2019-07-05
(1 row)

    next_day(value date, weekday integer)

    • 參數一：開始的日期。

    • 參數二：星期幾的數字，取值為1到7，1為星期日，2為星期一，以此類推。

    返回開始日期加天數之後的日期。

    • SELECT next_day('2019-06-22', 1);

      返回資訊如下：

       next_day
------------
2019-06-23
(1 row)
    • SELECT next_day('2019-06-22', 2);

      返回資訊如下：

       next_day
------------
2019-06-24
(1 row)

    months_between(date1 date, date2 date)

    返回date1和date2之間的月數。

    • 如果date1晚於date2，結果為正。

    • 如果date1早於date2，結果為負。

    • SELECT months_between('2019-01-01', '2018-11-01');

      返回資訊如下：

      months_between
----------------
             2
(1 row)
    • SELECT months_between('2018-11-01', '2019-01-01');

      返回資訊如下：

      months_between
----------------
            -2
(1 row)

    trunc(value timestamp with time zone, fmt text)

    • 參數一：要被截斷的timestamp。

    • 參數二：應用於截斷的度量單位，如年，月，日，周，時，分，秒等。

      • Y：截斷成日期年份的第一天。

      • Q：返回季度的第一天。

    • SELECT TRUNC(current_date,'Q');

      返回資訊如下：

        trunc
------------
2019-04-01
(1 row)
    • SELECT TRUNC(current_date,'Y');

      返回資訊如下：

        trunc
------------
2019-01-01
(1 row)

    trunc(value timestamp with time zone)

    截斷timestamp，預設截斷時分秒。

    SELECT TRUNC('2019-12-11'::timestamp);

    返回資訊如下：

            trunc
------------------------
2019-12-11 00:00:00+08
(1 row)

    trunc(value date)

    截斷日期。

    SELECT TRUNC('2019-12-11'::timestamp,'Y');

    返回資訊如下：

            trunc
------------------------
2019-01-01 00:00:00+08

    round(value timestamp with time zone, fmt text)

    將timestamp圓整到最近的unit_of_measure（日，周等）。

    SELECT round('2018-10-06 13:11:11'::timestamp, 'YEAR');

    返回資訊如下：

            round
------------------------
2019-01-01 00:00:00+08
(1 row)

    round(value timestamp with time zone)

    預設圓整到天。

    SELECT round('2018-10-06 13:11:11'::timestamp);

    返回資訊如下：

            round
------------------------
2018-10-07 00:00:00+08
(1 row)

    round(value date, fmt text)

    參數類型為date。

    • SELECT round(TO_DATE('27-OCT-00','DD-MON-YY'), 'YEAR');

      返回資訊如下：

        round
------------
2001-01-01
(1 row)
    • SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'), 'YEAR');

      返回資訊如下：

        round
------------
2000-01-01
(1 row)

    round(value date)

    參數類型為date。

    SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'));

    返回資訊如下：

      round
------------
2000-02-27
(1 row)

    instr(str text, patt text, start int, nth int)

    在一個string中搜尋一個substring，若搜尋到則返回substring在string中位置，若沒有搜尋到，則返回0。

    • start：搜尋的起始位置。

    • nth：搜尋第幾次出現的位置。

    • SELECT instr('Greenplum', 'e',1,2);

      返回資訊如下：

      instr
-------
    4
(1 row)
    • SELECT instr('Greenplum', 'e',1,1);

      返回資訊如下：

      instr
-------
    3
(1 row)

    instr(str text, patt text, start int)

    未提供nth參數，預設是第一次出現的位置。

    SELECT instr('Greenplum', 'e',1);

    返回資訊如下：

    instr
-------
    3
(1 row)

    instr(str text, patt text)

    未提供start參數，預設從頭開始搜尋。

    SELECT instr('Greenplum', 'e');

    返回資訊如下：

    instr
-------
    3
(1 row)

    plvstr.rvrs(str text, start int, end int)

    str為輸入的字串start；end分別為對字串從start到end這一段進行逆序。

    SELECT plvstr.rvrs('adb4pg', 5,6);

    返回資訊如下：

    reverse
---------
gp
(1 row)

    plvstr.rvrs(str text, start int)

    從start開始到字串結束進行逆序。

    SELECT plvstr.rvrs('adb4pg', 4);

    返回資訊如下：

    reverse
---------
gp4
(1 row)

    plvstr.rvrs(str text)

    逆序整個字串。

    SELECT plvstr.rvrs('adb4pg');

    返回資訊如下：

    reverse
---------
gp4bda
(1 row)

    concat(text, text)

    將兩個字串拼接在一起。

    SELECT concat('adb','4pg');

    返回資訊如下：

    concat
--------
adb4pg
(1 row)

    concat(text, anyarray)/concat(anyarray, text)/concat(anyarray, anyarray)

    用於拼接任意類型的資料。

    • SELECT concat('adb4pg', 6666);

      返回資訊如下：

        concat
------------
adb4pg6666
(1 row)
    • SELECT concat(6666, 6666);

      返回資訊如下：

       concat
----------
66666666
(1 row)
    • SELECT concat(current_date, 6666);

      返回資訊如下：

          concat
----------------
2019-06-306666
(1 row)

    nanvl(float4, float4)/nanvl(float4, float4)/nanvl(numeric, numeric)

    如果第一個參數為數實值型別，則返回第一個參數；如果不為數實值型別，則返回第二參數。

    • SELECT nanvl('NaN', 1.1);

      返回資訊如下：

      nanvl
-------
  1.1
(1 row)
    • SELECT nanvl('1.2', 1.1);

      返回資訊如下：

      nanvl
-------
  1.2
(1 row)

    bitand(bigint, bigint)

    將兩個整型的二進位進行and操作，並返回and之後的結果，只輸出一行。

    • SELECT bitand(1,3);

      返回資訊如下：

      bitand
--------
     1
(1 row)
    • SELECT bitand(2,6);

      返回資訊如下：

      bitand
--------
     2
(1 row)
    • SELECT bitand(4,6);

      返回資訊如下：

      bitand
--------
     4
(1 row)

    listagg(text)

    將文本值聚整合一個串。

    SELECT listagg(t) FROM (VALUES('abc'), ('def')) as l(t);

    返回資訊如下：

    listagg
---------
abcdef
(1 row)

    listagg(text, text)

    將文本值聚整合一個串，第二個參數執行了分割符。

    SELECT listagg(t, '.') FROM (VALUES('abc'), ('def')) as l(t);

    返回資訊如下：

    listagg
---------
abc.def
(1 row)

    nvl2(anyelement, anyelement, anyelement)

    如果第一個參數不為null，那麼返回第二個參數，如果第一個參數為null，則返回第三個參數。

    • SELECT nvl2(null, 1, 2);

      返回資訊如下：

      nvl2
------
   2
(1 row)
    • SELECT nvl2(0, 1, 2);

      返回資訊如下：

      nvl2
------
   1
(1 row)

    lnnvl(bool)

    如果參數為null或者false，則返回true；如果為true，則返回false。

    • SELECT lnnvl(null);

      返回資訊如下：

      lnnvl
-------
t
(1 row)
    • SELECT lnnvl(false);

      返回資訊如下：

      lnnvl
-------
t
(1 row)
    • SELECT lnnvl(true);

      返回資訊如下：

      lnnvl
-------
f
(1 row)

    dump("any")

    返回一個文本值，該文本中包含參數的資料類型代碼、以位元組計的長度和內部表示。

    SELECT  dump('adb4pg');

    返回資訊如下：

                    dump
---------------------------------------
Typ=705 Len=7: 97,100,98,52,112,103,0
(1 row)

    dump("any", integer)

    參數二表示返迴文本值的內部表示是使用10進位還是16進位，目前僅支援10進位和16進位。

    • SELECT dump('adb4pg', 10);

      返回資訊如下：

                      dump
---------------------------------------
Typ=705 Len=7: 97,100,98,52,112,103,0
(1 row)
    • SELECT dump('adb4pg', 16);

      返回資訊如下：

                     dump
------------------------------------
Typ=705 Len=7: 61,64,62,34,70,67,0
(1 row)
    • 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');
    • SELECT * FROM t1 ORDER BY nlssort(name, 'en_US.UTF-8');

      返回資訊如下：

      name
------
anne
Anne
bob
Bob
(4 row)
    • SELECT * FROM t1 ORDER BY nlssort(name, 'C');

      返回資訊如下：

      name
------
Anne
Bob
anne
bob
(4 row)

    substr(str text, start int)

    擷取參數一中字串的子串。參數二表示擷取到子串的起始位置 >= start。

    • SELECT substr('adb4pg', 1);

      返回資訊如下：

      substr
--------
adb4pg
(1 row)
    • SELECT substr('adb4pg', 4);

      返回資訊如下：

      substr
--------
4pg
(1 row)

    substr(str text, start int, len int)

    參數三會指定子串的結束位置 >= start and <= end。

    SELECT substr('adb4pg', 5,6);

    返回資訊如下：

    substr
--------
pg
(1 row)

    pg_catalog.substrb(varchar2, integer, integer)

    varchar2類型的擷取子串函數。參數二為start pos，參數三為end pos。

    SELECT  substr('adb4pg'::varchar2, 5,6);

    返回資訊如下：

    
substr
--------
pg
(1 row)

    pg_catalog.substrb(varchar2, integer)

    varchar2類型的擷取子串函數；參數二為start pos，從start pos一直取到字串結束。

    SELECT substr('adb4pg'::varchar2, 4) ;

    返回資訊如下：

    substr
--------
4pg
(1 row)

    pg_catalog.lengthb(varchar2)

    擷取varchar2類型字串占的位元組數。若輸入為null返回null，輸入為空白字元，則返回0。

    • SELECT lengthb('adb4pg'::varchar2) ;

      返回資訊如下：

      lengthb
---------
      6
(1 row)
    • SELECT lengthb('分析型'::varchar2) ;

      返回資訊如下：

      lengthb
---------
      9
(1 row)

    lpad(string char, length int, fill char)

    使用填充字串將左填充到指定長度。

    • 參數一：字串。

    • 參數二：指定長度。

    • 參數三：填充字串fill。

    說明

    當字串是CHAR 類型，PostgreSQL刪除尾隨空格，Oracle不刪除尾隨空格。

    SELECT lpad('abc '::char(4),6,'x');

    返回資訊如下：

      lpad
--------
 xxabc
(1 row)

    lpad(string char, length int)

    使用空格將字串左填充到指定長度。

    SELECT lpad('abc '::char(4),6);

    返回資訊如下：

      lpad
--------
   abc
(1 row)

    regexp_count(string text, pattern text, startPos int, flags text)

    返回一個整數，表示從起始位置開始，模式串在源字串出現的次數。 如果未找到模式串，則該函數返回0。

    • 參數一：源字串。

    • 參數二：模式串。

    • 參數三：搜尋起始位置，為正整數，不允許從源字串末尾搜尋負值。

    • 參數四：字元運算式，可更改函數的預設匹配行為，其可以包含以下一個或多個字元。

      • 'i'：不區分大小寫匹配。預設情況下，區分大小寫和重音。

      • 'c'：區分大小寫和區分重音的匹配。

      • 'n'：英文句點 (.) 與分行符號匹配。 預設情況下，句點與分行符號不匹配。

      • 'm'：將源字串視為多行。預設情況下，源字串被視為單行。

      • 'x'：忽略空白字元。 預設情況下，空白字元與其自身匹配。

    • SELECT regexp_count('a'||CHR(10)||'d', 'a.d', 1, 'n');

      返回資訊如下：

       regexp_count 
--------------
            1
(1 row)
    • SELECT regexp_count('a'||CHR(10)||'d', 'a.d', 1, 'n');

      返回資訊如下：

       regexp_count 
--------------
            1
(1 row)

    regexp_count(string text, pattern text, startPos int)

    返回一個整數，表示從起始位置開始，pattern在string出現的次數。 如果未找到pattern，則該函數返回0。

    SELECT regexp_count('abc', '[a-z]',3);

    返回資訊如下：

     regexp_count
--------------
            1
(1 row)

    regexp_count(string text, pattern text)

    返回一個整數，表示從頭開始，pattern在string出現的次數。 如果未找到pattern，則該函數返回0。

    SELECT regexp_count('abc', '[a-z]');

    返回資訊如下：

     regexp_count
--------------
            3
(1 row)

    regexp_instr(string text, pattern text, startPos int, occurence int, return_opt int, flags text, group int)

    返回一個整數，表示源字串中模式比對項所在的開始或結束位置。

    • 參數一：源字串。

    • 參數二：模式串。

    • 參數三：搜尋起始位置，為正整數，不允許從源字串末尾搜尋負值。

    • 參數四：出現次數，為正整數，指示應搜尋源字串中出現的模式串。 預設值為 1，表示模式串在源字串中第一次出現。

    • 參數五：指示匹配模式串的開始或結束位置。

      • 0（預設值）：返回出現的第一個字元的位置。

      • 1：返回出現後的字元的位置。

    • 參數六：字元運算式，可更改函數的預設匹配行為。更多資訊，請參見REGEXP_COUNT。

    • 參數七：擷取的群組序號，對於帶有擷取的群組的模式串，group為正整數，指示函數應返回模式串中對應序號的擷取的群組。 擷取的群組可以嵌套，它們按照左括弧在模式串中出現的順序進行編號。 如果group為0，則返回與模式串匹配的整個子字串的位置。 如果group值超過模式串中擷取的群組的數量，則該函數返回0。預設值為0。

    SELECT regexp_instr('abxxabc','(a)(b)(c)',1,1,0,'',2);

    返回資訊如下：

     regexp_instr
--------------
            6
(1 row)

    regexp_instr(string text, pattern text, startPos int, occurence int, return_opt int, flags text)

    返回一個整數，表示從起始位置開始，在string中pattern出現第occurence次的開始或結束位置。

    • return_opt：指示匹配模式串的開始或結束位置。

      • 0（預設值）：返回出現的第一個字元的位置。

      • 1：返回出現後的字元的位置。

    • flags：可更改函數的預設匹配行為。更多資訊，請參見REGEXP_COUNT。

    SELECT regexp_instr('abxxabc','(a)(b)(c)',1,1,0,'');

    返回資訊如下：

     regexp_instr
--------------
            5
(1 row)

    regexp_instr(string text, pattern text, startPos int, occurence int, return_opt int)

    返回一個整數，表示從起始位置開始，在string中pattern出現第occurence次的開始或結束位置。

    return_opt：指示匹配模式串的開始或結束位置。

    • 0（預設值）：返回出現的第一個字元的位置。

    • 1：返回出現後的字元的位置。

    SELECT regexp_instr('abc','[a-z]{3}',1,1,1);

    返回資訊如下：

     regexp_instr
--------------
            4
(1 row)

    regexp_instr(string text, pattern text, startPos int, occurence int)

    返回一個整數，表示從起始位置開始，在string中pattern出現第occurence次的位置。

    SELECT regexp_instr('abcd','[a-z]{2}',1,2);

    返回資訊如下：

     regexp_instr
--------------
            3
(1 row)

    regexp_instr(string text, pattern text, startPos int)

    返回一個整數，表示從起始位置開始，在string中pattern出現的位置。

    SELECT regexp_instr('abc','[a-z]',2);

    返回資訊如下：

     regexp_instr
--------------
            2
(1 row)

    regexp_instr(string text, pattern text)

    返回一個整數，表示從頭開始，在string中pattern出現的位置。

    SELECT regexp_instr('abc','[a-z]');

    返回資訊如下：

     regexp_instr
--------------
            1
(1 row)

    regexp_like(string text, pattern text, flags text)

    當模式串在源字串中匹配時返回true，否則返回false。

    • 參數一：源字串。

    • 參數二：模式串。

    • 參數三：字元運算式，可更改函數的預設匹配行為。更多資訊，請參見REGEXP_COUNT。

    SELECT regexp_like('a'||CHR(10)||'d', 'a.d', 'n');

    返回資訊如下：

     regexp_like
-------------
 t
(1 row)

    regexp_like(string text, pattern text)

    當pattern在string中匹配時返回true，否則返回false。

    SELECT regexp_like('abc', '[a-z]');

    返回資訊如下：

     regexp_like
-------------
 t
(1 row)

    regexp_substr(string text, pattern text, startPos int, occurence int, flags text)

    返回源字串中與模式串匹配的子字串。

    • 參數一：源字串。

    • 參數二：模式串。

    • 參數三：搜尋起始位置，為正整數，不允許從源字串末尾搜尋負值。

    • 參數四：出現次數occurence，為正整數，指示應搜尋源字串中出現的模式串。 預設值為1，表示模式串在源字串中第一次出現。

    • 參數五：字元運算式，可更改函數的預設匹配行為。更多資訊，請參見REGEXP_COUNT。

    SELECT regexp_substr('a,bc,def', '[^,]+',1,2,'');

    返回資訊如下：

     regexp_substr
---------------
 bc
(1 row)

    regexp_substr(string text, pattern text, startPos int, occurence int)

    返回從起始位置開始，在string中第occurence次與pattern匹配的子字串。

    SELECT regexp_substr('a,bc,def', '[^,]+',4,2);

    返回資訊如下：

     regexp_substr
---------------
 def
(1 row)

    regexp_substr(string text, pattern text, startPos int)

    返回從起始位置開始，在string中與pattern匹配的子字串。

    SELECT regexp_substr('a,bc,def', '[^,]+',4);

    返回資訊如下：

     regexp_substr
---------------
 c
(1 row)

    regexp_substr(string text, pattern text)

    返回從頭開始，在string中與pattern匹配的子字串。

    SELECT regexp_substr('a,bc,def', '[^,]+');

    返回資訊如下：

     regexp_substr
---------------
 a
(1 row)

    Orafce外掛程式除了提供上述相容函數，還對Oracle的Varchar2資料類型提供了相容。

    對於以下的四個Oracle函數，在AnalyticDB PostgreSQL中，無需安裝Orafce外掛程式就可以提供相容支援。

    函數

    描述

    樣本

    sinh(float)

    雙曲正弦值。

    SELECT sinh(0.1);

    返回資訊如下：

          sinh
-------------------
0.100166750019844
(1 row)

    tanh(float)

    雙曲正切值。

    SELECT  tanh(3);

    返回資訊如下：

          tanh
------------------
0.99505475368673
(1 row)

    cosh(float)

    雙曲餘弦值。

    SELECT cosh(0.2);

    返回資訊如下：

          cosh
------------------
1.02006675561908
(1 row)

    decode(expression, value, return [,value,return]... [, default])

    在運算式中尋找一個搜尋值，搜尋到則返回指定的值。如果沒有搜尋到，返回預設值。

    建立表並插入資料：

    CREATE TABLE t1(id int, name varchar(20));
INSERT INTO t1 values(1,'alibaba');
INSERT INTO t1 values(2,'adb4pg');
    • SELECT decode(id, 1, 'alibaba', 2, 'adb4pg', 'not found') from t1;

      返回資訊如下：

       case
---------
alibaba
adb4pg
(2 rows)
    • SELECT decode(id, 3, 'alibaba', 4, 'adb4pg', 'not found') from t1;

      返回資訊如下：

        case
-----------
not found
not found
(2 rows)

    資料類型轉換對照表

    Oracle

    AnalyticDB PostgreSQL

    VARCHAR2

    varchar or text

    DATE

    timestamp

    LONG

    text

    LONG RAW

    bytea

    CLOB

    text

    NCLOB

    text

    BLOB

    bytea

    RAW

    bytea

    ROWID

    oid

    FLOAT

    double precision

    DEC

    decimal

    DECIMAL

    decimal

    DOUBLE PRECISION

    double precision

    INT

    int

    INTEGER

    integer

    REAL

    real

    SMALLINT

    smallint

    NUMBER

    numeric

    BINARY_FLOAT

    double precision

    BINARY_DOUBLE

    double precision

    TIMESTAMP

    timestamp

    XMLTYPE

    xml

    BINARY_INTEGER

    integer

    PLS_INTEGER

    integer

    TIMESTAMP WITH TIME ZONE

    timestamp with time zone

    TIMESTAMP WITH LOCAL TIME ZONE

    timestamp with time zone

    系統函數轉換對照表

    Oracle

    AnalyticDB PostgreSQL

    sysdate

    current timestamp

    trunc

    trunc或date trunc

    dbms_output.put_line

    raise語句

    decode

    轉成case when或直接使用decode

    NVL

    coalesce

    PL/SQL遷移指導

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

    Package

    PL/PGSQL不支援Package，需要把Package轉換成schema，同時Package裡面的所有procedure和function也需要轉換成AnalyticDB PostgreSQL的function。

    例如：

    CREATE OR REPLACE PACKAGE pkg IS 

…

END;

    轉換成：

    CREATE SCHEMA pkg;

    • Package定義的變數

      Procedure/Function的局部變數保持不變，全域變數在AnalyticDB PostgreSQL中可以使用暫存資料表進行儲存。

    • Package初始化塊

      請刪除，若無法刪除請使用Function封裝，在需要的時候主動調用該Function。

    • Package內定義的Procedure/Function

      Package內定義的Procedure和Function需要轉成AnalyticDB PostgreSQL的Function，並把Function定義到Package對應的Schema內。

      例如，有一個Package名為pkg中有如下函數：

      FUNCTION test_func (args int) RETURN int is
var number := 10;
BEGIN
… …
END;

      轉換成如下AnalyticDB PostgreSQL的Function：

      CREATE OR REPLACE FUNCTION pkg. test_func(args int) RETURNS int AS
$$

  … …

$$
 LANGUAGE plpgsql;

    Procedure/Function

    Oracle中的Procedure和Function，不論屬於Package的還是屬於全域，都需要轉換成AnalyticDB PostgreSQL的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需要轉換成AnalyticDB PostgreSQL的Function。

    PL statement

    • FOR語句：

      PL/SQL和PL/PGSQL在帶有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;

    • PRAGMA語句

      AnalyticDB PostgreSQL中沒有PRAGMA語句，刪除該類語句。

    • 交易處理

      AnalyticDB PostgreSQL的Function內部無法使用事務控制語句，如begin，commit，rollback等。

      修改方法如下：

      • 刪除函數體內的事務控制語句，把事務控制放在函數體外。

      • 把函數按照commit或rollback拆分成多個。

    • EXECUTE語句

      AnalyticDB PostgreSQL支援類似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);

    • Pipe row

      Pipe row函數，可使用AnalyticDB PostgreSQL的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);

    • 異常處理

      • 使用raise拋出異常。

      • Catch異常後，不能rollback事務，只能在udf外做rollback。

      • AnalyticDB PostgreSQL支援的Error，請參見PostgreSQL官網。

    • Function中同時有Return和OUT參數

      在AnalyticDB PostgreSQL中，Function無法同時存在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中取對應欄位的傳回值即可。

    • 字串串連中變數含有單引號

      在如下樣本中，變數param2是一個字串類型。假設param2的值為adb'-'pg。下面的sql_str直接放到AnalyticDB PostgreSQL中使用會將-識別成一個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';

    • 擷取兩個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;

    PL資料類型

    • Record

      使用AnalyticDB PostgreSQL的複合資料型別替換。

      樣本：

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

      轉換成：

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

    • Nest table

      • Nest table作為PL變數，可以使用AnalyticDB PostgreSQL的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替換。

    • Associative Array

      無替換類型。

    • Variable-Size Arrays

      與Nest table類似，使用array類型替換。

    • Global variables

      目前AnalyticDB PostgreSQL不支援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為這個表的分布列，由於AnalyticDB PostgreSQL中不允許對於分布列的修改，需要加一個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;。

    SQL

    • Connect by

      Oracle層次查詢，AnalyticDB PostgreSQL沒有等價替換的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;

    • Rownum

      1. 限定查詢結果集大小，可以使用limit替換。

        樣本：

        SELECT * FROM t WHERE rownum < 10;

        轉換成：

        SELECT * FROM t LIMIT 10;

      2. 使用row_number() over()產生rownum。

        樣本：

        SELECT rownum, * FROM t;

        轉換成：

        SELECT row_number() over() AS rownum, * FROM t;

    • Dual表

      1. 去掉Dual。

        樣本：

        SELECT sysdate FROM dual;

        轉換成：

        SELECT current_timestamp;

      2. 建立一個叫dual的表。

    • Select中的udf

      AnalyticDB PostgreSQL支援在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;

    • （+）多表外連結

      AnalyticDB PostgreSQL不支援（+）文法形式，需要轉換成標準的Outer Join文法。

      樣本：

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

    • Merge Into

      通常情況下Merge Into文法可以通過INSERT ON CONFLICT文法進行適配，但是Merge Into文法的部分功能INSERT ON CONFLICT文法暫時無法實現，需要使用預存程序解決。

      INSERT ON CONFLICT文法更多介紹，請參見使用INSERT ON CONFLICT覆蓋寫入資料。

      預存程序的更多介紹，請參見預存程序。

    • Sequence

      樣本：

      CREATE SEQUENCE seq1;
SELECT seq1.nextval FROM dual;

      轉換成：

      CREATE SEQUENCE seq1;
SELECT nextval('seq1');

    • 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。但是在AnalyticDB PostgreSQL中則無法重發重複開啟，需要改寫成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;