4.2.2.6. 转换为 OceanBase-MySQL
4.2.2.6.1. 数值函数
4.2.2.6.1.1. BITAND
- 语法
BITAND (expr1,expr2)- 描述
- 该函数将其输入参数进行二进制按位与操作
参数解释
参数 |
说明 |
|
|---|---|---|
expr1 |
参数1 |
NUMBER 类型表达式 |
expr2 |
参数2 |
NUMBER 类型表达式 |
示例
-- 转换前Oracle SQL：
SELECT BITAND(0,0),BITAND(0,-1),BITAND(0,NULL),BITAND(1,2) FROM DUAL;
BITAND(0,0)|BITAND(0,-1)|BITAND(0,NULL)|BITAND(1,2)|
-----------+------------+--------------+-----------+
0| 0| | 0|
-- 转换后oceanbase-mysql：
SELECT 0&0,0&-1,0&NULL,1&2 FROM DUAL;
0&0|0&-1|0&NULL|1&2|
---+----+------+---+
0| 0| | 0|
4.2.2.6.1.2. DBMS_RANDOM.VALUE
- 语法
DBMS_RANDOM.VALUE- 描述
- 返回一个随机码，此随机码需满足大于或等于 0，小于 1
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT DBMS_RANDOM.VALUE from dual;
VALUE |
----------------------------------------+
0.90603062118926722027812535155007101231|
-- 转换后oceanbase-mysql：
SELECT rand() FROM dual;
rand() |
------------------+
0.2516990529854773|
4.2.2.6.1.3. DBMS_RANDOM.RANDOM
- 语法
DBMS_RANDOM.RANDOM- 描述
- 返回一个在 [-2^31，2^31）范围区间内的随机整数
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT DBMS_RANDOM.RANDOM from dual;
RANDOM |
----------+
-886930169|
-- 转换后oceanbase-mysql：
SELECT floor(power(-2, 31)+rand()*power(2, 32)) FROM dual;
floor(power(-2, 31)+rand()*power(2, 32))|
----------------------------------------+
-278630359|
4.2.2.6.1.4. MOD
- 语法
MOD(N,M)- 描述
- 取余函数
参数解释
参数 |
说明 |
|---|---|
N |
被除数 |
M |
除数 |
示例
-- 转换前Oracle SQL：
SELECT MOD(11,4) "Modulus",MOD(11,4),MOD(11,-4),MOD(-11,4),MOD(-11,-4) FROM DUAL;
Modulus | mod | mod | mod | mod
---------+-----+-----+-----+-----
3 | 3 | 3 | -3 | -3
-- 转换后oceanbase-mysql：
SELECT if(concat('a', 4)='a0', 11, MOD(11, 4)) AS `Modulus`,if(concat('a', 4)='a0', 11, MOD(11, 4)),if(concat('a', -4)='a0', 11, MOD(11, -4)),if(concat('a', 4)='a0', -11, MOD(-11, 4)),if(concat('a', -4)='a0', -11, MOD(-11, -4)) FROM DUAL;
Modulus|if(concat('a', 4)='a0', 11, MOD(11, 4))|if(concat('a', -4)='a0', 11, MOD(11, -4))|if(concat('a', 4)='a0', -11, MOD(-11, 4))|if(concat('a', -4)='a0', -11, MOD(-11, -4))|
-------+---------------------------------------+-----------------------------------------+-----------------------------------------+-------------------------------------------+
3| 3| 3| -3| -3|
4.2.2.6.1.5. TRUNC(number)
- 语法
TRUNC (numeric[,precision])- 描述
- 该函数返回参数 numeric 按精度 precision 截取后的值
参数解释
参数 |
说明 |
|---|---|
numeric |
表示被截取的数字 |
precision |
表示精度，为可选项，默认值为 0 |
示例
-- 转换前Oracle SQL：
SELECT TRUNC(555.666,2.2),TRUNC(555.666,-2),TRUNC(555.666) FROM DUAL;
------------------+-----------------+--------------+
TRUNC(555.666,2.2)|TRUNC(555.666,-2)|TRUNC(555.666)|
------------------+-----------------+--------------+
555.66| 500| 555|
-- 转换后oceanbase-mysql：
SELECT truncate(555.666, 2.2),truncate(555.666, -2),truncate(555.666, 0) FROM DUAL;
truncate(555.666, 2.2)|truncate(555.666, -2)|truncate(555.666, 0)|
----------------------+---------------------+--------------------+
555.66| 500| 555|
4.2.2.6.2. 字符串函数
4.2.2.6.2.1. LENGTH
- 语法
LENGTH(str)- 描述
- 该函数返回 str 的字符长度
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
示例
-- 转换前Oracle SQL：
SELECT LENGTH('中国'), LENGTH('hello') FROM DUAL;
LENGTH('中国')|LENGTH('HELLO')|
------------+---------------+
2| 5|
-- 转换后oceanbase-mysql：
SELECT char_length('中国'),char_length('hello') FROM DUAL
char_length('中国')|char_length('hello')|
-----------------+--------------------+
2| 5|
-- 在Oracle中，\\n会被视为两个字符，在Mysql中，\\n会被视为一个字符，下面语句执行后会返回6
-- 转换前Oracle SQL：
SELECT LENGTH('abc\n\t\r') FROM dual
LENGTH('ABC\N\T\R')|
-------------------+
9|
-- 转换后oceanbase-mysql：
SELECT char_length('abc\n\t\r') FROM dual;
char_length('abc\n\t\r')|
------------------------+
6|
4.2.2.6.2.2. LENGTHB
- 语法
LENGTHB(str)- 描述
- 该函数返回 str 的字节长度，与字符集有关
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
示例
-- 转换前Oracle SQL：
SELECT LENGTHB('中国'), LENGTHB('hello') FROM DUAL;
LENGTHB('中国')|LENGTHB('HELLO')|
-------------+---------------+
6| 5|
-- 转换后oceanbase-mysql：
SELECT length('中国'),char_length('hello') FROM DUAL;
length('中国')|char_length('hello')|
------------+--------------------+
6| 5|
-- 在Oracle中，\\n会被视为两个字节长度，在Mysql中，\\n会被视为一个字节长度，下面语句执行后会返回6
-- 转换前Oracle SQL：
SELECT LENGTHB('abc\n\t\r') FROM dual;
LENGTHB('ABC\N\T\R')|
--------------------+
9|
-- 转换后oceanbase-mysql：
SELECT length('abc\n\t\r') FROM dual;
length('abc\n\t\r')|
-------------------+
6|
4.2.2.6.2.3. LPAD
- 语法
LPAD(str,len,padstr)- 描述
- 使用字符串 padstr 从左侧填充字符串 str，直到长度为 len 时为止
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
len |
要填充长度 |
padstr |
要填充的字符串 |
示例
-- 转换前Oracle SQL：
SELECT
LPAD('1223', 3),
LPAD('1223', 4),
LPAD('1223', 5),
LPAD('1223', 6, '2+1'),
LPAD('1223', 7, ''),
LPAD('1223', 0),
LPAD('1223',-1)
FROM dual;
LPAD('1223',3)|LPAD('1223',4)|LPAD('1223',5)|LPAD('1223',6,'2+1')|LPAD('1223',7,'')|LPAD('1223',0)|LPAD('1223',-1)|
--------------+--------------+--------------+--------------------+-----------------+--------------+---------------+
122 |1223 | 1223 |2+1223 | | | |
-- 转换后oceanbase-mysql：
SELECT
if(length('1223')>=3, LPAD('1223', 3, ''), '1223'),
if(length('1223')>=4, LPAD('1223', 4, ''), '1223'),
if(length('1223')>=5, LPAD('1223', 5, ''), '1223'),
if('2+1' IS NULL OR length('2+1')=0, lpad('1223', 6, NULL), LPAD('1223', 6, '2+1')),
if('' IS NULL OR length('')=0, lpad('1223', 7, NULL), LPAD('1223', 7, '')),
if(length('1223')>=0, LPAD('1223', 0, ''), '1223'),
if(length('1223')>=-1, LPAD('1223', -1, ''), '1223')
FROM dual;
if(length('1223')>=3, LPAD('1223', 3, ''), '1223')|if(length('1223')>=4, LPAD('1223', 4, ''), '1223')|if(length('1223')>=5, LPAD('1223', 5, ''), '1223')|if('2+1' IS NULL OR length('2+1')=0, lpad('1223', 6, NULL), LPAD('1223', 6, '2+1'))|if('' IS NULL OR length('')=0, lpad('1223', 7, NULL), LPAD('1223', 7, ''))|if(length('1223')>=0, LPAD('1223', 0, ''), '1223')|if(length('1223')>=-1, LPAD('1223', -1, ''), '1223')|
--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------+--------------------------------------------------------------------------+--------------------------------------------------+----------------------------------------------------+
122 |1223 |1223 |2+1223 | | | |
4.2.2.6.2.4. RPAD
- 语法
RPAD(str,len,padstr)- 描述
- 使用字符串 padstr 从右侧填充字符串 str，直到长度为 len 时为止
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
len |
要填充长度 |
padstr |
要填充的字符串 |
示例
-- 转换前Oracle SQL：
SELECT
RPAD('1223', 3),
RPAD('1223', 4),
RPAD('1223', 5),
RPAD('1223', 6, '2+1'),
RPAD('1223', 7, ''),
RPAD('1223', 0),
RPAD('1223',-1)
FROM
dual;
RPAD('1223',3)|RPAD('1223',4)|RPAD('1223',5)|RPAD('1223',6,'2+1')|RPAD('1223',7,'')|RPAD('1223',0)|RPAD('1223',-1)|
--------------+--------------+--------------+--------------------+-----------------+--------------+---------------+
122 |1223 |1223 |12232+ | | | |
-- 转换后oceanbase-mysql：
SELECT
if(length('1223')>=3, RPAD('1223', 3, ''), '1223'),
if(length('1223')>=4, RPAD('1223', 4, ''), '1223'),
if(length('1223')>=5, RPAD('1223', 5, ''), '1223'),
if('2+1' IS NULL OR length('2+1')=0, rpad('1223', 6, NULL), RPAD('1223', 6, '2+1')),
if('' IS NULL OR length('')=0, rpad('1223', 7, NULL), RPAD('1223', 7, '')),
if(length('1223')>=0, RPAD('1223', 0, ''), '1223'),if(length('1223')>=-1, RPAD('1223', -1, ''), '1223')
FROM dual;
if(length('1223')>=3, RPAD('1223', 3, ''), '1223')|if(length('1223')>=4, RPAD('1223', 4, ''), '1223')|if(length('1223')>=5, RPAD('1223', 5, ''), '1223')|if('2+1' IS NULL OR length('2+1')=0, rpad('1223', 6, NULL), RPAD('1223', 6, '2+1'))|if('' IS NULL OR length('')=0, rpad('1223', 7, NULL), RPAD('1223', 7, ''))|if(length('1223')>=0, RPAD('1223', 0, ''), '1223')|if(length('1223')>=-1, RPAD('1223', -1, ''), '1223')|
--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------+--------------------------------------------------------------------------+--------------------------------------------------+----------------------------------------------------+
122 |1223 |1223 |12232+ | | | |
4.2.2.6.2.5. SUBSTR
语法
SUBSTR(str, pos)
SUBSTR(str, pos, len)
- 描述
- 返回 str 的子字符串，起始位置为 pos，长度为 len。参数中包含 NULL 时，返回 NULL
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
pos |
子字符串的起始位置 |
len |
子字符串的长度 |
示例
-- 转换前Oracle SQL：
SELECT SUBSTR('abcdefg', 3),SUBSTR('abcdefg', 3, 2),SUBSTR('abcdefg', -3),SUBSTR('abcdefg', 3, -2) FROM DUAL;
SUBSTR('ABCDEFG',3)|SUBSTR('ABCDEFG',3,2)|SUBSTR('ABCDEFG',-3)|SUBSTR('ABCDEFG',3,-2)|
-------------------+---------------------+--------------------+----------------------+
cdefg |cd |efg | |
-- 转换后oceanbase-mysql：
SELECT substr('abcdefg', if(3=0, 1, 3), length('abcdefg')),substr('abcdefg', if(3=0, 1, 3), 2),substr('abcdefg', if(-3=0, 1, -3), length('abcdefg')),substr('abcdefg', if(3=0, 1, 3), -2) FROM DUAL;
substr('abcdefg', if(3=0, 1, 3), length('abcdefg'))|substr('abcdefg', if(3=0, 1, 3), 2)|substr('abcdefg', if(-3=0, 1, -3), length('abcdefg'))|substr('abcdefg', if(3=0, 1, 3), -2)|
---------------------------------------------------+-----------------------------------+-----------------------------------------------------+------------------------------------+
cdefg |cd |efg | |
4.2.2.6.2.6. INSTR
- 语法
INSTR(str,substr)- 描述
- 返回字符串 str 中子字符串的第一个出现位置
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
substr |
子字符串 |
示例
-- 转换前Oracle SQL：
SELECT INSTR('Unisql','U') i1,INSTR('Unisql','u') i2,INSTR('Unisql','n') i3,INSTR('Unisql','a') i4,INSTR('Unisql','') i5,INSTR('Unisql',NULL) i6,INSTR('foobarbar', 'bar') i7, INSTR('xbar', 'foobar') i8,INSTR('北京欢迎你','北') i9 FROM DUAL;
I1|I2|I3|I4|I5|I6|I7|I8|I9|
--+--+--+--+--+--+--+--+--+
1| 0| 2| 0| | | 4| 0| 1|
-- 转换后oceanbase-mysql：
SELECT instr('Unisql', if(BINARY 'U'='', NULL, BINARY 'U')) AS `i1`,instr('Unisql', if(BINARY 'u'='', NULL, BINARY 'u')) AS `i2`,instr('Unisql', if(BINARY 'n'='', NULL, BINARY 'n')) AS `i3`,instr('Unisql', if(BINARY 'a'='', NULL, BINARY 'a')) AS `i4`,instr('Unisql', if(BINARY ''='', NULL, BINARY '')) AS `i5`,instr('Unisql', if(BINARY NULL='', NULL, BINARY NULL)) AS `i6`,instr('foobarbar', if(BINARY 'bar'='', NULL, BINARY 'bar')) AS `i7`,instr('xbar', if(BINARY 'foobar'='', NULL, BINARY 'foobar')) AS `i8`,instr('北京欢迎你', if(BINARY '北'='', NULL, BINARY '北')) AS `i9` FROM DUAL;
i1|i2|i3|i4|i5|i6|i7|i8|i9|
--+--+--+--+--+--+--+--+--+
1| 0| 2| 0| | | 4| 0| 1|
4.2.2.6.2.7. LTRIM
- 语法
LTRIM(char,[set])- 描述
- 从 char 的左端删除集合 set 中包含的所有字符
参数解释
参数 |
说明 |
|---|---|
char |
要操作的字符串 |
set |
要删除的字符集合 |
示例
-- 转换前Oracle SQL：
SELECT LTRIM(' 2023-09-21 ', ' 0123'),LTRIM(' 2023-09-21') FROM DUAL;
LTRIM('2023-09-21','0123')|LTRIM('2023-09-21')|
--------------------------+-------------------+
-09-21 |2023-09-21 |
-- 转换后oceanbase-mysql：
SELECT `unisql`.unisql_ltrim(' 2023-09-21 ', ' 0123'),`unisql`.unisql_ltrim(' 2023-09-21', ' ') FROM DUAL
`unisql`.unisql_ltrim(' 2023-09-21 ', ' 0123')|`unisql`.unisql_ltrim(' 2023-09-21', ' ')|
----------------------------------------------+------------------------------------------+
-09-21 |2023-09-21 |
4.2.2.6.2.8. RTRIM
- 语法
RTRIM(char,[set])- 描述
- 从 char 的右端删除集合 set 中包含的所有字符
参数解释
参数 |
说明 |
|---|---|
char |
要操作的字符串 |
set |
要删除的字符集合 |
示例
-- 转换前Oracle SQL：
SELECT RTRIM(' 2023-09-21 ', ' 0123'),RTRIM(' 2023-09-21') FROM DUAL;
RTRIM('2023-09-21','0123')|RTRIM('2023-09-21')|
--------------------------+-------------------+
2023-09- | 2023-09-21 |
-- 转换后oceanbase-mysql：
SELECT `unisql`.unisql_rtrim(' 2023-09-21 ', ' 0123'),`unisql`.unisql_rtrim(' 2023-09-21', ' ') FROM DUAL
`unisql`.unisql_rtrim(' 2023-09-21 ', ' 0123')|`unisql`.unisql_rtrim(' 2023-09-21', ' ')|
----------------------------------------------+------------------------------------------+
2023-09- | 2023-09-21 |
4.2.2.6.2.9. REGEXP_SUBSTR
语法
REGEXP_SUBSTR(source_char, pattern
[, position [, occurrence ] ])
- 描述
- 该函数允许使用正则表达式搜索字符串并返回匹配字符串
参数解释
参数 |
说明 |
|---|---|
source_char |
指定用作搜索值的字符表达式 |
pattern |
指定正则表达式截取规则。它通常是一个文本字面量 |
position |
指定开始正则表达式匹配的起始位置，取值是一个正整数，为可选项。默认值是 1，表示从第一个字符开始搜索 |
occurrence |
指定 source_char 中第几个正则表达式匹配位置，为可选项。默认值为 1 |
示例
-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+') from dual;
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+')|
---------------------------------------------+
a11 |
-- 转换后oceanbase-mysql：
SELECT `unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1) FROM dual;
`unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1)|
----------------------------------------------------------------------+
a11 |
-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1) from dual;
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+',1)|
-----------------------------------------------+
a11 |
-- 转换后oceanbase-mysql：
SELECT `unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1) FROM dual;
`unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 1)|
----------------------------------------------------------------------+
a11 |
-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1, 2) FROM DUAL;
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+',1,2)|
-------------------------------------------------+
a22 |
-- 转换后oceanbase-mysql：
SELECT `unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 2) FROM DUAL;
`unisql`.`unisql_regexp_substr`('a11a22a33a44', 'a[[:digit:]]+', 1, 2)|
----------------------------------------------------------------------+
a22 |
4.2.2.6.3. 时间日期函数
4.2.2.6.3.1. TRUNC(date)
- 语法
TRUNC(date,[fmt])- 描述
- 该函数返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值，并且返回的日期值在 date 之前
参数解释
参数 |
说明 |
|---|---|
date |
DATE 数据类型 |
fmt |
指定了函数返回值与 date 的距离单位 |
fmt的取值如下：
fmt 参数的取值 |
说明 |
|---|---|
J |
默认值，最近 0 点日期。 |
DAY、DY、D、DDD、DD |
返回离指定日期最近的星期日。 |
MONTH、MON、MM、RM |
返回离指定日期最近的月的第一天日期。 |
Q |
返回离指定日期最近的季的日期。 |
YYYY、YYY、YY、Y |
多个 y 表示不同的精度，返回离指定日期最近的年的第一个日期。 |
CC、SCC |
返回离指定日期最近的世纪的初日期。 |
示例
-- 转换前Oracle SQL：
SELECT TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'YEAR'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'MONTH'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'DDD')FROM DUAL;
TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'YEAR')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'MONTH')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'DDD')|
------------------------------------------------------------------+-------------------------------------------------------------------+-----------------------------------------------------------------+
2022-01-01 00:00:00.000| 2022-04-01 00:00:00.000| 2022-04-19 00:00:00.000|
-- 转换后oceanbase-mysql：
SELECT `unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'YEAR'),`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'MONTH'),`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'DDD') FROM DUAL;
`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'YEAR')|`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'MONTH')|`unisql`.unisql_trunc(CAST(str_to_date('2022/04/19 10:32:34', '%Y/%m/%d %H:%i:%s') AS DATETIME), 'DDD')|
--------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+
2022-01-01 00:00:00.000| 2022-04-01 00:00:00.000| 2022-04-19 00:00:00.000|
4.2.2.6.3.2. LAST_DAY
- 语法
LAST_DAY(date)- 描述
- 返回 date 当月最后一天的日期值
参数解释
参数 |
说明 |
|---|---|
date |
日期类型参数 |
示例
-- 转换前Oracle SQL：
SELECT LAST_DAY(SYSDATE),LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD')) FROM DUAL;
LAST_DAY(SYSDATE) |LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD'))|
-----------------------+--------------------------------------------+
2023-12-31 00:59:15.000| 2023-09-30 00:00:00.000|
-- 转换后oceanbase-mysql：
SELECT date_add(last_day(current_timestamp()), INTERVAL date_format(current_timestamp(), '%H:%i:%s') HOUR_SECOND),date_add(last_day(CAST(str_to_date('2023/09/21', '%Y/%m/%d') AS DATETIME)), INTERVAL date_format(CAST(str_to_date('2023/09/21', '%Y/%m/%d') AS DATETIME), '%H:%i:%s') HOUR_SECOND) FROM DUAL;
date_add(last_day(current_timestamp()), INTERVAL date_format(current_timestamp(), '%H:%i:%s') HOUR_SECOND)|date_add(last_day(CAST(str_to_date('2023/09/21', '%Y/%m/%d') AS DATETIME)), INTERVAL date_format(CAST(str_to_date('2023/09/21', '%Y/%m/%d') AS DATETIME), '%H:%i:%s') HOUR_SECOND)|
----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2023-12-31 00:59:15.000| 2023-09-30 00:00:00.000|
4.2.2.6.3.3. SYSDATE
- 语法
SYSDATE- 描述
- 返回当前日期和时间，MySQL相关数据库可指定时区设置，影响此函数的返回值
- 参数解释
- 无
示例
-- 转换前Oracle SQL：
SELECT SYSDATE FROM DUAL;
SYSDATE |
-----------------------+
2023-12-20 09:51:46.000|
-- 转换后oceanbase-mysql：
SELECT CURRENT_TIMESTAMP() FROM DUAL;
CURRENT_TIMESTAMP() |
-----------------------+
2024-02-02 14:31:05.000|
4.2.2.6.3.4. TO_CHAR (datetime)
- 语法
TO_CHAR({ datetime} [, fmt])- 描述
- 该函数将日期时间按照参数 fmt 指定的格式转换为 VARCHAR2 数据类型的值
参数解释
参数 |
说明 |
|---|---|
datetime |
datetime 属于日期时间， DATE、TIMESTAMP数据类型 |
fmt |
指定输出格式 |
示例
-- 转换前Oracle SQL：
SELECT
TO_CHAR(TO_DATE('2023-04-20', 'YYYY-MM-DD'),'YYYY-MM-DD') AS a1
FROM DUAL;
A1 |
----------+
2023-04-20|
-- 转换后oceanbase-mysql：
SELECT date_format(CAST(str_to_date('2023-04-20', '%Y-%m-%d') AS DATETIME), '%Y-%m-%d') AS `a1` FROM DUAL
a1 |
----------+
2023-04-20|
4.2.2.6.3.5. TO_TIMESTAMP
- 语法
TO_TIMESTAMP (char，[fmt])- 描述
- 该函数将字符串转换为 TIMESTAMP 数据类型
参数解释
参数 |
说明 |
|---|---|
datetime |
datetime 属于日期时间， DATE、TIMESTAMP数据类型 |
fmt |
指定输出格式 |
示例
-- 转换前Oracle SQL：
SELECT
to_timestamp('2020-02-02', 'yyyy-mm-dd') AS a1,
to_timestamp('2020-02-02 02', 'yyyy-mm-dd hh24') AS a2
FROM
dual
A1 |A2 |
-----------------------+-----------------------+
2020-02-02 00:00:00.000|2020-02-02 02:00:00.000|
-- 转换后oceanbase-mysql：
SELECT CAST(str_to_date('2020-02-02', '%Y-%m-%d') AS DATETIME(6)) AS `a1`,CAST(str_to_date('2020-02-02 02', '%Y-%m-%d %H') AS DATETIME(6)) AS `a2` FROM dual;
a1 |a2 |
-----------------------+-----------------------+
2020-02-02 00:00:00.000|2020-02-02 02:00:00.000|
4.2.2.6.3.6. CURRENT_DATE
- 语法
CURRENT_DATE- 描述
- 该函数返回当前会话时区的当前日期
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT CURRENT_DATE FROM DUAL;
-----------------------+
CURRENT_DATE |
-----------------------+
2023-12-20 19:11:29.000|
-- 转换后oceanbase-mysql：
SELECT current_timestamp(0) FROM DUAL;
current_timestamp(0) |
-----------------------+
2023-12-20 19:11:29.000|
4.2.2.6.3.7. CURRENT_TIMESTAMP
- 语法
CURRENT_TIMESTAMP- 描述
- 该函数返回当前会话时区中的当前日期
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP |
-----------------------------+
2023-12-20 19:04:55.457 +0800|
-- 转换后oceanbase-mysql：
SELECT CURRENT_TIMESTAMP() FROM DUAL;
CURRENT_TIMESTAMP() |
-----------------------+
2023-12-20 19:04:55.457|
4.2.2.6.3.8. ADD_MONTHS
- 语法
ADD_MONTHS(date, n)- 描述
- 该函数功能是返回日期 date 加上 n 个月后的日期值。
参数解释
参数 |
说明 |
|---|---|
date |
指定日期。该参数为 DATE 数据类型。 |
n |
整数或可以转换为一个整数的任意值。NUMBER 数据类型。 |
示例
-- 转换前Oracle SQL：
SELECT
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD') ,-3) AS a1,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 3)AS a2,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.1)AS a3,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.5)AS a4,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.8)AS a5,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 0)AS a6
FROM DUAL;
A1 |A2 |A3 |A4 |A5 |A6 |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-21 00:00:00.000|2024-03-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2023-12-21 00:00:00.000|
-- 转换后oceanbase-mysql：
SELECT
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(-3) MONTH) AS `a1`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(3) MONTH) AS `a2`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(1.1) MONTH) AS `a3`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(1.5) MONTH) AS `a4`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(1.8) MONTH) AS `a5`,
date_add(CAST(str_to_date('2023-12-21', '%Y-%m-%d') AS DATETIME), INTERVAL floor(0) MONTH) AS `a6`
FROM DUAL;
a1 |a2 |a3 |a4 |a5 |a6 |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-21 00:00:00.000|2024-03-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2023-12-21 00:00:00.000|
4.2.2.6.4. 转换函数
4.2.2.6.4.1. CAST
- 语法
CAST (expr AS type_name )- 描述
- 该函数用于将一种内置数据类型转换为另一种内置数据类型
参数解释
参数 |
说明 |
|---|---|
expr |
列名或者表达式。 |
AS |
用于分隔两个参数，在 AS 之前的是要处理的数据，在 AS 之后是要转换的数据类型。 |
type_name |
数据库的内建数据类型名称。 |
下表显示了哪些数据类型可以转换为其他内置数据类型
from BINARY_FLOAT / BINARY_DOUBLE |
from CHAR / VARCHAR2 |
from NUMBER |
from DATETIME/INTERVAL |
from RAW |
from NCHAR/ NVARCHAR2 |
|
|---|---|---|---|---|---|---|
to NUMBER |
yes |
yes |
yes |
no |
no |
yes |
警告
使用 CAST 函数转换数据类型时，需要注意以下内容：
对于以上列表中的转换，如果目标库对转换后的目标数据类型不支持，则会在目标库中执行时报错。
对于类似cast(‘6983140795139873811265107033372908879759234567’ AS number)这种超过38位精度的数字，转换后的SQL在目标库执行会存在报错或者精度错误的情况。
在oceanbase-mysql中，CAST to NUMBER的时候存在四舍五入的情况，请使用CAST函数时注意目标类型的精度。
示例
-- 转换前Oracle SQL:
SELECT
CAST('100.2345' AS NUMBER) AS to_nu,
CAST('100.2345' AS NUMBER(*)) AS to_nu,
CAST('100.2345' AS NUMBER(*,0)) AS to_nu,
CAST('100.2345' AS NUMBER(*,2)) AS to_nu,
CAST('100.2345' AS NUMBER(4)) AS to_nu,
CAST('100.2345' AS NUMBER(4,0)) AS to_nu,
CAST('100.2345' AS NUMBER(9)) AS to_nu,
CAST('100.2345' AS NUMBER(9,0)) AS to_nu,
CAST('100.2345' AS NUMBER(18)) AS to_nu,
CAST('100.2345' AS NUMBER(18,0)) AS to_nu,
CAST('100.2345' AS NUMBER(38)) AS to_nu,
CAST('100.2345' AS NUMBER(38,0)) AS to_nu,
CAST('100.2345' AS NUMBER(10,2)) AS to_nu,
cast('100.2345' AS decimal) AS to_dec,
cast('100.2345' AS decimal(*)) AS to_dec,
cast('100.2345' AS decimal(*,0)) AS to_dec,
cast('100.2345' AS decimal(*,2)) AS to_dec,
cast('100.2345' AS decimal(4)) AS to_dec,
cast('100.2345' AS decimal(4,0)) AS to_dec,
cast('100.2345' AS decimal(9)) AS to_dec,
cast('100.2345' AS decimal(9,0)) AS to_dec,
CAST('100.2345' AS decimal(18)) AS to_dec,
CAST('100.2345' AS decimal(18,0)) AS to_dec,
CAST('100.2345' AS decimal(38)) AS to_dec,
CAST('100.2345' AS decimal(38,0)) AS to_dec,
CAST('100.2345' AS decimal(10,2)) AS to_dec,
cast('100.2345' AS dec) AS to_dec,
cast('100.2345' AS dec(*)) AS to_dec,
cast('100.2345' AS dec(*,0)) AS to_dec,
cast('100.2345' AS dec(*,2)) AS to_dec,
cast('100.2345' AS dec(4)) AS to_dec,
cast('100.2345' AS dec(4,0)) AS to_dec,
cast('100.2345' AS dec(9)) AS to_dec,
cast('100.2345' AS dec(9,0)) AS to_dec,
CAST('100.2345' AS dec(18)) AS to_dec,
CAST('100.2345' AS dec(18,0)) AS to_dec,
CAST('100.2345' AS dec(38)) AS to_dec,
CAST('100.2345' AS dec(38,0)) AS to_dec,
CAST('100.2345' AS dec(10,2)) AS to_dec,
cast('100.2345' AS numeric) AS to_numr,
cast('100.2345' AS numeric(*)) AS to_numr,
cast('100.2345' AS numeric(*,0)) AS to_numr,
cast('100.2345' AS numeric(*,2)) AS to_numr,
cast('100.2345' AS numeric(4)) AS to_numr,
cast('100.2345' AS numeric(4,0)) AS to_numr,
cast('100.2345' AS numeric(9)) AS to_numr,
cast('100.2345' AS numeric(9,0)) AS to_numr,
CAST('100.2345' AS numeric(18)) AS to_numr,
CAST('100.2345' AS numeric(18,0)) AS to_numr,
CAST('100.2345' AS numeric(38)) AS to_numr,
CAST('100.2345' AS numeric(38,0)) AS to_numr,
CAST('100.2345' AS numeric(10,2)) AS to_numr
FROM dual
TO_NU |TO_NU |TO_NU|TO_NU |TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU |TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|
--------+--------+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100.2345|100.2345| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100| 100.23| 100| 100| 100| 100| 100| 100| 100| 100| 100.23|
-- 转换后oceanbase-mysql：
SELECT CAST('100.2345' AS DECIMAL) AS `to_nu`,CAST('100.2345' AS DECIMAL) AS `to_nu`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_nu`,CAST('100.2345' AS DECIMAL(38, 2)) AS `to_nu`,CAST('100.2345' AS DECIMAL(4)) AS `to_nu`,CAST('100.2345' AS DECIMAL(4)) AS `to_nu`,CAST('100.2345' AS DECIMAL(9)) AS `to_nu`,CAST('100.2345' AS DECIMAL(9)) AS `to_nu`,CAST('100.2345' AS DECIMAL(18)) AS `to_nu`,CAST('100.2345' AS DECIMAL(18)) AS `to_nu`,CAST('100.2345' AS DECIMAL(38)) AS `to_nu`,CAST('100.2345' AS DECIMAL(38)) AS `to_nu`,CAST('100.2345' AS DECIMAL(10, 2)) AS `to_nu`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38, 2)) AS `to_dec`,CAST('100.2345' AS DECIMAL(4)) AS `to_dec`,CAST('100.2345' AS DECIMAL(4)) AS `to_dec`,CAST('100.2345' AS DECIMAL(9)) AS `to_dec`,CAST('100.2345' AS DECIMAL(9)) AS `to_dec`,CAST('100.2345' AS DECIMAL(18)) AS `to_dec`,CAST('100.2345' AS DECIMAL(18)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38)) AS `to_dec`,CAST('100.2345' AS DECIMAL(10, 2)) AS `to_dec`,CAST('100.2345' AS DEC(38, 0)) AS `to_dec`,CAST('100.2345' AS DEC(38, 0)) AS `to_dec`,CAST('100.2345' AS DEC(38, 0)) AS `to_dec`,CAST('100.2345' AS DEC(38, 2)) AS `to_dec`,CAST('100.2345' AS DEC(4)) AS `to_dec`,CAST('100.2345' AS DEC(4)) AS `to_dec`,CAST('100.2345' AS DEC(9)) AS `to_dec`,CAST('100.2345' AS DEC(9)) AS `to_dec`,CAST('100.2345' AS DEC(18)) AS `to_dec`,CAST('100.2345' AS DEC(18)) AS `to_dec`,CAST('100.2345' AS DEC(38)) AS `to_dec`,CAST('100.2345' AS DEC(38)) AS `to_dec`,CAST('100.2345' AS DEC(10, 2)) AS `to_dec`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38, 0)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38, 2)) AS `to_numr`,CAST('100.2345' AS DECIMAL(4)) AS `to_numr`,CAST('100.2345' AS DECIMAL(4)) AS `to_numr`,CAST('100.2345' AS DECIMAL(9)) AS `to_numr`,CAST('100.2345' AS DECIMAL(9)) AS `to_numr`,CAST('100.2345' AS DECIMAL(18)) AS `to_numr`,CAST('100.2345' AS DECIMAL(18)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38)) AS `to_numr`,CAST('100.2345' AS DECIMAL(38)) AS `to_numr`,CAST('100.2345' AS DECIMAL(10, 2)) AS `to_numr` FROM dual
to_nu|to_nu|to_nu|to_nu |to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu |to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|
-----+-----+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100| 100.23| 100| 100| 100| 100| 100| 100| 100| 100| 100.23|
4.2.2.6.4.2. TO_DATE
- 语法
TO_DATE(char [, fmt])- 描述
- 该函数将 CHAR、VARCHAR、NCHAR 或 NVARCHAR2 数据类型的字符转换为日期数据类型的值
参数解释
参数 |
说明 |
|---|---|
char |
CHAR、VARCHAR、NCHAR 或 NVARCHAR2 数据类型的值 |
fmt |
指定 char 的时间格式 |
示例
-- 转换前Oracle SQL：
SELECT
to_date('2020-02-02', 'yyyy-mm-dd') as a1,
to_date('2020-02-02 0201', 'yyyy-mm-dd hh24mi')as a2
FROM DUAL
A1 |A2 |
-----------------------+-----------------------+
2020-02-02 00:00:00.000|2020-02-02 02:01:00.000|
-- 转换后oceanbase-mysql：
SELECT CAST(str_to_date('2020-02-02', '%Y-%m-%d') AS DATETIME) AS `a1`,CAST(str_to_date('2020-02-02 0201', '%Y-%m-%d %H%i') AS DATETIME) AS `a2` FROM DUAL;
a1 |a2 |
-----------------------+-----------------------+
2020-02-02 00:00:00.000|2020-02-02 02:01:00.000|
4.2.2.6.4.3. TO_NUMBER
- 语法
TO_NUMBER(expr)- 描述
- 该函数功能是将 CHAR、VARCHAR2等类型的字符串转换为 NUMBER 数值数据类型的值
参数解释
参数 |
说明 |
|---|---|
expr |
属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。 |
示例
-- 转换前Oracle SQL：
SELECT TO_NUMBER('0123456'),TO_NUMBER('1.2'),TO_NUMBER('0'),TO_NUMBER(1.2),TO_NUMBER('-1'),TO_NUMBER(-1) FROM DUAL;
TO_NUMBER('0123456')|TO_NUMBER('1.2')|TO_NUMBER('0')|TO_NUMBER(1.2)|TO_NUMBER('-1')|TO_NUMBER(-1)|
--------------------+----------------+--------------+--------------+---------------+-------------+
123456| 1.2| 0| 1.2| -1| -1|
-- 转换后oceanbase-mysql：
SELECT CAST('0123456' AS DECIMAL(65, 30)),CAST('1.2' AS DECIMAL(65, 30)),CAST('0' AS DECIMAL(65, 30)),CAST(1.2 AS DECIMAL(65, 30)),CAST('-1' AS DECIMAL(65, 30)),CAST(-1 AS DECIMAL(65, 30)) FROM DUAL;
CAST('0123456' AS DECIMAL(65, 30)) |CAST('1.2' AS DECIMAL(65, 30)) |CAST('0' AS DECIMAL(65, 30)) |CAST(1.2 AS DECIMAL(65, 30)) |CAST('-1' AS DECIMAL(65, 30)) |CAST(-1 AS DECIMAL(65, 30)) |
-------------------------------------+--------------------------------+--------------------------------+--------------------------------+---------------------------------+---------------------------------+
123456.000000000000000000000000000000|1.200000000000000000000000000000|0.000000000000000000000000000000|1.200000000000000000000000000000|-1.000000000000000000000000000000|-1.000000000000000000000000000000|
4.2.2.6.4.4. TO_CLOB
- 语法
TO_CLOB(lob_column | char)- 描述
- 该函数将 LOB 列或其他字符串中的 NCLOB 值转换为 CLOB 值。
参数解释
参数 |
说明 |
|---|---|
lob_column |
属于 LOB 列或其他字符串中的 NCLOB 值。 |
char |
属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB 类型的值。 |
示例
-- 转换前Oracle SQL：
SELECT TO_CLOB('1'),TO_CLOB(0),TO_CLOB(NULL), TO_CLOB('') FROM DUAL;
TO_CLOB('1')|TO_CLOB(0)|TO_CLOB(NULL)|TO_CLOB('')|
------------+----------+-------------+-----------+
1 |0 | | |
-- 转换后oceanbase-mysql：
SELECT CAST('1' AS CHAR),CAST(0 AS CHAR),CAST(NULL AS CHAR),CAST('' AS CHAR) FROM DUAL
CAST('1' AS CHAR)|CAST(0 AS CHAR)|CAST(NULL AS CHAR)|CAST('' AS CHAR)|
-----------------+---------------+------------------+----------------+
1 |0 | | |
4.2.2.6.5. 编码解码函数
4.2.2.6.5.1. DECODE
- 语法
DECODE (condition, search, result [, search, result ...][, default])- 描述
- 该函数功能是将 condition 与每个 search 依次做比较，并返回对比结果
参数解释
参数 |
说明 |
|---|---|
condition |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
search |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
result |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
default |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
示例
-- 转换前Oracle SQL：
SELECT DECODE(SIGN((5*3-2)-(3*4-1)),0,'相等',1,'(5*3-2)大','(3*4-1)大'),
DECODE(INSTR('CLARK','S'), 0, '不含有 S', '含有 S') "CLARK",
DECODE(INSTR('KING','S'), 0, '不含有 S', '含有 S') "KING",
DECODE(INSTR('MILLER','S'), 0, '不含有 S', '含有 S') "MILLER",
DECODE(INSTR('ADAMS','S'), 0, '不含有 S', '含有 S') "ADAMS",
DECODE(INSTR('FORD','S'), 0, '不含有 S', '含有 S') "FORD",
DECODE(INSTR('JONES','S'), 0, '不含有 S', '含有 S') "JONES"
FROM DUAL;
DECODE(SIGN((5*3-2)-(3*4-1)),0,'相等',1,'(5*3-2)大','(3*4-1)大')|CLARK|KING |MILLER|ADAMS|FORD |JONES|
------------------------------------------------------------+-----+-----+------+-----+-----+-----+
(5*3-2)大 |不含有 S|不含有 S|不含有 S |含有 S |不含有 S|含有 S |
-- 转换后oceanbase-mysql：
SELECT CASE WHEN SIGN((5*3-2)-(3*4-1))=0 THEN '相等' WHEN SIGN((5*3-2)-(3*4-1))=1 THEN '(5*3-2)大' ELSE '(3*4-1)大' END,CASE WHEN strpos('CLARK', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "CLARK",CASE WHEN strpos('KING', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "KING",CASE WHEN strpos('MILLER', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "MILLER",CASE WHEN strpos('ADAMS', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "ADAMS",CASE WHEN strpos('FORD', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "FORD",CASE WHEN strpos('JONES', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "JONES"
case |CLARK|KING |MILLER|ADAMS|FORD |JONES|
--------+-----+-----+------+-----+-----+-----+
(5*3-2)大|不含有 S|不含有 S|不含有 S |含有 S |不含有 S|含有 S |
4.2.2.6.6. 空值函数
4.2.2.6.6.1. NVL
- 语法
NVL(expr1, expr2)- 描述
- 该函数从两个表达式返回一个非空值。如果 expr1 与 expr2 的结果都为空值，则 NVL 函数返回 NULL
参数解释
参数 |
说明 |
|---|---|
expr1 |
指定第一个参数，数据类型可以是数据库内建数据类型中的任何数据类型。 |
expr2 |
指定第二个参数，数据类型可以是数据库内建数据类型中的任何数据类型。 |
示例
-- 转换前Oracle SQL：
SELECT NVL(10,'1'),NVL(NULL,1),NVL(0/1,1) FROM DUAL;
NVL(10,'1')|NVL(NULL,1)|NVL(0/1,1)|
-----------+-----------+----------+
10| 1| 0|
-- 转换后oceanbase-mysql：
SELECT ifnull(10, '1'),ifnull(NULL, 1),ifnull(0/1, 1) FROM DUAL;
ifnull(10, '1')|ifnull(NULL, 1)|ifnull(0/1, 1)|
---------------+---------------+--------------+
10 | 1| 0.0000|
4.2.2.6.7. 环境和标识符函数
4.2.2.6.7.1. SYS_GUID
- 语法
SYS_GUID()- 描述
- 该函数生成并返回由 16 个字节组成的全局唯一标识符，即生成一个全局唯一序列号。这个全局唯一序列号通常是一个 RAW（原始） 数据类型。如果在客户端工具或应用程序中以文本形式显示，可能会看到乱码，因为 RAW 类型的数据在文本中可能不可读。可以使用 RAWTOHEX 函数将其转换为十六进制字符串。
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT SYS_GUID() FROM DUAL;
SYS_GUID() |
----------------+
íx OH xàc) çt|
-- 转换后oceanbase-mysql：
SELECT uuid() FROM DUAL;
uuid() |
------------------------------------+
4744ece6-9f34-11ee-9bde-005056b27854|
4.2.2.6.7.2. USER
- 语法
USER- 描述
- 返回当前的用户名与主机名
- 参数解释
无
示例
-- 转换前Oracle SQL：
SELECT USER FROM DUAL;
USER |
------+
TEST|
-- 转换后oceanbase-mysql：
SELECT user() FROM DUAL
user() |
--------------------------+
test@10.188.120.241|
4.2.2.6.8. 聚合函数
4.2.2.6.8.1. AVG
- 语法
AVG([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]- 描述
- 该函数将数值类型或者可以转换成数值类型的表达式作为参数求平均值。可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
警告
统一SQL暂时不支持AVG作为分析函数的用法，不支持over子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT ALL |
计算时是否去重。为可选项，默认值 ALL。ALL： 计算所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT： 去除重复行，且忽略值为 NULL 的行。 |
expr |
指定要计算的列名。列的数据类型是数值类型或者可以转换成数值类型的表达式 |
OVER |
暂时不支持 |
示例
-- 建表语句：
CREATE TABLE unisql_employee(employee_id int,name varchar(30),salary int,department_id int,hire_date varchar(10),commission_pct number);
INSERT INTO unisql_employee values(1,'JACK',5000,1,'2023-01-01',0.05);
INSERT INTO unisql_employee values(2,'TOM',10000,1,'2023-02-01',0.15);
INSERT INTO unisql_employee values(3,'LINDA',15000,1,'2023-03-01',0.20);
INSERT INTO unisql_employee values(4,'ADA',20000,2,'2023-04-01',0.10);
INSERT INTO unisql_employee values(5,'TINA',30000,2,'2023-05-01',0.20);
INSERT INTO unisql_employee values(6,'KATE',50000,3,'2023-06-01',0.30);
-- 转换前Oracle SQL：
SELECT AVG(salary) FROM unisql_employee;
AVG(SALARY) |
----------------------------------------+
21666.6666666666666666666666666666666667|
-- 转换后oceanbase-mysql：
SELECT AVG(salary) FROM unisql_employee;
AVG(`salary`)|
-------------+
21666.6667|
4.2.2.6.8.2. COUNT
- 语法
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]- 描述
- 该函数用于返回查询 expr 的行数。可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
警告
统一SQL暂时不支持COUNT作为分析函数的用法，不支持over子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT ALL |
计算时是否去重。为可选项，默认值 ALL。ALL： 计算所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT： 去除重复行，且忽略值为 NULL 的行。 |
expr |
指定参与计算的列名 |
OVER |
暂时不支持 |
星号(*) |
表示返回满足条件的所有行，且包含重复行和空行。 |
示例
-- 转换前Oracle SQL：
SELECT count(1),count(*),count(employee_id),count(DISTINCT(department_id)) FROM unisql_employee ke;
COUNT(1)|COUNT(*)|COUNT(EMPLOYEE_ID)|COUNT(DISTINCT(DEPARTMENT_ID))|
--------+--------+------------------+------------------------------+
6| 6| 6| 3|
-- 转换后oceanbase-mysql：
SELECT count(1),count(1),count(`employee_id`),count(DISTINCT (`department_id`)) FROM `unisql_employee` AS `ke`;
count(1)|count(1)|count(`employee_id`)|count(DISTINCT (`department_id`))|
--------+--------+--------------------+---------------------------------+
6| 6| 6| 3|
4.2.2.6.8.3. LISTAGG
- 语法
LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)- 描述
- 该函数用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值
参数解释
参数 |
说明 |
|---|---|
measure_expr |
可以是任何表达式。度量列中的空值将被忽略。 |
delimiter |
指定用于分隔度量值的字符串。此子句是可选的，默认为 NULL。 |
示例
-- 转换前Oracle SQL：
SELECT LISTAGG(name, '; ') WITHIN GROUP (ORDER BY employee_id,name) AS rk FROM unisql_employee WHERE department_id=1;
RK |
----------------+
JACK; TOM; LINDA|
-- 转换后oceanbase-mysql：
SELECT group_concat(`name` ORDER BY `employee_id`,`name` SEPARATOR '; ') AS `rk` FROM `unisql_employee` WHERE `department_id`=1;
rk |
----------------+
JACK; TOM; LINDA|
4.2.2.6.8.4. MIN
- 语法
MIN({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]- 描述
- 该函数返回参数中指定列的最小值。可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
警告
统一SQL暂时不支持MIN作为分析函数的用法，不支持over子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT ALL |
计算时是否去重。为可选项，默认值 ALL。ALL： 计算所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT： 去除重复行，且忽略值为 NULL 的行。 |
expr |
可为数值、字符、日期型或其它类型的数据列或表达式 |
OVER |
暂时不支持 |
示例
-- 转换前Oracle SQL：
SELECT MIN(SALARY),MIN(1),MIN(DISTINCT department_id) FROM unisql_employee
min |min|min|
----+---+---+
5000| 1| 1|
-- 转换后oceanbase-mysql：
SELECT MIN(`SALARY`),MIN(1),MIN(DISTINCT `department_id`) FROM `unisql_employee`
MIN(`SALARY`)|MIN(1)|MIN(DISTINCT `department_id`)|
-------------+------+-----------------------------+
5000| 1| 1|
4.2.2.6.8.5. SUM
- 语法
SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]- 描述
- 该函数返回指定参数的总和，可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
警告
统一SQL暂时不支持SUM作为分析函数的用法，不支持over子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT ALL |
计算时是否去重。为可选项，默认值 ALL。ALL： 计算所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT： 去除重复行，且忽略值为 NULL 的行。 |
expr |
数值数据类型或任何可以隐式转换为数值数据类型的表达式。 |
OVER |
暂时不支持 |
示例
-- 转换前Oracle SQL：
SELECT sum(salary),sum(1),sum(DISTINCT department_id) FROM unisql_employee ke;
SUM(SALARY)|SUM(1)|SUM(DISTINCTDEPARTMENT_ID)|
-----------+------+--------------------------+
130000| 6| 6|
-- 转换后oceanbase-mysql：
SELECT sum(`salary`),sum(1),sum(DISTINCT `department_id`) FROM `unisql_employee` AS `ke`.
sum(`salary`)|sum(1)|sum(DISTINCT `department_id`)|
-------------+------+-----------------------------+
130000| 6| 6|
4.2.2.6.8.6. 注意事项
暂不支持分析函数。
暂不支持over子句。
关于参数中涉及到字面量
''
oracle对字面量''的处理等价于NULLmysql对字面量''的处理等价于0,在使用过程中，可能会出现两边结果不一致的情况在单独使用字面量''时请注意，举例说明：
-- 转换前Oracle SQL：
SELECT EXP(NULL),EXP(''),EXP(0) FROM DUAL;
---------+-------+------+
EXP(NULL)|EXP('')|EXP(0)|
---------+-------+------+
| | 1|
-- 转换后oceanbase-mysql：
SELECT EXP(NULL),EXP(''),EXP(0) FROM DUAL;
---------+-------+------+
EXP(NULL)|EXP('')|EXP(0)|
---------+-------+------+
| 1.0| 1.0|
对于EXTRACT函数
Oracle在进行时间抽取时,如果参数带有时区,则根据其对应的UTC时间进行抽取,如果是其他类型的时间字段,则根据GMT时间进行抽取。MySQL在进行时间抽取时,都是按照GMT对应时间进行抽取,即不考虑时区。
-- execute in oracle, you can see the difference between the result value of hour
SELECT CURRENT_TIMESTAMP,systimestamp,TO_TIMESTAMP_TZ('2023-09-07 16:15:40','YYYY-MM-DD HH24:MI:SS'),TIMESTAMP '2023-09-07 16:15:40' FROM dual
-----------------------------+-----------------------------+------------------------------------------------------------+-----------------------------+
CURRENT_TIMESTAMP |SYSTIMESTAMP |TO_TIMESTAMP_TZ('2023-09-0716:15:40','YYYY-MM-DDHH24:MI:SS')|TIMESTAMP'2023-09-0716:15:40'|
-----------------------------+-----------------------------+------------------------------------------------------------+-----------------------------+
2023-09-07 16:51:27.192 +0800|2023-09-07 16:51:27.192 +0800| 2023-09-07 16:15:40.000 +0800| 2023-09-07 16:15:40.000|
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS HOUR1,EXTRACT(HOUR FROM systimestamp) AS HOUR2,EXTRACT(HOUR FROM TO_TIMESTAMP_TZ('2023-09-07 16:15:40','YYYY-MM-DD HH24:MI:SS')) AS HOUR3,EXTRACT(HOUR FROM TIMESTAMP '2023-09-07 16:15:40') AS HOUR FROM DUAL
-----+-----+-----+----+
HOUR1|HOUR2|HOUR3|HOUR|
-----+-----+-----+----+
8| 8| 8| 16|
关于ROWNUM
rownum作为where条件时,目前只支持=1和<=num两种写法,参考rownum的示例。
关于自定义函数
由于Oracle中一些函数在MySQL中没有与之对应的实现,统一SQL工具为尽可能的进行支持,提供了一些自定义函数,序列等的实现,在使用时需要先在MySQL数据库中运行统一SQL提供的脚本。
常用日期格式模型列表
日期格式，说明 |
|
|---|---|
YYYY |
4位数字表示的年份（例如：2021）。 |
YYY |
3位数字表示的年份（例如：021）。 |
YY |
2位数字表示的年份（例如：21）。 |
Y |
年份的最后一位或两位数字（例如：1，21）。 |
RRRR |
四位数字表示的年份，可以自动解释为近期年份（例如：2021）。 |
Q |
一年中的季度（1-4）。 |
MM |
表示月份的两位数字（01-12）。 |
MON |
缩写形式的月份名称（例如：JAN，FEB）。 |
MONTH |
完整形式的月份名称（例如：JANUARY，FEBRUARY）。 |
DD |
表示月份中的日期的两位数字（01-31）。 |
DY |
缩写形式的星期几名称（例如：MON，TUE）。 |
DAY |
完整形式的星期几名称（例如：MONDAY，TUESDAY）。 |
HH |
小时（00-23）。 |
HH12 |
12小时制的小时（01-12）。 |
HH24 |
24小时制的小时（00-23）。 |
MI |
分钟（00-59）。 |
SS |
秒（00-59）。 |
FF |
小数秒（精度为百万分之一秒）。 |
AM |
上午/下午标识符（例如：AM，PM）。 |