备注：

• 对标 Oracle 的版本：11.2.0.4
• 为了美化表格显示，将以下数据库名称进行简写
  • PostgreSQL --> PGSQL
  • openGauss --> OG
• 本文仅供参考

DUAL 表的支持

ROWNUM 的支持

• 测试示例：

select * from emp where rownum <= 8;

• PostgreSQL 替代 ROWNUM 的方式

-- limit
postgres=# select * from emp limit 8;

-- 窗口函数 row_number() over ()
SELECT *
FROM (SELECT row_number() OVER () rownum, * FROM emp) b
WHERE rownum <= 8;

字段类型

备注：这里的支持理解为能否在数据库中直接创建带有以下类型的表，而并非转换其他类型。

函数

以下仅对Oracle的函数和相关数据库做了简单查询使用的对比，针对复杂情况请自行测试，本文仅供参考。

字符函数

上面各函数在 Oracle 中执行的例子

SQL> SELECT ASCII('a') a FROM dual;  --97
SQL> SELECT CONCAT('Hello','world') a FROM dual;  --Helloworld
SQL> SELECT INSTR('Hello world','or') a FROM dual;  --8
SQL> SELECT LENGTH('Hello') a FROM dual;  --5
SQL> SELECT LENGTHB('Hello') a FROM dual;  --5
SQL> SELECT LOWER('Hello') a FROM dual;  --hello
SQL> SELECT UPPER('hello') a FROM dual;  --HELLO
SQL> SELECT INITCAP('hello') a FROM dual;  --HELLO
SQL> SELECT LTRIM('=Hello=','=') a FROM dual;  --Hello=
SQL> SELECT RTRIM('=Hello=','=') a FROM dual;  --=Hello
SQL> SELECT TRIM('='FROM'=Hello=') a FROM dual;  --Hello
SQL> SELECT REPLACE('ABCDE','CD','AAA') a FROM dual;  --ABAAAE
SQL> SELECT SUBSTR('ABCDE',2,3) a FROM dual;  --BCD
SQL> SELECT SUBSTRB('ABCDE',2,3) a FROM dual;  --BCD
SQL> select LPAD('hElloWoRlD',15,'&') a from dual;  --&&&&&hElloWoRlD
SQL> select RPAD('hElloWoRlD',15,'&') a from dual;  --hElloWoRlD&&&&&

SQL> SELECT CHR(67)||CHR(65)||CHR(84) "Dog" from dual;  --CAT
SQL> SELECT NCHR(187) from dual;  -->
SQL> SELECT NLS_INITCAP('ijsland') "a" from dual;  --Ijsland
SQL> SELECT NLS_LOWER('ijsland') "a" from dual;  --ijsland
SQL> SELECT NLS_UPPER('ijsland') "a" from dual;  --IJSLAND
SQL> SELECT NLSSORT('ijsland') "a" from dual;  --696A736C616E6400
SQL> SELECT REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA', '( ){2,}', ' ') "REGEXP_REPLACE" FROM DUAL;  --500 Oracle Parkway, Redwood Shores, CA
SQL> SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR" FROM DUAL;  --, Redwood Shores,
SQL> SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL;  --3
SQL> SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) "REGEXP_INSTR" FROM DUAL;  --37
SQL> select SOUNDEX('b') from dual;  --B000
SQL> SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;  --SQL_Plus_Users_Guide

SQL> select trim('    helloworld    ') from dual;

