3.2.2.5. 转换为 Tdsql-Oracle
3.2.2.5.1. 字符串函数
3.2.2.5.1.1. REGEXP_INSTR
- 语法
REGEXP_INSTR(source_char, pattern)- 描述
- 该函数作用是返回正则表达式匹配值在源字符串中的位置。
参数 |
说明 |
|---|---|
source_char |
指定用作搜索值的字符表达式，数据类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB |
pattern |
指定正则表达式截取规则。它通常是一个文本文字，字符类型可为 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 |
注意
由于TDSQL-PostgreSQL(Oracle模式)中不存在regexp_instr函数,所以在使用统一SQL转换涉及到regexp_instr函数的sql时,需要先导入创建自定义函数的脚本(参考快速开始-》导入目标库脚本一节)
1. 当前仅支持两个参数的转换
2. 当第一个参数是null/''时,Oracle返回null,转换后的sql在tdsql-pg(oracle模式)中返回0,使用时请注意。
3. 正则匹配默认大小写敏感,多行被视为一行匹配,支持的正则规则参考后续列表
4. 由于Oracle和tdsql-pg(Oracle)关于正则的实现存在差异,会出现部分正则写法不支持的情况,例如:
在Oracle中:
select regexp_instr('IL3nbfhzGl','*') FROM dual;
REGEXP_INSTR('IL3NBFHZGL','*')|
------------------------------+
1|
转换之后在TDSQL-Oracle模式数据库执行:
SELECT unisql.regexp_instr(CAST('IL3nbfhzGl' AS text), CAST('*' AS text)) FROM dual
SQL 错误 [2201B]: ERROR: invalid regular expression: quantifier operand invalid
在Oracle中:
select regexp_instr('text^', '^(?!\^)') from dual;
REGEXP_INSTR('TEXT^','^(?!\^)')|
-------------------------------+
0|
转换之后在TDSQL-Oracle模式数据库执行:
SELECT unisql.regexp_instr(CAST('text^' AS text), CAST('^(?!\^)' AS text)) FROM dual
SQL 错误 [XX000]: ERROR: function 0x9d2570 returned NULL
示例
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('abc', '[abc]') FROM DUAL;
REGEXP_INSTR('ABC','[ABC]')|
---------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('abc' AS text), CAST('[abc]' AS text)) FROM DUAL
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('foo', '(foo)') FROM DUAL;
REGEXP_INSTR('FOO','(FOO)')|
---------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('foo' AS text), CAST('(foo)' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('aaa', 'a{3}') FROM DUAL;
REGEXP_INSTR('AAA','A{3}')|
--------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('aaa' AS text), CAST('a{3}' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('bb', 'b{2,}') FROM DUAL;
REGEXP_INSTR('BB','B{2,}')|
--------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('bb' AS text), CAST('b{2,}' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('cc', 'c{2,4}') FROM DUAL;
REGEXP_INSTR('CC','C{2,4}')|
---------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('cc' AS text), CAST('c{2,4}' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('hello', '[[:alpha:]]') FROM DUAL;
REGEXP_INSTR('HELLO','[[:ALPHA:]]')|
-----------------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('hello' AS text), CAST('[[:alpha:]]' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('a', '[=a=]') FROM DUAL;
REGEXP_INSTR('A','[=A=]')|
-------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('a' AS text), CAST('[=a=]' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('abc123', '\d') FROM DUAL;
REGEXP_INSTR('ABC123','\D')|
---------------------------+
4|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('abc123' AS text), CAST('\d' AS text)) FROM DUAL;
regexp_instr|
------------+
4|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('word123!', '\W') FROM DUAL;
REGEXP_INSTR('WORD123!','\W')|
-----------------------------+
8|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('word123!' AS text), CAST('\W' AS text)) FROM DUAL;
regexp_instr|
------------+
8|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('start', '\Astart') FROM DUAL;
REGEXP_INSTR('START','\ASTART')|
-------------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('start' AS text), CAST('\Astart' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('abc123', 'a.*?c') FROM DUAL;
REGEXP_INSTR('ABC123','A.*?C')|
------------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('abc123' AS text), CAST('a.*?c' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('abc123', 'a.+?c') FROM DUAL;
REGEXP_INSTR('ABC123','A.+?C')|
------------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('abc123' AS text), CAST('a.+?c' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('abc', 'a.??c') FROM DUAL;
REGEXP_INSTR('ABC','A.??C')|
---------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('abc' AS text), CAST('a.??c' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('aaa', 'a{2}?') FROM DUAL;
REGEXP_INSTR('AAA','A{2}?')|
---------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('aaa' AS text), CAST('a{2}?' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('aaa', 'a{2,}?') FROM DUAL;
REGEXP_INSTR('AAA','A{2,}?')|
----------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('aaa' AS text), CAST('a{2,}?' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR('aaa', 'a{2,3}?') FROM DUAL;
REGEXP_INSTR('AAA','A{2,3}?')|
-----------------------------+
1|
-- 转换后TDSQL-Oracle模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST('aaa' AS text), CAST('a{2,3}?' AS text)) FROM DUAL;
regexp_instr|
------------+
1|
正则规则
规则 |
描述 |
备注 |
|---|---|---|
\ |
反斜杠(1.可代表自己;2.引用下一个字符;3.引入一个操作符;4.什么都不做) |
单独使用目标库执行会报错 |
* |
匹配零或更多 |
单独使用目标库执行会报错 |
+ |
匹配一次或多次 |
单独使用目标库执行会报错 |
? |
匹配零或一次 |
单独使用目标库执行会报错 |
| |
选择匹配,比如:aa`|`bb（匹配 aa 或 bb ） |
单独使用目标库执行会报错 |
^ |
默认情况下匹配字符串的开头。在多行模式下，它匹配源字符串中任何地方的任何行的开头。 |
单独使用目标库执行会报错 |
$ |
默认情况下匹配字符串的末端。在多行模式下，它匹配源字符串中任何地方的任何线路的末端。 |
单独使用目标库执行会报错 |
. |
匹配受支持的字符集中的任何字符,null除外 |
|
[ ] |
匹配列表中表示的任何表达式, 比如: [abc](匹配 a、b 或 c 中的一个字母),[a-z](匹配 a 到 z 中的一个字母)。如果使用^则表示匹配除列表外的表达式[^abc](匹配除了 a、b 或 c 中的其他字母) |
|
() |
分组表达 |
|
{m} |
完全匹配M次 |
|
{m,} |
匹配至少M次 |
|
{m,n} |
匹配至少M次，但不超过n次 |
|
\n |
反向表示表达式（n是1和9之间的数字）与n之前的“（’and”）’之间的第N个子表达相匹配 |
|
[..] |
指定一个collation元素 |
|
[::] |
指定字符类（例如，[：alpha：]）。它匹配字符类中的任何字符 |
|
[==] |
指定等价类。例如，[= a =]匹配所有具有基本字母’a’的字符。 |
|
\d |
一个数字字符 |
|
\D |
一个非数字字符 |
|
\w |
一个单词字符 |
|
\W |
一个非单词字符 |
|
\s |
一个空格字符 |
|
\S |
一个非空格字符 |
|
\A |
仅在字符串的开头或字符串末端的newline字符之前进行匹配 |
|
\Z |
仅在字符串结束时匹配 |
|
*? |
匹配前面的模式元素0或更多次(nongreedy) |
单独使用目标库执行会报错 |
+? |
匹配前面的模式元素元素1或更多次(nongreedy) |
单独使用目标库执行会报错 |
?? |
匹配前面的模式元素0或1次(nongreedy) |
单独使用目标库执行会报错 |
{n}? |
完全匹配前面的模式元素n次(nongreedy) |
|
{n,}? |
与前面的模式元素匹配至少n次(nongreedy) |
|
{n,m}? |
匹配前面的模式元素，至少n，但不超过M次(nongreedy) |
|
()\n |
分组和后向引用 |
不支持 |
(?:expr) |
忽略捕获的子模式 |
不支持 |
(?=expr) |
正向先行断言（Positive Lookahead),它断言从当前位置开始，后面必须匹配表达式 |
不支持 |
(?!expr) |
负向先行断言（Negative Lookahead）,断言从当前位置开始，后面不能匹配表达式 |
不支持 |
\m |
只在一个词的开头匹配 |
不支持 |
\M |
只在一个词的末尾匹配 |
不支持 |
\y |
只在一个词的开头或末尾匹配 |
不支持 |
\Y |
只在一个词的不是开头或末尾的点上匹配 |
不支持 |
3.2.2.5.2. 时间日期函数
3.2.2.5.2.1. ADD_MONTHS
- 语法
ADD_MONTHS(date, n)- 描述
- 该函数功能是返回日期 date 加上 n 个月后的日期值。
参数解释
参数 |
说明 |
|---|---|
date |
指定日期。该参数为 DATE 数据类型。 |
n |
整数或可以转换为一个整数的任意值。NUMBER 数据类型。 |
示例
-- 转换前Oracle SQL：
SELECT
ADD_MONTHS(CURRENT_DATE,-3) AS a1,
ADD_MONTHS(CURRENT_DATE, 3)AS a2,
ADD_MONTHS(CURRENT_DATE, 1.1)AS a3,
ADD_MONTHS(CURRENT_DATE, 1.5)AS a4,
ADD_MONTHS(CURRENT_DATE, 1.8)AS a5,
ADD_MONTHS(CURRENT_DATE, 0)AS a6
FROM DUAL;
A1 |A2 |A3 |A4 |A5 |A6 |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-22 15:25:08.000|2024-03-22 15:25:08.000|2024-01-22 15:25:08.000|2024-01-22 15:25:08.000|2024-01-22 15:25:08.000|2023-12-22 15:25:08.000|
-- 转换后TDSQL-Oracle模式 SQL：
SELECT localtimestamp(0)+make_interval(0, CAST(trunc(-3) AS int), 0, 0, 0, 0, 0) AS a1,localtimestamp(0)+make_interval(0, CAST(trunc(3) AS int), 0, 0, 0, 0, 0) AS a2,localtimestamp(0)+make_interval(0, CAST(trunc(1.1) AS int), 0, 0, 0, 0, 0) AS a3,localtimestamp(0)+make_interval(0, CAST(trunc(1.5) AS int), 0, 0, 0, 0, 0) AS a4,localtimestamp(0)+make_interval(0, CAST(trunc(1.8) AS int), 0, 0, 0, 0, 0) AS a5,localtimestamp(0)+make_interval(0, CAST(trunc(0) AS int), 0, 0, 0, 0, 0) AS a6
a1 |a2 |a3 |a4 |a5 |a6 |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-22 15:26:16.000|2024-03-22 15:26:16.000|2024-01-22 15:26:16.000|2024-01-22 15:26:16.000|2024-01-22 15:26:16.000|2023-12-22 15:26:16.000|
3.2.2.5.3. 转换函数
3.2.2.5.3.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 BINARY_FLOAT/ BINARY_DOUBLE |
yes |
yes |
yes |
no |
no |
yes |
to CHAR/ VARCHAR2 |
yes |
yes |
yes |
yes |
yes |
no |
to NUMBER |
yes |
yes |
yes |
no |
no |
yes |
to DATETIME/INTERVAL |
no |
yes |
no |
yes |
no |
no |
to RAW |
yes |
yes |
yes |
no |
yes |
no |
to NCHAR/NVARCHAR2 |
yes |
no |
yes |
yes |
yes |
yes |
警告
使用 CAST 函数转换数据类型时，需要注意以下内容：
对于以上列表中的转换，如果目标库对转换后的目标数据类型不支持，则会在目标库中执行时报错。
对于类似cast(‘6983140795139873811265107033372908879759234567’ AS number)这种超过38位精度的数字，转换后的SQL在目标库执行会存在报错或者精度错误的情况。
示例
-- 转换前Oracle SQL：
select
cast(123 as BINARY_FLOAT),
cast(123 as BINARY_DOUBLE),
cast(1 as CHAR),
cast(123 as VARCHAR2(10)),
cast('123' as NUMBER),
cast('2023-12-20' as DATE),
cast('2023-12-20 10:07:55.222' as TIMESTAMP),
cast('2-6' AS INTERVAL YEAR TO MONTH),
cast('1 10:22:22' AS INTERVAL DAY TO SECOND),
cast('313233' AS RAW(10)),
cast(123 as NCHAR(3)),
cast(123 as NVARCHAR2(3))
from dual;
CAST(123ASBINARY_FLOAT)|CAST(123ASBINARY_DOUBLE)|CAST(1ASCHAR)|CAST(123ASVARCHAR2(10))|CAST('123'ASNUMBER)|CAST('2023-12-20'ASDATE)|CAST('2023-12-2010:07:55.222'ASTIMESTAMP)|CAST('2-6'ASINTERVALYEARTOMONTH)|CAST('110:22:22'ASINTERVALDAYTOSECOND)|CAST('313233'ASRAW(10))|CAST(123ASNCHAR(3))|CAST(123ASNVARCHAR2(3))|
-----------------------+------------------------+-------------+-----------------------+-------------------+------------------------+-----------------------------------------+--------------------------------+--------------------------------------+-----------------------+-------------------+-----------------------+
123.0| 123.0|1 |123 | 123| 2023-12-20 00:00:00.000| 2023-12-20 10:07:55.222|2-6 |1 10:22:22.0 |123 |123 |123 |
-- 转换后TDSQL-Oracle模式 SQL：
float4|float8|bpchar|varchar|numeric|timestamp |timestamp |interval |interval |bytea |bpchar|varchar|
------+------+------+-------+-------+-----------------------+-----------------------+---------------------------------------------+------------------------------------------------+------+------+-------+
123.0| 123.0|1 |123 | 123|2023-12-20 00:00:00.000|2023-12-20 10:07:55.222|2 years 6 mons 0 days 0 hours 0 mins 0.0 secs|0 years 0 mons 1 days 10 hours 22 mins 22.0 secs|313233|123 |123 |
-- 转换前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|
-- 转换后TDSQL-Oracle模式 SQL：
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 numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,2)) AS to_numr,CAST('100.2345' AS numeric(4)) AS to_numr,CAST('100.2345' AS numeric(4)) AS to_numr,CAST('100.2345' AS numeric(9)) AS to_numr,CAST('100.2345' AS numeric(9)) AS to_numr,CAST('100.2345' AS numeric(18)) AS to_numr,CAST('100.2345' AS numeric(18)) AS to_numr,CAST('100.2345' AS numeric(38)) AS to_numr,CAST('100.2345' AS numeric(38)) AS to_numr,CAST('100.2345' AS numeric(10,2)) AS to_numr
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|
3.2.2.5.4. 空值函数
3.2.2.5.4.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|
-- 转换后TDSQL-Oracle模式 SQL：
SELECT coalesce(10, '1'),coalesce(NULL, 1),coalesce(0/1, 1)
coalesce|coalesce|coalesce|
--------+--------+--------+
10| 1| 0|
3.2.2.5.5. 环境和标识符函数
3.2.2.5.5.1. SYS_GUID
- 语法
SYS_GUID()- 描述
- 该函数生成并返回由 16 个字节组成的全局唯一标识符，即生成一个全局唯一序列号
参数解释
无
示例
警告
结果格式或表现存在不一致的情况。如果对数据完全一致要求较高，建议不使用该特性或对结果进一步处理。
-- 转换前Oracle SQL：
SELECT SYS_GUID() FROM DUAL;
SYS_GUID() |
----------------+
íx OH xàc) çt|
-- 转换后TDSQL-Oracle模式 SQL：
SELECT gen_random_uuid()
gen_random_uuid |
------------------------------------+
6b846107-4402-46da-832f-c9738a6e029e|
3.2.2.5.6. 聚合函数
3.2.2.5.6.1. MEDIAN
- 语法
MEDIAN(expr)- 描述
- 该函数用于返回一组数值的中值，即将一组数值排序后返回居于中间的数值。如果参数集合中包含偶数个数值，该函数将返回位于中间的两个数的平均值。
参数解释
参数 |
说明 |
|---|---|
expr |
指定要求中值的表达式，参数类型属于数值数据类型或可以隐式转换为数字数据类型。 |
示例
-- 转换前Oracle SQL：
SELECT department_id, MEDIAN(salary) FROM unisql_employee ke GROUP BY department_id;
DEPARTMENT_ID|MEDIAN(SALARY)|
-------------+--------------+
1| 10000|
2| 25000|
3| 50000|
-- 转换后TDSQL-Oracle模式 SQL
SELECT department_id,percentile_cont(5e-01) WITHIN GROUP (ORDER BY salary) FROM unisql_employee AS ke GROUP BY department_id
department_id|percentile_cont|
-------------+---------------+
1| 10000.0|
2| 25000.0|
3| 50000.0|