几个开源数据库和插件对 oracle 字段类型和函数的兼容情况 -m6米乐安卓版下载

备注：

• 对标 oracle 的版本：11.2.0.4
• 为了美化表格显示，将以下数据库名称进行简写
  • postgresql --> pgsql
  • opengauss --> og
• 本文仅供参考

• 测试示例：

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 的其他函数