11.2. 内置函数
本节主要介绍统一SQL支持原生Oracle数据库中的函数
11.2.1. 数字函数
11.2.2. 字符串函数
11.2.2.1. CHR
- 语法
CHR(n)- 描述
- 该函数根据数字参数返回字符，返回值与当前系统的字符集相关
参数解释
参数 |
说明 |
|---|---|
n |
字符的数字代码,是一个整数值，范围为 [0，4294967295] |
示例
-- 转换前Oracle SQL：
select CHR(67),CHR(68) from dual;
CHR(67)|CHR(68)|
-------+-------+
C |D |
-- 转换后TDSQL-Oracle模式 SQL：
SELECT CHR(67),CHR(68)
chr|chr|
---+---+
C |D |
11.2.2.2. 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 |
只在一个词的不是开头或末尾的点上匹配 |
不支持 |
11.2.3. 时间日期函数
11.2.3.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|
11.2.4. 通用比较函数
11.2.5. 转换函数
11.2.5.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|
11.2.6. 空值函数
11.2.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|
-- 转换后TDSQL-Oracle模式 SQL：
SELECT coalesce(10, '1'),coalesce(NULL, 1),coalesce(0/1, 1)
coalesce|coalesce|coalesce|
--------+--------+--------+
10| 1| 0|
11.2.7. 环境和标识符函数
11.2.7.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|
11.2.8. 层次函数
11.2.8.1. SYS_CONNECT_BY_PATH
- 语法
SYS_CONNECT_BY_PATH(column,'char')- 描述
- 该函数返回从根到节点的列值的路径，由 CONNECT BY 条件返回的每一行的列值用指定分隔符号分隔。该函数仅在层次查询中有效
参数解释
参数 |
说明 |
|---|---|
column |
指定返回数据的列名。可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型。 |
char |
指定分隔符号。可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型。 |
示例
-- 建表语句：
CREATE TABLE unisql_sys_connect_by_path_test(z_id int,name varchar(20),n_id int);
INSERT INTO unisql_sys_connect_by_path_test VALUES(0,'ABC',null);
INSERT INTO unisql_sys_connect_by_path_test VALUES(1,'An',0);
INSERT INTO unisql_sys_connect_by_path_test VALUES(2,'Bn',0);
INSERT INTO unisql_sys_connect_by_path_test VALUES(3,'Cn',0);
INSERT INTO unisql_sys_connect_by_path_test VALUES(4,'A1',1);
INSERT INTO unisql_sys_connect_by_path_test VALUES(5,'B1',2);
INSERT INTO unisql_sys_connect_by_path_test VALUES(6,'C1',3);
INSERT INTO unisql_sys_connect_by_path_test VALUES(7,'C2',6);
INSERT INTO unisql_sys_connect_by_path_test VALUES(8,'A2',4);
INSERT INTO unisql_sys_connect_by_path_test VALUES(9,'B2',5);
INSERT INTO unisql_sys_connect_by_path_test VALUES(10,'A3',8);
INSERT INTO unisql_sys_connect_by_path_test VALUES(11,'A4',10);
INSERT INTO unisql_sys_connect_by_path_test VALUES(12,'B3',9);
-- 转换前Oracle SQL：
SELECT z_id,name,n_id,SYS_CONNECT_BY_PATH(name, '/') "Path"
FROM unisql_sys_connect_by_path_test
WHERE n_id in(0,1,2)
START WITH n_id IS NOT NULL
CONNECT BY PRIOR n_id = z_id
ORDER BY z_id;
Z_ID|NAME|N_ID|Path |
----+----+----+---------------+
1|An | 0|/A4/A3/A2/A1/An|
1|An | 0|/A2/A1/An |
1|An | 0|/A3/A2/A1/An |
1|An | 0|/A1/An |
1|An | 0|/An |
2|Bn | 0|/B3/B2/B1/Bn |
2|Bn | 0|/B1/Bn |
2|Bn | 0|/B2/B1/Bn |
2|Bn | 0|/Bn |
3|Cn | 0|/C2/C1/Cn |
3|Cn | 0|/C1/Cn |
3|Cn | 0|/Cn |
4|A1 | 1|/A3/A2/A1 |
4|A1 | 1|/A1 |
4|A1 | 1|/A2/A1 |
4|A1 | 1|/A4/A3/A2/A1 |
5|B1 | 2|/B1 |
5|B1 | 2|/B3/B2/B1 |
5|B1 | 2|/B2/B1 |
-- 转换后TDSQL-Oracle模式 SQL：
WITH RECURSIVE tmp AS (SELECT z_id AS "$z_id",name AS "$name",n_id AS "$n_id",concat('/', name) AS "Path",NULL AS "prior$n_id" FROM unisql_sys_connect_by_path_test WHERE n_id IS NOT NULL UNION ALL SELECT unisql_sys_connect_by_path_test.z_id AS "$z_id",unisql_sys_connect_by_path_test.name AS "$name",unisql_sys_connect_by_path_test.n_id AS "$n_id",concat(concat("Path", '/'), unisql_sys_connect_by_path_test.name) AS "Path",CAST("$n_id" AS text) AS "prior$n_id" FROM unisql_sys_connect_by_path_test , tmp WHERE tmp."$n_id"=unisql_sys_connect_by_path_test.z_id) SELECT tmp."$z_id" AS z_id,tmp."$name" AS name,tmp."$n_id" AS n_id,tmp."Path" AS "Path" FROM tmp WHERE tmp."$n_id" IN (0,1,2) ORDER BY tmp."$z_id"
z_id|name|n_id|Path |
----+----+----+---------------+
1|An | 0|/An |
1|An | 0|/A1/An |
1|An | 0|/A2/A1/An |
1|An | 0|/A3/A2/A1/An |
1|An | 0|/A4/A3/A2/A1/An|
2|Bn | 0|/B1/Bn |
2|Bn | 0|/Bn |
2|Bn | 0|/B3/B2/B1/Bn |
2|Bn | 0|/B2/B1/Bn |
3|Cn | 0|/C1/Cn |
3|Cn | 0|/Cn |
3|Cn | 0|/C2/C1/Cn |
4|A1 | 1|/A4/A3/A2/A1 |
4|A1 | 1|/A2/A1 |
4|A1 | 1|/A3/A2/A1 |
4|A1 | 1|/A1 |
5|B1 | 2|/B2/B1 |
5|B1 | 2|/B1 |
5|B1 | 2|/B3/B2/B1 |
11.2.9. 聚合函数
11.2.9.1. AVG
- 语法
AVG([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]- 描述
- 该函数将数值类型或者可以转换成数值类型的表达式作为参数求平均值。可以将其用作聚合或分析函数。
说明
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
参数解释
参数 |
说明 |
|---|---|
DISTINCT 或 ALL |
查询时是否去重。为可选项，默认值 ALL。ALL：查询时不去除数据中的重复值，且忽略数据中的 NULL 值。DISTINCT：查询时去除数据中的重复值，且忽略数据中的 NULL 值。 |
expr |
指定要计算的列名。列的数据类型是数值类型或者可以转换成数值类型的表达式。数值类型可以为 NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE。 |
OVER |
使用 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|
-- 转换后TDSQL-Oracle模式 SQL：
SELECT AVG(salary) FROM unisql_employee
avg |
------------------+
21666.666666666667|
11.2.9.2. 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|
11.2.10. 分析函数
11.2.10.1. AVG
- 语法
AVG([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]- 描述
- 该函数将数值类型或者可以转换成数值类型的表达式作为参数求平均值。可以将其用作聚合或分析函数。
说明
作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
参数解释
参数 |
说明 |
|---|---|
DISTINCT 或 ALL |
查询时是否去重。为可选项，默认值 ALL。ALL：查询时不去除数据中的重复值，且忽略数据中的 NULL 值。DISTINCT：查询时去除数据中的重复值，且忽略数据中的 NULL 值。 |
expr |
指定要计算的列名。列的数据类型是数值类型或者可以转换成数值类型的表达式。数值类型可以为 NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE。 |
OVER |
使用 OVER 子句定义窗口进行计算 |
示例
-- 转换前Oracle SQL：
SELECT employee_id,salary,department_id,AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS avgRes FROM unisql_employee ke GROUP BY department_id,salary ,employee_id;
EMPLOYEE_ID|SALARY|DEPARTMENT_ID|AVGRES|
-----------+------+-------------+------+
1| 5000| 1| 5000|
2| 10000| 1| 7500|
3| 15000| 1| 10000|
4| 20000| 2| 20000|
5| 30000| 2| 25000|
6| 50000| 3| 50000|
-- 转换后TDSQL-Oracle模式 SQL：
SELECT employee_id,salary,department_id,AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS avgRes FROM unisql_employee AS ke GROUP BY department_id,salary,employee_id
employee_id|salary|department_id|avgres |
-----------+------+-------------+----------------------+
1| 5000| 1| 5000.0000000000000000|
2| 10000| 1| 7500.0000000000000000|
3| 15000| 1|10000.0000000000000000|
4| 20000| 2| 20000.000000000000|
5| 30000| 2| 25000.000000000000|
6| 50000| 3| 50000.000000000000|
11.2.11. 序列伪列
11.2.11.1. seq_name.nextval
该函数用于获取序列号的下一个squence的值
-- 创建sequence
create sequence uni_seq increment by 1 start with 1
-- 获取下一个sequence的值
-- 转换前Oracle SQL：
SELECT uni_seq.nextval FROM unisql_employee ke;
NEXTVAL|
-------+
1|
2|
3|
4|
5|
6|
-- 转换后TDSQL-Oracle模式 SQL：
SELECT nextval('uni_seq') FROM unisql_employee AS ke.
nextval|
-------+
1|
2|
3|
4|
5|
6|
11.2.12. 其他
11.2.12.1. REGEXP_LIKE condition
- 语法
REGEXP_LIKE(source_char,pattern)- 描述
- 根据正则表达式进行匹配搜索
参数解释
参数 |
说明 |
|---|---|
source_char |
是一个字符表达式，可作为搜索值 |
pattern |
正则表达式 |
示例
-- 建表语句
CREATE TABLE unisql_regexp_like_test(id int,name varchar(50),hiredate varchar(50),salary int);
INSERT INTO unisql_regexp_like_test VALUES(100, 'RAPHAELY', '2017-07-01', 1700);
INSERT INTO unisql_regexp_like_test VALUES(100, 'DE HAAN', '2018-05-01',11000);
INSERT INTO unisql_regexp_like_test VALUES(100, 'ERRAZURIZ', '2017-07-21', 1400);
INSERT INTO unisql_regexp_like_test VALUES(100, 'HARTSTEIN', '2019-05-01',14000);
INSERT INTO unisql_regexp_like_test VALUES(100, 'RAPHAELY', '2017-07-22', 1700);
INSERT INTO unisql_regexp_like_test VALUES(100, 'WEISS', '2019-07-11',13500);
INSERT INTO unisql_regexp_like_test VALUES(100, 'RUSSELL', '2019-10-05', 13000);
INSERT INTO unisql_regexp_like_test VALUES(100, 'PARTNERS', '2018-12-01',14000);
INSERT INTO unisql_regexp_like_test VALUES(200, 'ROSS', '2019-06-11',13500);
INSERT INTO unisql_regexp_like_test VALUES(200, 'BELL', '2019-05-25', 13000);
INSERT INTO unisql_regexp_like_test VALUES(200, 'PART', '2018-08-11',14000);
-- 转换前Oracle SQL：
SELECT * FROM unisql_regexp_like_test WHERE regexp_like(name, '^R')
ID |NAME |HIREDATE |SALARY|
---+--------+----------+------+
100|RAPHAELY|2017-07-01| 1700|
100|RAPHAELY|2017-07-22| 1700|
100|RUSSELL |2019-10-05| 13000|
200|ROSS |2019-06-11| 13500|
-- 转换后TDSQL-Oracle模式 SQL：
SELECT * FROM unisql_regexp_like_test WHERE unisql.regexp_like(name, '^R')
id |name |hiredate |salary|
---+--------+----------+------+
100|RAPHAELY|2017-07-01| 1700|
100|RAPHAELY|2017-07-22| 1700|
100|RUSSELL |2019-10-05| 13000|
200|ROSS |2019-06-11| 13500|