備注：
- 對標(biāo) Oracle 的版本：11.2.0.4
- 為了美化表格顯示，將以下數(shù)據(jù)庫名稱進行簡寫
- PostgreSQL --> PGSQL
- openGauss --> OG
- 本文僅供參考
DUAL 表的支持
| 表名 |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| DUAL |
N |
Y |
N |
N |
Y |
Y |
ROWNUM 的支持
| 類型 |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| ROWNUM |
N |
N |
Y |
Y |
N |
N |
select * from emp where rownum <= 8;
postgres=# select * from emp limit 8;
SELECT *
FROM (SELECT row_number() OVER () rownum, * FROM emp) b
WHERE rownum <= 8;
字段類型
備注：這里的支持理解為能否在數(shù)據(jù)庫中直接創(chuàng)建帶有以下類型的表，而并非轉(zhuǎn)換其他類型。
| 字段 |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| CHAR |
Y |
Y |
Y |
Y |
Y |
Y |
| VARCHAR2 |
N |
Y |
Y |
Y |
N |
Y |
| NVARCHAR2 |
N |
Y |
Y |
Y |
N |
Y |
| NUMBER |
N |
N |
Y |
Y |
N |
Y |
| FLOAT |
Y |
Y |
Y |
Y |
Y |
Y |
| DATE |
只存儲日期 |
Y |
Y |
Y |
只存儲日期 |
Y |
| TIMESTAMP(6) |
Y |
Y |
Y |
Y |
Y |
Y |
| BLOB |
N |
N |
Y |
Y |
Y |
N |
| CLOB |
N |
N |
Y |
Y |
N |
N |
| NCLOB |
N |
N |
N |
N |
N |
N |
| LONG |
N |
N |
N |
N |
Y |
N |
| LONG RAW |
N |
N |
N |
N |
N |
N |
| RAW |
N |
N |
Y |
Y |
N |
N |
函數(shù)
以下僅對Oracle的函數(shù)和相關(guān)數(shù)據(jù)庫做了簡單查詢使用的對比，針對復(fù)雜情況請自行測試，本文僅供參考。
字符函數(shù)
| 函數(shù) |
說明 |
| ASCII(X) |
返回字符X的ASCII碼 |
| CONCAT(X,Y) |
連接字符串X和Y |
| INSTR(X,STR[,START][,N]) |
從X中查找str，可以指定從start開始，也可以指定從n開始 |
| LENGTH(X) |
返回X的長度(字符個數(shù)) |
| LENGTHB(X) |
返回X的長度(字節(jié)數(shù)) |
| LOWER(X) |
X轉(zhuǎn)換成小寫 |
| UPPER(X) |
X轉(zhuǎn)換成大寫 |
| INITCAP(X) |
將X中的第一個字母轉(zhuǎn)換成大寫 |
| LTRIM(X[,TRIM_STR]) |
把X的左邊截去trim_str字符串，缺省截去空格 |
| RTRIM(X[,TRIM_STR]) |
把X的右邊截去trim_str字符串，缺省截去空格 |
| TRIM([TRIM_STR FROM]X) |
把X的兩邊截去trim_str字符串，缺省截去空格 |
| REPLACE(X,old,new) |
在X中查找old，并替換成new |
| SUBSTR(X,start[,length]) |
返回X的字串，從start處開始，截取length個字符，缺省length，默認(rèn)到結(jié)尾 |
| SUBSTRB(X,start[,length]) |
- |
| LPAD(x,n,y) |
在字符串x的左邊補齊空格，得到總長為n個字符的字符串。其中y是可選項，這個參數(shù)用于指定左邊補齊的字符串 |
| RPAD(x,n,y) |
在字符串x的右邊補齊空格，得到總長為n個字符的字符串。其中y是可選項，這個參數(shù)用于指定右邊補齊的字符串 |
| - |
- |
| CHR(n) |
- |
| NCHR(number) |
- |
| NLS_INITCAP(char) |
- |
| NLS_LOWER(char) |
- |
| NLS_UPPER(char) |
- |
| NLSSORT(char) |
- |
| REGEXP_REPLACE(source_char,pattern,replace_string) |
- |
| REGEXP_SUBSTR(source_char,pattern) |
- |
| REGEXP_COUNT(source_char,pattern) |
- |
| REGEXP_INSTR(source_char,pattern) |
- |
| SOUNDEX(char) |
- |
| TRANSLATE(expr,from_string,to_string) |
- |
上面各函數(shù)在 Oracle 中執(zhí)行的例子
SQL> SELECT ASCII('a') a FROM dual;
SQL> SELECT CONCAT('Hello','world') a FROM dual;
SQL> SELECT INSTR('Hello world','or') a FROM dual;
SQL> SELECT LENGTH('Hello') a FROM dual;
SQL> SELECT LENGTHB('Hello') a FROM dual;
SQL> SELECT LOWER('Hello') a FROM dual;
SQL> SELECT UPPER('hello') a FROM dual;
SQL> SELECT INITCAP('hello') a FROM dual;
SQL> SELECT LTRIM('=Hello=','=') a FROM dual;
SQL> SELECT RTRIM('=Hello=','=') a FROM dual;
SQL> SELECT TRIM('='FROM'=Hello=') a FROM dual;
SQL> SELECT REPLACE('ABCDE','CD','AAA') a FROM dual;
SQL> SELECT SUBSTR('ABCDE',2,3) a FROM dual;
SQL> SELECT SUBSTRB('ABCDE',2,3) a FROM dual;
SQL> select LPAD('hElloWoRlD',15,'&') a from dual;
SQL> select RPAD('hElloWoRlD',15,'&') a from dual;
SQL> SELECT CHR(67)||CHR(65)||CHR(84) "Dog" from dual;
SQL> SELECT NCHR(187) from dual;
SQL> SELECT NLS_INITCAP('ijsland') "a" from dual;
SQL> SELECT NLS_LOWER('ijsland') "a" from dual;
SQL> SELECT NLS_UPPER('ijsland') "a" from dual;
SQL> SELECT NLSSORT('ijsland') "a" from dual;
SQL> SELECT REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ') "REGEXP_REPLACE" FROM DUAL;
SQL> SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR" FROM DUAL;
SQL> SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL;
SQL> SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) "REGEXP_INSTR" FROM DUAL;
SQL> select SOUNDEX('b') from dual;
SQL> SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;
| 函數(shù) |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| ASCII |
Y |
Y |
Y |
Y |
Y |
Y |
| CONCAT |
Y |
Y |
Y |
Y |
Y |
Y |
| INSTR |
N |
Y |
Y |
Y |
Y |
Y |
| LENGTH |
Y |
Y |
Y |
Y |
Y |
Y |
| LENGTHB |
N |
Y |
Y |
Y |
N |
Y |
| LOWER |
Y |
Y |
Y |
Y |
Y |
Y |
| UPPER |
Y |
Y |
Y |
Y |
Y |
Y |
| INITCAP |
Y |
Y |
Y |
Y |
N |
Y |
| LTRIM |
Y |
Y |
Y |
Y |
N |
Y |
| RTRIM |
Y |
Y |
Y |
Y |
N |
Y |
| TRIM |
Y |
Y |
Y |
Y |
Y |
Y |
| REPLACE |
Y |
Y |
Y |
Y |
Y |
Y |
| SUBSTR |
Y |
Y |
Y |
Y |
Y |
Y |
| SUBSTRB |
N |
Y |
Y |
Y |
N |
Y |
| LPAD |
Y |
Y |
Y |
Y |
Y |
Y |
| RPAD |
Y |
Y |
Y |
Y |
Y |
Y |
| - |
- |
- |
- |
- |
- |
- |
| CHR |
Y |
Y |
Y |
Y |
N |
Y |
| NCHR |
N |
N |
N |
N |
N |
N |
| NLS_INITCAP |
N |
N |
N |
N |
N |
N |
| NLS_LOWER |
N |
N |
N |
N |
N |
N |
| NLS_UPPER |
N |
N |
N |
N |
N |
N |
| NLSSORT |
N |
Y |
N |
N |
N |
Y |
| REGEXP_REPLACE |
結(jié)果不一致 |
Y |
結(jié)果不一致 |
結(jié)果不一致 |
Y |
Y |
| REGEXP_SUBSTR |
N |
Y |
Y |
Y |
Y |
Y |
| REGEXP_COUNT |
N |
Y |
N |
N |
N |
Y |
| REGEXP_INSTR |
N |
Y |
N |
N |
Y |
Y |
| SOUNDEX |
N |
N |
N |
N |
Y |
N |
| TRANSLATE |
Y |
Y |
Y |
Y |
N |
Y |
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)
數(shù)字函數(shù)
| 函數(shù) |
說明 |
示例 |
| ABS(X) |
X的絕對值 |
ABS(-3)=3 |
| COS(X) |
X的余弦 |
COS(1)=0.540302306 |
| ACOS(X) |
X的反余弦 |
ACOS(1)=0 |
| SIN(X) |
X的正弦 |
SIN(1)=0.841470985 |
| ASIN(X) |
X的反正弦 |
ASIN(1)=1.57079633 |
| TAN(X) |
X的正切 |
TAN(1)=1.55740772 |
| ATAN(X) |
X的反切 |
ATAN(1)=0.785398163 |
| CEIL(X) |
大于或等于X的最小值 |
CEIL(5.4)=6 |
| FLOOR(X) |
小于或等于X的最大值 |
FLOOR(5.8)=5 |
| LOG(X,Y) |
X為底Y的對數(shù) |
LOG(2,4)=2 |
| MOD(X,Y) |
X除以Y的余數(shù) |
MOD(8,3)=2 |
| POWER(X,Y) |
X的Y次冪 |
POWER(2,3)=8 |
| ROUND(X[,Y]) |
X在第Y位四舍五入 |
ROUND(3.456,2)=3.46 |
| SQRT(X) |
X的平方根 |
SQRT(4)=2 |
| TRUNC(X[,Y]) |
X在第Y位截斷 |
TRUNC(3.456,2)=3.45 |
| - |
- |
- |
| ATAN2(n1,n2) |
- |
ATAN2(.3, .2)=0.982793723 |
| BITAND(expr1,expr2) |
- |
BITAND(6,3)=2 |
| COSH(n) |
- |
COSH(0)=1 |
| EXP(n) |
- |
EXP(4)=54.59815 |
| LN(n) |
- |
LN(95)=4.55387689 |
| NANVL(n2,n1) |
- |
- |
| REMAINDER(n2,n1) |
- |
- |
| SIGN(n) |
- |
SIGN(-15)=-1 |
| SINH(n) |
- |
SINH(1)=1.17520119 |
| TANH(n) |
- |
TANH(.5)=0.462117157 |
| WIDTH_BUCKET(expr, min_value, max_value, num_buckets) |
- |
- |
| 函數(shù) |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| ABS |
Y |
Y |
Y |
Y |
Y |
Y |
| COS |
Y |
Y |
Y |
Y |
Y |
Y |
| ACOS |
Y |
Y |
Y |
Y |
Y |
Y |
| SIN |
Y |
Y |
Y |
Y |
Y |
Y |
| ASIN |
Y |
Y |
Y |
Y |
Y |
Y |
| TAN |
Y |
Y |
Y |
Y |
Y |
Y |
| ATAN |
Y |
Y |
Y |
Y |
Y |
Y |
| CEIL |
Y |
Y |
Y |
Y |
Y |
Y |
| FLOOR |
Y |
Y |
Y |
Y |
Y |
Y |
| LOG |
Y |
Y |
Y |
Y |
Y |
顯式轉(zhuǎn)換 |
| MOD |
Y |
Y |
Y |
Y |
Y |
Y |
| POWER |
Y |
Y |
Y |
Y |
Y |
Y |
| ROUND (number) |
Y |
Y |
Y |
Y |
Y |
Y |
| SQRT |
Y |
Y |
Y |
Y |
Y |
Y |
| TRUNC (number) |
Y |
Y |
Y |
Y |
N |
Y |
| - |
- |
- |
- |
- |
- |
- |
| ATAN2 |
Y |
Y |
Y |
Y |
Y |
Y |
| BITAND |
N |
Y |
Y |
Y |
N |
Y |
| COSH |
Y |
Y |
N |
N |
N |
Y |
| EXP |
Y |
Y |
Y |
Y |
Y |
Y |
| LN |
Y |
Y |
Y |
Y |
Y |
Y |
| NANVL |
N |
Y |
N |
N |
N |
Y |
| REMAINDER |
N |
N |
N |
N |
N |
N |
| SIGN |
Y |
Y |
Y |
Y |
Y |
Y |
| SINH |
Y |
Y |
N |
N |
N |
Y |
| TANH |
Y |
Y |
N |
N |
N |
Y |
| WIDTH_BUCKET |
Y |
Y |
Y |
Y |
- |
Y |
以下情況為什么需要顯式轉(zhuǎn)換呢
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 支持創(chuàng)建
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
日期函數(shù)
| 函數(shù) |
說明 |
| SYSDATE |
獲得當(dāng)前系統(tǒng)時間 |
| ADD_MONTHS(d,n) |
在某一個日期 d 上，加上指定的月數(shù) n，返回計算后的新日期。 |
| LAST_DAY(d) |
返回指定日期當(dāng)月的最后一天。 |
| MONTHS_BETWEEN(x,y) |
給出Date(y) - Date(x) 的月數(shù)(可以是小數(shù)) |
| NEXT_DAY(x,day) |
返回從x開始一周內(nèi)，下一個day的日期，這里的day為星期 |
| EXTRACT(fmt FROM d) |
提取日期中的特定部分。 |
| ROUND(d[,fmt]) |
返回一個以 fmt 為格式的四舍五入日期值， d 是日期， fmt 是格式 |
| TRUNC(d[,fmt]) |
返回一個以 fmt 為格式的截斷日期值（不進行四舍五入）， d 是日期， fmt 是格式 |
| TO_CHAR(datetime/interval) |
將日期格式轉(zhuǎn)為字符格式 |
| - |
- |
| CURRENT_DATE |
- |
| CURRENT_TIMESTAMP |
- |
| DBTIMEZONE |
- |
| SESSIONTIMEZONE |
- |
| FROM_TZ(timestamp_value,time_zone_value) |
- |
| SYSTIMESTAMP |
- |
| LOCALTIMESTAMP |
- |
| NEW_TIME(date,timezone1,timezone2) |
- |
| NUMTODSINTERVAL(n,interval_unit) |
- |
| NUMTOYMINTERVAL(n,interval_unit) |
- |
| SYS_EXTRACT_UTC(datetime_with_timezone) |
- |
| TO_DSINTERVAL |
- |
| TO_TIMESTAMP |
- |
| TO_TIMESTAMP_TZ |
- |
| TO_YMINTERVAL |
- |
| TZ_OFFSET |
- |
上面各函數(shù)在 Oracle 中執(zhí)行的例子
SQL> select sysdate from dual;
SQL> select add_months(sysdate,2) from dual;
SQL> select last_day(sysdate) from dual;
SQL> select months_between(sysdate,to_date('20181120','yyyymmdd')) a from dual;
SQL> select next_day(sysdate,'Monday') from dual;
SQL> select extract(YEAR FROM SYSDATE) "year" from dual;
SQL> select round(to_date('20190715','yyyymmdd'),'month') from dual;
SQL> select round(to_date('20190716','yyyymmdd'),'month') from dual;
SQL> select trunc(to_date('20190716','yyyymmdd'),'month') from dual;
SQL> select trunc(sysdate,'year') from dual;
SQL> SELECT TO_CHAR(sysdate) FROM DUAL;
SQL> SELECT CURRENT_DATE FROM DUAL;
SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;
SQL> SELECT DBTIMEZONE FROM DUAL;
SQL> select SESSIONTIMEZONE from dual;
SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') FROM DUAL;
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SQL> select LOCALTIMESTAMP from dual;
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;
SQL> select NUMTODSINTERVAL(100, 'day') from dual;
SQL> select NUMTOYMINTERVAL(1,'year') from dual;
SQL> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00') FROM DUAL;
SQL> SELECT TO_CHAR(TIMESTAMP '2009-01-01 00:00:00' + TO_DSINTERVAL('P100DT05H'), 'YYYY-MM-DD HH24:MI:SS') "Time Stamp" FROM DUAL;
SQL> SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;
SQL> SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
SQL> select TO_YMINTERVAL('01-02')FROM DUAL;
SQL> SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
| 函數(shù) |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| SYSDATE |
N |
加括號 |
Y |
Y |
加括號 |
Y |
| ADD_MONTHS |
N |
Y |
Y |
Y |
N |
Y |
| LAST_DAY |
N |
Y |
Y |
Y |
只返回日期 |
Y |
| MONTHS_BETWEEN |
N |
小數(shù)部分不準(zhǔn)確 |
N |
N |
N |
Y |
| NEXT_DAY |
N |
Y |
Y |
Y |
N |
Y |
| EXTRACT (datetime) |
Y |
Y |
Y |
Y |
Y |
Y |
| ROUND (date) |
N |
Y |
N |
N |
結(jié)果不一致 |
Y |
| TRUNC (date) |
N |
Y |
N |
Y |
N |
Y |
| TO_CHAR (datetime) |
N |
Y |
Y |
Y |
N |
Y |
| - |
- |
- |
- |
- |
- |
- |
| CURRENT_DATE |
只返回日期 |
只返回日期 |
只返回日期 |
只返回日期 |
只返回日期 |
只返回日期 |
| CURRENT_TIMESTAMP |
Y |
Y |
Y |
Y |
不帶時區(qū) |
Y |
| DBTIMEZONE |
N |
加括號 |
N |
N |
N |
加括號 |
| SESSIONTIMEZONE |
N |
加括號 |
N |
N |
N |
加括號 |
| FROM_TZ |
N |
N |
N |
N |
N |
Y |
| SYSTIMESTAMP |
N |
N |
Y |
N |
N |
Y |
| LOCALTIMESTAMP |
Y |
Y |
Y |
Y |
Y |
Y |
| NEW_TIME |
N |
N |
N |
N |
N |
Y |
| NUMTODSINTERVAL |
N |
Y |
Y |
Y |
N |
Y |
| NUMTOYMINTERVAL |
N |
N |
N |
N |
N |
Y |
| SYS_EXTRACT_UTC |
N |
N |
N |
N |
N |
結(jié)果不準(zhǔn)確 |
| TO_DSINTERVAL |
N |
N |
N |
N |
N |
Y |
| TO_TIMESTAMP |
Y |
Y |
Y |
Y |
N |
Y |
| TO_TIMESTAMP_TZ |
N |
N |
N |
N |
N |
Y |
| TO_YMINTERVAL |
N |
N |
N |
N |
N |
Y |
| TZ_OFFSET |
N |
N |
N |
N |
N |
N |
轉(zhuǎn)換函數(shù)
| 函數(shù) |
說明 |
| ASCIISTR |
- |
| BIN_TO_NUM |
- |
| CAST |
- |
| CHARTOROWID |
- |
| COMPOSE |
- |
| CONVERT |
- |
| DECOMPOSE |
- |
| HEXTORAW |
- |
| NUMTODSINTERVAL |
同上 |
| NUMTOYMINTERVAL |
同上 |
| TO_CHAR (character) |
- |
| TO_CHAR (datetime) |
同上 |
| TO_CHAR (number) |
- |
| TO_DATE |
- |
| TO_DSINTERVAL |
同上 |
| TO_MULTI_BYTE |
- |
| TO_NUMBER |
- |
| TO_SINGLE_BYTE |
- |
| TO_TIMESTAMP |
同上 |
| TO_TIMESTAMP_TZ |
同上 |
| TO_YMINTERVAL |
同上 |
| UNISTR |
- |
| RAWTOHEX |
- |
| RAWTONHEX |
- |
| ROWIDTOCHAR |
- |
| ROWIDTONCHAR |
- |
| SCN_TO_TIMESTAMP |
- |
| TIMESTAMP_TO_SCN |
- |
| TO_BINARY_DOUBLE |
- |
| TO_BINARY_FLOAT |
- |
| TO_BLOB |
- |
| TO_CLOB |
- |
| TO_LOB |
- |
| TO_NCHAR (character) |
- |
| TO_NCHAR (datetime) |
- |
| TO_NCHAR (number) |
- |
| TO_NCLOB |
- |
| TREAT |
- |
上面各函數(shù)在 Oracle 中執(zhí)行的例子
SQL> SELECT ASCIISTR('AB?CDE') FROM DUAL;
SQL> SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL;
SQL> SELECT CAST('22-OCT-1997' AS TIMESTAMP) FROM DUAL;
SQL> select CHARTOROWID('AAAFd1AAFAAAABSAA/') from dual;
SQL> SELECT COMPOSE( 'o' || UNISTR('\0308') ) from dual;
SQL> SELECT CONVERT('? ê í ? ? A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;
postgres=# select convert('abc中國'::bytea, 'UTF8', 'GBK');
SQL> select HEXTORAW('4041424344') FROM DUAL;
SQL> SELECT TO_CHAR('01110') FROM DUAL;
SQL> SELECT TO_CHAR('01110' + 1) FROM DUAL;
SQL> select to_date('20190715','yyyymmdd') from dual;
SQL> SELECT dump(TO_MULTI_BYTE( 'A')) FROM DUAL;
SQL> SELECT TO_NUMBER('1111') from dual;
SQL> SELECT TO_SINGLE_BYTE('aaa') FROM DUAL;
SQL> SELECT UNISTR('abc\00e5\00f1\00f6') FROM DUAL;
| 函數(shù) |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| ASCIISTR |
N |
N |
N |
N |
N |
N |
| BIN_TO_NUM |
N |
N |
N |
N |
N |
Y |
| CAST |
Y |
Y |
Y |
Y |
N |
Y |
| CHARTOROWID |
N |
N |
N |
N |
N |
N |
| COMPOSE |
N |
N |
N |
N |
N |
N |
| CONVERT |
Y |
Y |
Y |
Y |
Y |
Y |
| DECOMPOSE |
N |
N |
N |
N |
N |
N |
| HEXTORAW |
N |
N |
Y |
Y |
N |
N |
| TO_CHAR (character) |
N |
N |
Y |
Y |
N |
Y |
| TO_CHAR (number) |
N |
Y |
Y |
Y |
N |
Y |
| TO_DATE |
只返回日期 |
Y |
Y |
Y |
N |
Y |
| TO_MULTI_BYTE |
N |
Y |
N |
N |
N |
Y |
| TO_NUMBER |
N |
Y |
Y |
Y |
N |
Y |
| TO_SINGLE_BYTE |
N |
Y |
N |
N |
N |
Y |
| UNISTR |
N |
Y |
N |
N |
N |
Y |
| RAWTOHEX |
- |
- |
- |
- |
- |
- |
| RAWTONHEX |
- |
- |
- |
- |
- |
- |
| ROWIDTOCHAR |
- |
- |
- |
- |
- |
- |
| ROWIDTONCHAR |
- |
- |
- |
- |
- |
- |
| SCN_TO_TIMESTAMP |
- |
- |
- |
- |
- |
- |
| TIMESTAMP_TO_SCN |
- |
- |
- |
- |
- |
- |
| TO_BINARY_DOUBLE |
- |
- |
- |
- |
- |
- |
| TO_BINARY_FLOAT |
- |
- |
- |
- |
- |
- |
| TO_BLOB |
- |
- |
- |
- |
- |
- |
| TO_CLOB |
- |
- |
- |
- |
- |
- |
| TO_LOB |
- |
- |
- |
- |
- |
- |
| TO_NCHAR (character) |
- |
- |
- |
- |
- |
- |
| TO_NCHAR (datetime) |
- |
- |
- |
- |
- |
- |
| TO_NCHAR (number) |
- |
- |
- |
- |
- |
- |
| TO_NCLOB |
- |
- |
- |
- |
- |
- |
| TREAT |
- |
- |
- |
- |
- |
- |
聚合函數(shù)
| 函數(shù) |
說明 |
| LISTAGG |
- |
| VM_CONCAT |
- |
| MEDIAN |
- |
上面各函數(shù)在 Oracle 中執(zhí)行的例子
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
| 函數(shù) |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| LISTAGG |
N |
顯式轉(zhuǎn)換 |
Y |
Y |
N |
顯式轉(zhuǎn)換 |
| MEDIAN |
N |
Y |
Y |
Y |
N |
Y |
| VM_CONCAT |
Y |
Y |
N |
N |
N |
Y |
- VM_CONCAT 在 PostgreSQL 和 IvorySQL 中只能轉(zhuǎn)換字符類型，不能轉(zhuǎn)換數(shù)值類型。
- VM_CONCAT 在 Oracle 用的比較多，openGauss 系列的數(shù)據(jù)庫不支持的話，可以使用下面的方式手工創(chuàng)建這個函數(shù)
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
| 函數(shù) |
說明 |
| GREATEST |
- |
| LEAST |
- |
上面各函數(shù)在 Oracle 中執(zhí)行的例子
SQL> SELECT GREATEST('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL;
SQL> SELECT GREATEST(1, 3.925, 2.4) "Greatest" FROM DUAL;
SQL> SELECT LEAST('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL;
SQL> SELECT LEAST(1, 3.925, 2.4) "Greatest" FROM DUAL;
| 函數(shù) |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| GREATEST |
Y |
Y |
Y |
Y |
Y |
Y |
| LEAST |
Y |
Y |
Y |
Y |
Y |
Y |
Encoding and Decoding Functions
| 函數(shù) |
說明 |
| DECODE |
- |
| DUMP |
- |
| ORA_HASH |
- |
| VSIZE |
- |
上面各函數(shù)在 Oracle 中執(zhí)行的例子
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;
SQL> SELECT VSIZE('abc') from dual;
| 函數(shù) |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| DECODE |
N |
Y |
Y |
Y |
N |
Y |
| DUMP |
N |
Y |
N |
N |
N |
Y |
| ORA_HASH |
- |
- |
- |
- |
- |
- |
| VSIZE |
N |
N |
N |
N |
N |
Y |
NULL-Related Functions
| 函數(shù) |
說明 |
| COALESCE |
- |
| LNNVL |
- |
| NANVL |
同上 |
| NULLIF |
- |
| NVL |
- |
| NVL2 |
- |
上面各函數(shù)在 Oracle 中執(zhí)行的例子
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
| 函數(shù) |
PGSQL 13.3 |
orafce 3.19 |
OG 2.1 |
OG 3.0 |
mysql 8.0 |
IvorySQL 1.2 |
| COALESCE |
- |
- |
- |
- |
- |
- |
| LNNVL |
N |
Y |
N |
N |
N |
Y |
| NULLIF |
Y |
Y |
Y |
Y |
Y |
Y |
| NVL |
N |
Y |
Y |
Y |
N |
Y |
| NVL2 |
N |
Y |
N |
N |
N |
Y |
Oracle 的其他函數(shù)
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions002.htm#SQLRF20034
