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

参数名称

说明

adb_compatibility_mode

兼容性模式，取值如下：

• postgres（默认值）：开启PostgreSQL兼容性模式。

• oracle：开启Oracle兼容性模式。

配置参数之前建议先通过SHOW adb_compatibility_mode;查看当前参数的值。

如果要修改数据库实例级别参数，请提交工单联系技术支持进行修改。

函数

描述

示例

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。

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)

函数

描述

示例

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