TRIM('HELL
----------
helloworld


mysql> select trim('    helloworld    ') from dual;
+----------------------------+
| trim('    helloworld    ') |
+----------------------------+
| helloworld                 |
+----------------------------+
1 row in set (0.00 sec)

数字函数

以下情况为什么需要显式转换呢

oracle=# \df LOG
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 oracle     | log  | "number"         | "number"            | func
 oracle     | log  | "number"         | "number", "number"  | func
 pg_catalog | log  | double precision | double precision    | func
 pg_catalog | log  | numeric          | numeric             | func
 pg_catalog | log  | numeric          | numeric, numeric    | func
(5 rows)

oracle=# select LOG(2,4) a from dual;
ERROR:  function log(integer, integer) is not unique
LINE 1: select LOG(2,4) a from dual;
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
oracle=# show search_path;
        search_path
----------------------------
 public, oracle, pg_catalog
(1 row)

oracle=# select LOG(2::number,4) a from dual;
 a
---
 2
(1 row)

oracle=# select LOG(2::numeric,4) a from dual;
         a
--------------------
 2.0000000000000000
(1 row)

NANVL

SQL> CREATE TABLE float_point_demo (dec_num NUMBER(10,2), bin_double BINARY_DOUBLE, bin_float BINARY_FLOAT);

Table created.

SQL> INSERT INTO float_point_demo VALUES (0,'NaN','NaN');

1 row created.

SQL> SELECT * FROM float_point_demo;

   DEC_NUM BIN_DOUBLE  BIN_FLOAT
---------- ---------- ----------
         0        Nan        Nan

SQL> SELECT bin_float, NANVL(bin_float,0) FROM float_point_demo;

 BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
       Nan                  0

BINARY_DOUBLE 和 BINARY_FLOAT 字段类型只有 openGauss 2.1 支持创建

openGauss=# CREATE TABLE float_point_demo (bin_double BINARY_DOUBLE, bin_float BINARY_FLOAT);
CREATE TABLE
openGauss=# \d+ float_point_demo
                         Table "public.float_point_demo"
   Column   |       Type       | Modifiers | Storage | Stats target | Description
------------+------------------+-----------+---------+--------------+-------------
 bin_double | double precision |           | plain   |              |
 bin_float  | real             |           | plain   |              |
Has OIDs: no
Options: orientation=row, compression=no

日期函数

上面各函数在 Oracle 中执行的例子

SQL> select sysdate from dual;  --2022-04-02 23:10:36
SQL> select add_months(sysdate,2) from dual;  --2022-06-02 23:10:59
SQL> select last_day(sysdate) from dual;  --2022-04-30 23:11:25
SQL> select months_between(sysdate,to_date('20181120','yyyymmdd')) a from dual;  --40.4505373
SQL> select next_day(sysdate,'Monday') from dual;  --2022-04-04 23:12:45
SQL> select extract(YEAR FROM SYSDATE) "year" from dual;  --2022
SQL> select round(to_date('20190715','yyyymmdd'),'month') from dual;  --2019-07-01 00:00:00
SQL> select round(to_date('20190716','yyyymmdd'),'month') from dual;  --2019-08-01 00:00:00
SQL> select trunc(to_date('20190716','yyyymmdd'),'month') from dual;  --2019-07-01 00:00:00
SQL> select trunc(sysdate,'year') from dual;  --2022-01-01 00:00:00
SQL> SELECT TO_CHAR(sysdate) FROM DUAL;  --2022-04-03 07:53:06

SQL> SELECT CURRENT_DATE FROM DUAL;  --2022-04-03 06:59:24
SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;  --03-APR-22 07.01.50.391024 AM +08:00
SQL> SELECT DBTIMEZONE FROM DUAL;  --+00:00
SQL> select SESSIONTIMEZONE from dual;  --+08:00
SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') FROM DUAL;  --28-MAR-00 08.00.00.000000000 AM +03:00
SQL> SELECT SYSTIMESTAMP FROM DUAL;  --03-APR-22 07.44.24.146176 AM +08:00
SQL> select LOCALTIMESTAMP from dual;  --03-APR-22 07.15.28.945537 AM
SQL> SELECT NEW_TIME(TO_DATE('11-10-09 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time" FROM DUAL;  --2009-11-09 21:23:45
SQL> select NUMTODSINTERVAL(100, 'day') from dual;  --+000000100 00:00:00.000000000
SQL> select NUMTOYMINTERVAL(1,'year') from dual;  --+000000001-00
SQL> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00') FROM DUAL;  --28-MAR-00 07.30.00.000000000 PM
SQL> SELECT TO_CHAR(TIMESTAMP '2009-01-01 00:00:00' + TO_DSINTERVAL('P100DT05H'), 'YYYY-MM-DD HH24:MI:SS') "Time Stamp" FROM DUAL;  --2009-04-11 05:00:00
SQL> SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')  FROM DUAL;  --10-SEP-02 02.10.10.123000000 PM
SQL> SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;  --01-DEC-99 11.00.00.000000000 AM -08:00
SQL> select TO_YMINTERVAL('01-02')FROM DUAL;  --+000000001-02
SQL> SELECT TZ_OFFSET('US/Eastern') FROM DUAL;  ---04:00

转换函数

上面各函数在 Oracle 中执行的例子

SQL> SELECT ASCIISTR('ABÄCDE') FROM DUAL;  --AB\00C4CDE
SQL> SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL;  --10
SQL> SELECT CAST('22-OCT-1997' AS TIMESTAMP) FROM DUAL;  --22-OCT-97 12.00.00.000000 AM
SQL> select CHARTOROWID('AAAFd1AAFAAAABSAA/') from dual;  --AAAFd1AAFAAAABSAA/
SQL> SELECT COMPOSE( 'o' || UNISTR('\0308') ) from dual;  --ö
SQL> SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;  --A E I ? ? A B C D E ? 
postgres=# select convert('abc中国'::bytea, 'UTF8', 'GBK');  --\x616263d6d0b9fa
SQL> select HEXTORAW('4041424344') FROM DUAL;  --4041424344
SQL> SELECT TO_CHAR('01110') FROM DUAL;  --01110
SQL> SELECT TO_CHAR('01110' + 1) FROM DUAL;  --1111
SQL> select to_date('20190715','yyyymmdd') from dual;  --2019-07-15 00:00:00
SQL> SELECT dump(TO_MULTI_BYTE( 'A')) FROM DUAL;  --Typ=1 Len=2: 163,193
SQL> SELECT TO_NUMBER('1111') from dual;  --1111
SQL> SELECT TO_SINGLE_BYTE('aaa') FROM DUAL;  --aaa
SQL> SELECT UNISTR('abc\00e5\00f1\00f6') FROM DUAL;  --abcåñö

聚合函数

上面各函数在 Oracle 中执行的例子

drop table tt CASCADE;
create table tt (col1 int,col3 char);
insert into tt values (1001,'1');
insert into tt values (2001,'2');
insert into tt values (3001,'3');
insert into tt values (4001,'4');

SQL> SELECT LISTAGG(col1,':') WITHIN GROUP (ORDER BY col1) from tt;

LISTAGG(COL1,':')WITHINGROUP(ORDERBYCOL1)
--------------------------------------------------------------------------------
1001:1002:2002:3001

SQL> SELECT wm_concat(col3) from tt;

WM_CONCAT(COL3)
---------------------------------------------------------------------------------
1,2,3,4

SQL> select MEDIAN(col1) from tt;

MEDIAN(COL1)
------------
        2501

• VM_CONCAT 在 PostgreSQL 和 IvorySQL 中只能转换字符类型，不能转换数值类型。
• VM_CONCAT 在 Oracle 用的比较多，openGauss 系列的数据库不支持的话，可以使用下面的方式手工创建这个函数

CREATE OR REPLACE FUNCTION vm_concat_state_func (results text, val text)
RETURNS text
LANGUAGE sql COST 50 IMMUTABLE
AS $$ select results || ',' ||val; $$;

CREATE OR REPLACE FUNCTION vm_concat_final_func (results text)
RETURNS text
LANGUAGE sql COST 111 IMMUTABLE
AS $$ select substr(results, 2); $$;

CREATE AGGREGATE wm_concat(text)
(
  sfunc = vm_concat_state_func,
  stype = text,
  initcond = '',
  FINALFUNC = vm_concat_final_func
);

General Comparison Functions

上面各函数在 Oracle 中执行的例子

SQL> SELECT GREATEST('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL;  --HARRY
SQL> SELECT GREATEST(1, 3.925, 2.4) "Greatest" FROM DUAL;  --3.925
SQL> SELECT LEAST('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL;  --HAROLD
SQL> SELECT LEAST(1, 3.925, 2.4) "Greatest" FROM DUAL;  --1

Encoding and Decoding Functions

上面各函数在 Oracle 中执行的例子

drop table tt;
create table tt (col1 int,col3 int);
insert into tt values (1001,1),(2001,2),(3001,3),(4001,4);

SELECT col1,
       DECODE(col3, 1, 'one',
                    2, 'two',
                    3, 'three',
                    'other number') "num-word"
       FROM tt;

 col1 |   num-word
------+--------------
 1001 | one
 2001 | two
 3001 | three
 4001 | other number

SQL> SELECT DUMP('abc') from dual;  --Typ=96 Len=3: 97,98,99
SQL> SELECT VSIZE('abc') from dual;  --3

NULL-Related Functions

上面各函数在 Oracle 中执行的例子

drop table tt;
create table tt (col1 int,col3 int);
insert into tt values (1002,2000);
insert into tt values (2002,null);

SQL> select * from tt;

      COL1       COL3
---------- ----------
      1002       2000
      2002

SQL> SELECT col1,col3 FROM tt WHERE LNNVL( col3 > 2000 );

      COL1       COL3
---------- ----------
      1002       2000
      2002

SQL> SELECT col1,NULLIF(col3,col1) FROM tt;

      COL1 NULLIF(COL3,COL1)
---------- -----------------
      1002              2000
      2002

SQL> SELECT col1, NVL(col3,0) "nvl" FROM tt;

      COL1        nvl
---------- ----------
      1002       2000
      2002          0
SQL> SELECT col1, NVL2(col3,'IS NOT NULL','IS NULL') FROM tt;

      COL1 NVL2(COL3,'
---------- -----------
      1002 IS NOT NULL
      2002 IS NULL

SQL> SELECT col1, NVL2(col3,0,1) FROM tt;

      COL1 NVL2(COL3,0,1)
---------- --------------
      1002              0
      2002              1

Oracle 的其他函数

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions002.htm#SQLRF20034