Oracle文法相容對比 Oracle vs AntDB vs Postgresql
1. DDL
create,alter,drop,truncate
2. DML
insert,update,delete
3. DQL
select
4. DCL
grant,revoke,alter password
5. TCL
commit,rollback,savepoint
6. 資料類型
ORACLE | AntDB | Postgresql |
varchar2 | varchar2 | varchar |
char(n) | char(n) | char(n) |
date(日期) | date(日期) | timestamp(時間日期型)、date(日期)、time(時間) |
number(n) | number(n) | smallint、int、bigint |
number(p,n) | number(p,n) | numeric(p,n)(低效)、float(高效) |
clob | clob | text |
blob | blob | bytea |
rownum | rownum | 無 |
rowid | rowid | ctid |
7. 系統函數
原生支援: 原生支援: √;不支援:╳;擴充支援 擴充支援 :○
函數類型 | 函數名稱 | ORACLE | AntDB | Postgresql |
數值函數 | ABS | √ | √ | √ |
ACOS | √ | √ | √ |
ASIN | √ | √ | √ |
ATAN | √ | √ | √ |
ATAN2 | √ | √ | √ |
BITAND | √ | √ | √ |
CEIL | √ | √ | √ |
COS | √ | √ | √ |
COSH | √ | √ | √ |
EXP | √ | √ | √ |
FLOOR | √ | √ | √ |
LN | √ | √ | √ |
LOG | √ | √ | √ |
MOD | √ | √ | √ |
NANVL | √ | √ | ○ |
POWER | √ | √ | √ |
ROUND (number) | √ | √ | √ |
SIGN | √ | √ | √ |
SIN | √ | √ | √ |
SINH | √ | √ | ○ |
SQRT | √ | √ | √ |
TAN | √ | √ | √ |
TANH | √ | √ | ○ |
TRUNC (number) | √ | √ | √ |
字元函數 | CHR | √ | √ | √ |
CONCAT | √ | √ | √ |
INITCAP | √ | √ | √ |
LOWER | √ | √ | √ |
LPAD | √ | √ | √ |
LTRIM | √ | √ | √ |
REGEXP_REPLACE | √ | √ | √ |
REGEXP_SUBSTR | √ | √ | ╳ |
REPLACE | √ | √ | √ |
RPAD | √ | √ | √ |
RTRIM | √ | √ | √ |
SUBSTR | √ | √ | √ |
TRANSLATE | √ | √ | √ |
TREAT | √ | ╳ | ╳ |
TRIM | √ | √ | √ |
UPPER | √ | √ | √ |
ASCII | √ | √ | √ |
INSTR | √ | √ | ○ |
LENGTH | √ | √ | √ |
REGEXP_INSTR | √ | √ | ╳ |
REVERSE | √ | √ | √ |
日期函數 | ADD_MONTHS | √ | √ | ○ |
CURRENT_DATE | √ | √ | √ |
CURRENT_TIMESTAMP | √ | √ | √ |
EXTRACT (datetime) | √ | √ | √ |
LAST_DAY | √ | √ | ○ |
LOCALTIMESTAMP | √ | ╳ 關鍵字 | ╳ 關鍵字 |
MONTHS_BETWEEN | √ | √ | ○ |
NEW_TIME | √ | √ | ╳ |
NEXT_DAY | √ | √ | ○ |
ROUND (date) | √ | √ | ╳ |
SYSDATE | √ | √ | ╳ |
SYSTIMESTAMP | √ | √ | ╳ |
TO_CHAR (datetime) | √ | √ | √ |
TO_TIMESTAMP | √ | √ | √ |
TRUNC (date) | √ | √ | √ |
編碼解碼函數 | DECODE | √ | √ | ○ |
DUMP | √ | √ | ○ |
空值比較函數 | COALESCE | √ | √ | √ |
LNNVL | √ | √ | ○ |
NANVL | √ | √ | ○ |
NULLIF | √ | √ | √ |
NVL | √ | √ | ○ |
NVL2 | √ | √ | ○ |
通用數值比較函數 | GREATEST | √ | √ | √ |
LEAST | √ | √ | √ |
類型轉換函數 | CAST | √ | √ | √ |
CONVERT | √ | √ | ○ |
TO_CHAR (character) | √ | √ | √ |
TO_CHAR (datetime) | √ | √ | √ |
TO_CHAR (number) | √ | √ | √ |
TO_DATE | √ | √ | √ |
TO_NUMBER | √ | √ | √ |
TO_TIMESTAMP | √ | √ | √ |
分析函數 | AVG * | √ | √ | √ |
COUNT * | √ | √ | √ |
DENSE_RANK | √ | √ | √ |
FIRST | √ | ╳ | ╳ |
FIRST_VALUE * | √ | √ | √ |
LAG | √ | √ | √ |
LAST | √ | ╳ | ╳ |
LAST_VALUE * | √ | √ | √ |
LEAD | √ | √ | √ |
MAX * | √ | √ | √ |
MIN * | √ | √ | √ |
RANK | √ | √ | √ |
ROW_NUMBER | √ | √ | √ |
SUM * | √ | √ | √ |
8. SQL運算符
SQL運算符類型 | 運算符名稱 | ORACLE | AntDB | Postgresql |
算數運算符 | + | √ | √ | √ |
- | √ | √ | √ |
* | √ | √ | √ |
/ | √ | √ | √ |
邏輯運算符 | and | √ | √ | √ |
or | √ | √ | √ |
not | √ | √ | √ |
比較運算符 | != | √ | √ | √ |
<> | √ | √ | √ |
^= | √ | ╳ | ╳ |
= | √ | √ | √ |
< | √ | √ | √ |
> | √ | √ | √ |
<= | √ | √ | √ |
>= | √ | √ | √ |
is (not) null | √ | √ | √ |
(not) between and | √ | √ | √ |
(not)in | √ | √ | √ |
all/any | √ | √ | √ |
exists | √ | √ | √ |
like | √ | √ | √ |
連接配接運算符 | ll | √ | √ | √ |
合并運算符 | union (all) | √ | √ | √ |
minus | √ | √ | except |
intersect | √ | ╳ | √ |
9. 查詢
SQL查詢類型 | 名稱 | ORACLE | AntDB | Postgresql |
去重 | distinct | √ | √ | √ |
unique | √ | ╳ | ╳ |
分組 | group by | √ | √ | √ |
過濾 | having | √ | √ | √ |
排序 | order by | √ | √ | √ |
遞歸 | connect by | √ | √ | ╳ |
cte | cte | √ | √ | √ |
case when | case when | √ | √ | √ |
批量insert | insert all into | √ | ╳ insert into values | ╳ insert into values |
merge into | merge into | √ | ╳ upsert | ╳ upsert |
10. 表連接配接
表連接配接類型 | 表連接配接名稱 | ORACLE | AntDB | Postgresql |
内連接配接 | (inner) join | √ | √ | √ |
from tableA,tableB | √ | √ | √ |
左連接配接 | left (outer) join | √ | √ | √ |
右連接配接 | right (outer) join | √ | √ | √ |
全連接配接 | full (outer) join | √ | √ | √ |
(+) | (+) | √ | √ | ╳ |
11. 視圖/函數/存儲過程/觸發器
類型 | 名稱 | ORACLE | AntDB | Postgresql |
視圖 | create view | √ | √ | √ |
alter view | √ | √ | √ |
drop view | √ | √ | √ |
函數 | create fuction | √ | √ | √ |
alter fuction | √ | √ | √ |
drop fuction | √ | √ | √ |
存儲過程 | create procedure | √ | √ | √ |
alter procedure | √ | √ | √ |
drop procedure | √ | √ | √ |
觸發器 | create trigger | √ | √ | √ |
alter trigger | √ | √ | √ |
drop trigger | √ | √ | √ |
12. sequence
類型 | 名稱 | ORACLE | AntDB | Postgresql |
建立序列 | create sequence | √ | √ | √ |
修改序列 | alter sequence | √ | √ | √ |
删除序列 | drop sequence | √ | √ | √ |
操作序列 | seq.nextVal | √ | √ | ╳ nextVal(‘seq’) |
seq.currVal | √ | √ | ╳ currVal(‘seq’) |
13. 其他
類型 | 名稱 | ORACLE | AntDB | Postgresql |
過程語言 | declare | √ | √ | √ |
exception | √ | √ | √ |
cursor | √ | √ | √ |
自定義type | create type | √ | √ | √ |
alter type | √ | √ | √ |
drop type | √ | √ | √ |
資料類型隐式轉換 | 隐式轉換 | √ | √ | ╳ |
oracle别名 | oracle别名 | √ | √ | ╳ |
類型複制 | %type | √ | √ | √ |
%rowtype | √ | √ | √ |
like通配符 | % | √ | √ | √ |
_ | √ | √ | √ |
dual虛拟表 | dual | √ | √ | ╳ |