3.2.2.9. 转换为 DM
3.2.2.9.1. 字符串函数
3.2.2.9.1.1. INSTR
- 语法
INSTR(str,substr)- 描述
- 返回字符串 str 中子字符串的第一个出现位置
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
substr |
子字符串 |
警告
对于该函数，在Oracle中字符串匹配区分大小写，达梦中不区分大小写，在使用时请特别注意。
示例
-- 转换前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|
-- 转换后达梦 SQL：
SELECT INSTR('Unisql', 'U') AS i1,INSTR('Unisql', 'u') AS i2,INSTR('Unisql', 'n') AS i3,INSTR('Unisql', 'a') AS i4,INSTR('Unisql', '') AS i5,INSTR('Unisql', NULL) AS i6,INSTR('foobarbar', 'bar') AS i7,INSTR('xbar', 'foobar') AS i8,INSTR('▒▒ЙЕ╗ХМГ─с', '▒▒') AS i9 FROM DUAL
i1|i2|i3|i4|i5|i6|i7|i8|i9|
--+--+--+--+--+--+--+--+--+
1| 1| 2| 0| | | 4| 0| 1|
3.2.2.9.2. 时间日期函数
3.2.2.9.2.1. ADD_MONTHS
- 语法
ADD_MONTHS(date, n)- 描述
- 该函数功能是返回日期 date 加上 n 个月后的日期值。
参数解释
参数 |
说明 |
|---|---|
date |
指定日期。该参数为 DATE 数据类型。 |
n |
整数或可以转换为一个整数的任意值。NUMBER 数据类型。 |
警告
在 Oracle 中，DATE 类型包含日期和时间信息，精确到秒。虽然它具有时间部分，但通常在显示和处理时可能会截断为整数秒。但实际上，DATE 类型存储了时分秒的信息。
当你从 DATE 类型中检索数据时，如果没有特殊设置，可能只会看到日期和部分时间信息（如 HH:MM:SS）被格式化为整数或特定的格式。
你可以使用适当的函数或转换方法来显示或提取 DATE 类型的时间信息。
在达梦中，DATE 类型只包含日期而不包含时间信息，所以在检索数据时，看到的数据格式可能会不一致。
示例
-- 转换前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|
-- 转换后达梦 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|2024-03-21|2024-01-21|2024-02-21|2024-02-21|2023-12-21|
3.2.2.9.3. 转换函数
3.2.2.9.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 NUMBER |
yes |
yes |
yes |
no |
no |
yes |
警告
使用 CAST 函数转换数据类型时，需要注意以下内容：
对于以上列表中的转换，如果目标库对转换后的目标数据类型不支持，则会在目标库中执行时报错。
对于类似cast(‘6983140795139873811265107033372908879759234567’ AS number)这种超过38位精度的数字，转换后的SQL在目标库执行会存在报错或者精度错误的情况。
示例
-- 转换前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|
-- 转换后达梦 SQL：
SELECT CAST('100.2345' AS number) AS to_nu,CAST('100.2345' AS number) AS to_nu,CAST('100.2345' AS number(38,0)) AS to_nu,CAST('100.2345' AS number(38,2)) AS to_nu,CAST('100.2345' AS number(4)) AS to_nu,CAST('100.2345' AS number(4)) AS to_nu,CAST('100.2345' AS number(9)) AS to_nu,CAST('100.2345' AS number(9)) AS to_nu,CAST('100.2345' AS number(18)) AS to_nu,CAST('100.2345' AS number(18)) AS to_nu,CAST('100.2345' AS number(38)) AS to_nu,CAST('100.2345' AS number(38)) AS to_nu,CAST('100.2345' AS number(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 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|
3.2.2.9.3.2. NUMTODSINTERVAL
- 语法
NUMTODSINTERVAL(number, 'interval_unit')- 描述
- 该函数作用是将一个数值表达式加时间间隔单位转换为一个 INTERVAL DAY TO SECOND 数据类型的值。可以用来对一个日期时间值进行加减计算。
参数解释
参数 |
说明 |
|---|---|
number |
指定间隔数量，是一个 NUMBER 值或可以隐式转换为 NUMBER 值的表达式。 |
interval_unit |
指定间隔单位，可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的值，不区分大小写。默认情况下，返回的精度为 9 |
interval_unit的取值如下：
DAY, 表示天。
HOUR, 表示小时。
MINUTE ,表示分钟。
SECOND, 表示秒。
示例
-- 转换前Oracle SQL：
SELECT NUMTODSINTERVAL(10,'day'),
NUMTODSINTERVAL(10,'HOUR'),
NUMTODSINTERVAL(10,'MINUTE'),
NUMTODSINTERVAL(10,'SECOND')
FROM DUAL;
NUMTODSINTERVAL(10,'DAY')|NUMTODSINTERVAL(10,'HOUR')|NUMTODSINTERVAL(10,'MINUTE')|NUMTODSINTERVAL(10,'SECOND')|
-------------------------+--------------------------+----------------------------+----------------------------+
10 0:0:0.0 |0 10:0:0.0 |0 0:10:0.0 |0 0:0:10.0 |
-- 转换后达梦 SQL：
SELECT NUMTODSINTERVAL(10, 'day'),NUMTODSINTERVAL(10, 'HOUR'),NUMTODSINTERVAL(10, 'MINUTE'),NUMTODSINTERVAL(10, 'SECOND') FROM DUAL
NUMTODSINTERVAL(10,'day') |NUMTODSINTERVAL(10,'HOUR') |NUMTODSINTERVAL(10,'MINUTE') |NUMTODSINTERVAL(10,'SECOND') |
--------------------------------------------------------+--------------------------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
INTERVAL '000000010 00:00:00.000000' DAY(9) TO SECOND(6)|INTERVAL '000000000 10:00:00.000000' DAY(9) TO SECOND(6)|INTERVAL '000000000 00:10:00.000000' DAY(9) TO SECOND(6)|INTERVAL '000000000 00:00:10.000000' DAY(9) TO SECOND(6)|
3.2.2.9.3.3. TO_CLOB
- 语法
TO_CLOB(lob_column | char)- 描述
- 该函数将 LOB 列或其他字符串中的 NCLOB 值转换为 CLOB 值。
参数解释
参数 |
说明 |
|---|---|
lob_column |
属于 LOB 列或其他字符串中的 NCLOB 值。 |
char |
属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB 类型的值。 |
示例
CREATE TABLE unisql_test_clob (c1 clob,c2 varchar2(10));
INSERT INTO unisql_test_clob VALUES (TO_CLOB('1'),'orale');
-- 转换前Oracle SQL：
SELECT TO_CLOB(c1),TO_CLOB(c2),TO_CLOB(10) FROM unisql_test_clob;
TO_CLOB(C1)|TO_CLOB(C2)|TO_CLOB(10)|
-----------+-----------+-----------+
1 |orale |10 |
-- 转换后oceanbsse-oracle-Oracle SQL：
SELECT CAST(c1 AS text),CAST(c2 AS text),CAST(10 AS text) FROM unisql_test_clob
c1|c2 |text|
--+-----+----+
1 |orale|10 |
3.2.2.9.3.4. TO_NCHAR
- 语法
TO_NCHAR(x)- 描述
- 该函数将 CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据转换为国家字符集，返回 NVARCHAR2 数据类型。
参数解释
参数 |
说明 |
|---|---|
character |
CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据 |
示例
-- 转换前Oracle SQL:
select to_nchar('abc') from dual;
select to_nchar('1234567') from dual;
select to_nchar(col1) from to_nchar_test_1;
select to_nchar(1) from dual;
select to_nchar(to_timestamp('2023-10-31 16:38:10','YYYY-MM-DD HH24:MI:SS')) from dual;
-- 转换后达梦 SQL:
SELECT CAST('abc' AS NVARCHAR2) FROM dual
SELECT CAST('1234567' AS NVARCHAR2) FROM dual
SELECT CAST(col1 AS NVARCHAR2) FROM to_nchar_test_1
SELECT CAST(1 AS NVARCHAR2) FROM dual
SELECT CAST(to_timestamp('2023-10-31 16:38:10', 'YYYY-MM-DD HH24:MI:SS') AS NVARCHAR2) FROM dual
3.2.2.9.3.5. TO_NCHAR (number)
- 语法
TO_NCHAR(n)- 描述
- 该函数将 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 类型的数值转换为国家字符集中的字符串。
参数解释
参数 |
说明 |
|---|---|
n |
属于 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。 |
示例
-- 转换前Oracle SQL：
SELECT TO_NCHAR(123456789.123) FROM DUAL;
TO_NCHAR(123456789.123)|
-----------------------+
123456789.123 |
-- 转换后oceanbsse-oracle-Oracle SQL：
SELECT CAST(123456789.123 AS text) FROM DUAL
text |
-------------+
123456789.123|
3.2.2.9.3.6. RAWTOHEX
该函数将一个字节数组转换为十六进制字符串。
-- 转换前Oracle SQL：
SELECT RAWTOHEX(SYS_GUID()) FROM DUAL;
RAWTOHEX(SYS_GUID()) |
--------------------------------+
0D003A97D52E7EB7E0632989140A93CC|
-- 转换后oceanbsse-oracle-Oracle SQL：
SELECT replace(SYS_GUID(), '-', '') FROM DUAL
replace |
--------------------------------+
874cb2d1c2f24023a5f16d1525043d6b|
3.2.2.9.3.7. JSON_OBJECT
- 描述
- 对于 SQL 输入的一系列 key-value 对，JSON_OBJECT() 函数将其转换成一个 JSON 格式的对象，包含了前面输入的所有 key-value 对。
语法
JSON_OBJECT([KEY]'key_name': value_expr,
[KEY]'key_name' VALUE value_expr,
column_name_x);
示例
-- 转换前Oracle SQL:
SELECT JSON_OBJECT(
'first_name' : a.first_name,
'first_namet' : 'first_name',
KEY 'full_name' VALUE a.first_name || ' ' || b.last_name,
'last_name' VALUE a.last_name,
KEY 'full_name_x' VALUE 'full_name_x',
'last_name_x' VALUE 'last_name_x',
b.employee_id,
a.EMPLOYEE_ID,
'salary' : 99999999
) FROM employee_json a,employee_json b;
-- 转换后达梦 SQL:
-- 返回的 object 对象自动对 key/value 中的 key 进行去重排序，去重时仅保留输入的最后一对 key/value
-- value为空字符串，oracle处理为null，DM处理为空字符串
SELECT json_object(
'first_name', a.first_name,
'first_namet', 'first_name',
'full_name', a.first_name||' '||b.last_name,
'last_name', a.last_name,
'full_name_x', 'full_name_x',
'last_name_x', 'last_name_x',
'employee_id', b.employee_id,
'EMPLOYEE_ID', a.EMPLOYEE_ID,
'salary', 99999999)
FROM employee_json a , employee_json b
3.2.2.9.4. 环境和标识符函数
3.2.2.9.4.1. SYS_GUID
- 语法
SYS_GUID()- 描述
- 该函数生成并返回由 16 个字节组成的全局唯一标识符，即生成一个全局唯一序列号
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT SYS_GUID(),RAWTOHEX(SYS_GUID()) FROM DUAL;
SYS_GUID() |RAWTOHEX(SYS_GUID()) |
----------------+--------------------------------+
yv Û¹úàe |0D7976950CDCB9FAE065000000000001|
-- 转换后达梦 SQL：
SELECT SYS_GUID(),RAWTOHEX(SYS_GUID()) FROM DUAL;
SYS_GUID() |RAWTOHEX(SYS_GUID()) |
----------------+--------------------------------+
\ái,Å"² Û²ëpq ?|71E1692CC522B2115E8B7CA6C586F50B|
3.2.2.9.4.2. USERENV
- 描述
- 该函数返回有关当前会话的信息。该函数不能在 CHECK 约束的条件下使用。
语法
USERENV('parameter')
参数解释
参数 |
说明 |
达梦是否支持 |
|---|---|---|
CLIENT_INFO |
返回用户会话信息（最多 64 字节），应用程序可以使用 DBMS_APPLICATION_INFO 系统包存储这些信息 |
不支持 |
INSTANCE |
当前实例的标识号 |
不支持 |
LANG |
语言的缩写名称，是比 LANGUAGE 参数更短的格式 |
支持 |
LANGUAGE |
当前会话使用的语言和区域，以及数据库字符集。格式为 language_territory.characterset |
支持 |
SCHEMAID |
Schema ID |
支持 |
SESSIONID |
审计会话标识符 |
不支持 |
SID |
会话 ID |
支持 |
ENTRYID |
每个会话分配的一个唯一标识符 |
不支持 |
示例
-- 转换前Oracle SQL:
SELECT USERENV('LANGUAGE') "Language" FROM DUAL;
-- 转换后达梦 SQL:
SELECT sys_context('USERENV', 'LANGUAGE') AS "Language" FROM DUAL;
3.2.2.9.5. 聚合函数
3.2.2.9.5.1. LISTAGG
- 语法
LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)- 描述
- 该函数用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值
参数解释
参数 |
说明 |
|---|---|
measure_expr |
可以是任何表达式。度量列中的空值将被忽略。 |
delimiter |
指定用于分隔度量值的字符串。此子句是可选的，默认为 NULL。 |
警告
下面示例中的ON OVERFLOW TRUNCATE/ON OVERFLOW ERROR语法词在达梦中不支持。统一SQL支持转换，但是在数据库执行是会报错，请在使用时注意。
示例
-- 转换前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|
-- 转换后达梦 SQL:
SELECT LISTAGG(name, '; ') WITHIN GROUP (ORDER BY employee_id,name) AS rk FROM unisql_employee WHERE department_id=1
rk |
----------------+
JACK; TOM; LINDA|
-- ORA-00900: SQL 错误 [42000]: 第 1 行, 第 24 列[ON]附近出现错误:
-- 使用语法词ON OVERFLOW TRUNCATE/ON OVERFLOW ERROR
CREATE TABLE listagg_test_employees (
emp_id int,
emp_name VARCHAR(100),
department_id int
);
-- 插入数据
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (1, 'John Doe', 1);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (2, 'Jane Smith', 1);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (3, 'Mark Johnson', 2);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (4, 'Emily Davis', 2);
SELECT LISTAGG(emp_name ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name,', ' ON OVERFLOW TRUNCATE) over(partition by department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name ON OVERFLOW ERROR) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW ERROR) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name,', ' ON OVERFLOW ERROR) over(partition by department_id) rk FROM listagg_test_employees;