全部产品
Search

搜索本产品

    云原生数据仓库AnalyticDB：Oracle应用迁移至云原生数据仓库 AnalyticDB PostgreSQL 版

    文档中心

    云原生数据仓库AnalyticDB：Oracle应用迁移至云原生数据仓库 AnalyticDB PostgreSQL 版

    更新时间：Jan 04, 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与空字符串''等价。

      重要

      使用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;