支持以下数学函数：
BIN_TO_NUM
BITAND
BITOR
BITXOR
COSH
MOD
REMAINDER
ROUND_TIES_TO_EVEN
SINH
TANH
描述
BIN_TO_NUM 将位向量转换为其等效数字。
语法
BIN_TO_NUM(VARIADIC integer []) returns int
通用规则
此函数的每个参数表示位向量中的一位。
此函数以任何整数数据类型或任何可隐式转换为整数的非整数数据类型作为参数。
BIN_TO_NUM 在数据仓库应用程序中很有用，用于使用分组集从物化视图中选择感兴趣的组。
注意
如果使用 BIN_TO_NUM 进行转换，则需要预先指定 "oracle" 作为search_path。
参见
有关如何编辑 search_path 的信息，请参阅 "使用 orafce 的注意事项"。
有关如何设置服务器参数的信息，请参阅LightDB文档中的 "服务器管理" > "服务器配置" > "设置参数"。
示例
SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL;
bin_to_num
------------
10
(1 row)
描述
执行按位 AND 操作。
语法
BITAND(int1 integers, int2 integers) returns bigint
通用规则
BITAND 对两个整数的每一位执行 AND 操作，并返回结果。
指定整数类型的值。
返回值的数据类型为 BIGINT。
示例
在以下示例中，返回数字字面值 5 和 3 的 AND 操作结果。
SELECT BITAND(5,3) FROM DUAL;
bitand
-------
1
(1 row)
描述
执行按位 OR 操作。
语法
BITOR(int1 bigint, int2 bigint) returns bigint
通用规则
BITOR 对两个 BIGINT 的每一位执行 OR 操作，并返回结果。
指定 BIGINT 类型的值。
返回值的数据类型为 BIGINT。
注意
如果使用 BITOR，则需要预先指定 "oracle" 作为 search_path。
参见
有关如何编辑 search_path 的信息，请参阅 "使用 orafce 的注意事项"。
有关如何设置服务器参数的信息，请参阅LightDB文档中的 "服务器管理" > "服务器配置" > "设置参数"。
示例
在以下示例中，返回数字字面值 5 和 3 的 OR 操作结果。
SELECT BITOR(5,3) FROM DUAL;
bitor
-------
7
(1 row)
描述
执行按位异或操作。
语法
BITXOR(int1 bigint, int2 bigint) returns bigint
通用规则
BITXOR 对两个整数的每一位执行 XOR 操作，并返回结果。
指定 BIGINT 类型的值。
返回值的数据类型为 BIGINT。
注意
如果使用 BITXOR，则需要预先指定 "oracle" 作为 search_path。
参见
有关如何编辑 search_path 的信息，请参阅 "使用 orafce 的注意事项"。
有关如何设置服务器参数的信息，请参阅LightDB文档中的 "服务器管理" > "服务器配置" > "设置参数"。
示例
在以下示例中，返回数字字面值 5 和 3 的 XOR 操作结果。
SELECT BITXOR(5,3) FROM DUAL;
bitxor
--------
6
(1 row
描述
计算一个数字的双曲余弦。
语法
COSH(num numeric) returns double precision
通用规则
COSH 返回指定数字的双曲余弦值。
数字必须是数值数据类型。
返回值的数据类型是 DOUBLE PRECISION。
示例
在以下示例中，返回数字字面值 2.236 的双曲余弦值。
SELECT COSH(2.236) FROM DUAL;
cosh
-----------------
4.7313591000247
(1 row)
描述
MOD返回n2除以n1的余数。如果n1为0，则返回n2。
语法
MOD(n2 bigint, n1 bigint) returns bigint
MOD(n2 integer, n1 integer) returns integer
MOD(n2 numeric, n1 numeric) returns numeric
MOD(n2 smallint, n1 smallint) returns smallint
General rules
该函数接受数字作为参数
注意
mod 函数不支持一个参数是字符串形式的小数，另一个参数是整数的情况。
示例
SELECT mod(5,2) FROM DUAL;
mod
-----------
1
描述
返回n2除以n1的余数。
语法
REMAINDER(n2 numeric, n1 numeric) returns numeric
通用规则
此函数将任何数字数据类型或可隐式转换为数字数据类型的任何非数字数据类型作为参数。
MOD函数类似于REMAINDER函数，但其公式中使用FLOOR函数，而REMAINDER使用ROUND函数。
如果n1！= 0，则余数为n2 -（n1 * N），其中N是最接近n2 / n1的整数。 如果n2 / n1等于x.5，则N是最接近的偶数整数。
注意
如果使用REMAINDER函数，则需要事先在search_path中指定“oracle”。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
请参阅LightDB文档中“服务器管理”>“服务器配置”>“设置参数”以获取有关如何设置服务器参数的信息。
示例
SELECT REMAINDER(5.5,2) FROM DUAL;
remainder
-----------
-0.5
(1 row)
描述
该函数将n四舍五入为整数位。
语法
ROUND_TIES_TO_EVEN(n NUMERIC,integer int4 DEFAULT 0) returns numeric
通用规则
如果整数为正，则n将向小数点右侧的整数位四舍五入。
如果未指定整数，则n将四舍五入为0位。
如果整数为负，则n将向小数点左侧的整数位四舍五入。
注意
如果使用ROUND_TIES_TO_EVEN函数，则需要事先在search_path中指定“oracle”。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
请参阅LightDB文档中“服务器管理”>“服务器配置”>“设置参数”以获取有关如何设置服务器参数的信息。
示例
以下示例将一个数字向右舍入到小数点后一位：
SELECT ROUND_TIES_TO_EVEN(45.177,1) "ROUND_EVEN" FROM DUAL;
ROUND_EVEN
------------
45.2
(1 row)
以下示例将一个数字向左舍入到小数点前一位：
SELECT ROUND_TIES_TO_EVEN(45.177,-1) "ROUND_EVEN" FROM DUAL;
ROUND_EVEN
------------
50
(1 row)
描述
计算一个数字的双曲正弦。
语法
SINH(num numeric) returns double precision
通用规则
SINH函数返回指定数字的双曲正弦。
数字必须是数字数据类型。
返回值的数据类型是DOUBLE PRECISION。
示例
在以下示例中，返回数字1.414的双曲正弦。
SELECT SINH(1.414) FROM DUAL;
sinh
-----------------
1.93460168824956
(1 row)
描述
计算一个数字的双曲正切。
语法
TANH(num numeric) returns double precision
通用规则
TANH函数返回指定数字的双曲正切。
数字必须是数字数据类型。
返回值的数据类型是DOUBLE PRECISION。
示例
在以下示例中，返回数字3的双曲正切。
SELECT TANH(3) FROM DUAL;
tanh
-----------------
0.995054753686731
(1 row)
描述
计算大于等于一个数的最小整数，当输入参数是interval类型时，会先获取其天数并将其转成数值后再计算。
语法
CEIL(i interval) returns number
CEIL(n numeric) returns numeric
CEIL(d double precision) returns numeric
通用规则
CEIL函数计算大于等于一个数的最小整数。
参数可以是interval类型、numeric类型或double precision类型。
返回值的数据类型是numeric或double precision类型。
示例
在以下示例中，返回大于等于一个数的最小整数，当输入参数是interval类型时，先获取其天数，这是个数值，然后再返回大于等于这个数值的最小整数。
select ceil(sysdate - '2023-01-01'::date) FROM dual ;
ceil
------
335
(1 row)
select ceil(sysdate - to_date('2023-11-27','yyyy-mm-dd')) FROM dual;
ceil
------
5
(1 row)
select ceil(sysdate - to_date('2023-01-01','yyyy-mm-dd')) FROM dual;
ceil
------
335
(1 row)
支持以下字符串函数：
BTRIM
INSTR
INSTRB
LENGTH
LENGTHB
LPAD
LTRIM
NLSSORT
REGEXP_COUNT
REGEXP_INSTR
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_REPLACE
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
SUBSTRB
CONCAT
UPPER
描述
从字符串的开头和结尾删除指定的字符。
语法
BTRIM(str text) returns text
BTRIM(str text, trimChars text) returns text
BTRIM(str clob) returns clob
BTRIM(str clob, trimChars clob) returns clob
通用规则
BTRIM 从字符串 str 的开头和结尾删除 trimChars，并返回结果字符串。
如果指定了多个修剪字符，则删除与修剪字符匹配的所有字符。如果省略了 trimChars，则删除所有前导和尾随半角空格。
返回值的数据类型为文本类型如果第一个参数是 text 类型，返回值类型为 CLOB 如果第一个参数是 clob 类型。
注意
Oracle 数据库不支持 BTRIM 函数。
BTRIM 函数的 CHAR 类型规范使用 orafce 来定义，其行为与 LightDB 的 BTRIM 函数不同。必须修改 search_path 参数才能使其与上述规范描述的行为相同。
信息
LightDB 的 BTRIM 通用规则如下：
如果字符串是 CHAR 类型，则先删除尾随空格，然后删除修剪字符。
参见
有关如何编辑 search_path 的信息，请参阅“使用 orafce 的注意事项”。
请参阅 LightDB 文档中的“SQL 语言” > “函数和操作符” > “字符串函数和操作符”中的 BTRIM 了解更多信息。
示例
在以下示例中，返回从字符串 "aabcaba" 的开头和结尾删除 "a" 后的字符串。
SELECT BTRIM('aabcaba','a') FROM DUAL;
btrim
-------
bcab
(1 row)
描述
返回字符串中子字符串的位置。
语法
INSTR(str1 text, str2 text) returns integer
INSTR(str1 text, str2 text, startPos integer) returns integer
INSTR(str1 text, str2 text, startPos integer, occurrences integer) returns integer
通用规则
INSTR 函数在字符串 str1 中搜索子字符串 str2，并返回第一次出现的字符位置（以字符计数）。
搜索从字符串 str1 的指定起始位置 startPos 开始。
当 startPos 为 0 或负数时，起始位置将从 str1 结尾左侧的指定字符数开始，INSTR 函数将从该点向后搜索。
如果没有指定起始位置，则从 str1 的开头开始搜索。
如果指定了 occurrences，则返回 str2 的第 n 次出现在 str1 中的位置。只能指定正数。
如果未指定 occurrences，则返回找到的第一个出现位置的起始位置。
如果在 str1 中未找到 str2，则返回 0。
对于 startPos 和 occurrences，请指定 SMALLINT 或 INTEGER 类型。
返回值的数据类型为 INTEGER。
示例
在以下示例中，字符串 "ABCACBCAAC" 中找到了字符 "BC"，并返回了这些字符的位置。
SELECT INSTR('ABCACBCAAC','BC') FROM DUAL;
instr
-------
2
(1 row)
SELECT INSTR('ABCACBCAAC','BC',-1,2) FROM DUAL;
instr
-------
2
(1 row)
描述
INSTRB 函数使用字节搜索字符串中的子字符串，并返回第一个指定出现的子字符串字节所在字符串的位置。
语法
INSTRB( p_source text, p_search text, p_position int4 default 1, p_occurrence int4 default 1) RETURNS integer
通用规则
INSTRB 函数在字符串 p_source 中搜索子字符串 p_search，并返回第一次出现的子字符串字节所在字符串的位置（以字节计数）。
搜索从字符串 p_source 的指定起始位置 p_position 开始。
当 p_position 为 0 或负数时，将引发错误。这与 Oracle 不同，在 Oracle 中，p_position 将从 p_source 结尾左侧的指定字节数开始，INSTRB 函数将从该点向后搜索。
如果未指定起始位置，则从 p_source 的开头开始搜索。
如果指定了 p_occurrence，则返回 p_search 的第 n 次出现在 p_source 中的位置。只能指定正数。
如果未指定 occurrences，则返回找到的第一个出现位置的起始位置。
如果在 p_source 中未找到 p_search，则返回 0。
返回值的数据类型为 INTEGER。
注意
如果使用 INSTRB 函数，则需要事先指定 search_path 为 "oracle"。
请参见
有关如何编辑 search_path 的信息，请参阅 "Notes on Using orafce"。
有关如何设置服务器参数的信息，请参阅 LightDB 文档中的 "Server Administration" > "Server Configuration" > "Setting Parameters" 部分。
示例
在下面的例子中，找到字符串“hhh测试onhx”中的字符“on”，并返回那些字节的位置。
SELECT INSTRB('hhh测试onhx', 'on') FROM DUAL;
instrb
--------
10
(1 row)
SELECT INSTRB('hhh测试onhx on xxxx', 'on', 7, 2) FROM DUAL;
instrb
--------
15
(1 row)
描述
返回字符串中字符的数量。
语法
LENGTH(str text) returns integer
通用规则
LENGTH 返回字符串 str 中字符的数量。
如果字符串是 CHAR 类型，尾随空格也会计入长度。
返回值的数据类型是 INTEGER。
注意
上面的 LENGTH 规范使用 orafce 来确定其行为，这与 LightDB 的 LENGTH 不同。必须修改 search_path 参数，以使其按照 orafce 规范运行。
信息
LightDB 的 LENGTH 的通用规则如下：
如果字符串是 CHAR 类型，尾随空格不计入长度。
参见
有关如何编辑 search_path 的信息，请参阅“使用 orafce 的说明”。
有关 LENGTH 的信息，请参阅 LightDB 文档中的 “SQL 语言” > “函数和运算符” > “字符串函数和运算符”。
示例
在以下示例中，返回表 t1 中列 col2（使用 CHAR(10) 定义）中的字符数。
SELECT col2,LENGTH(col2) FROM t1 WHERE col1 = '1001';
col2 | length
------------+--------
AAAAA | 10
(1 row)
描述
返回字符串的字节数。
语法
LENGTHB(str text) returns integer
通用规则
LENGTHB 返回字符串 str 中的字节数。
如果字符串是 CHAR 类型，尾随空格也会计入长度。
返回值的数据类型是 INTEGER。
示例
在以下示例中，返回表 t1 中列 col2（使用 CHAR(10) 定义）中的字节数。请注意，在第二个 SELECT 语句中，"*" 中的每个字符占用 3 个字节，总共占用 9 个字节，并且为 7 个尾随空格添加了 7 个字节。这将得出结果为 16 个字节。
SELECT col2,LENGTHB(col2) FROM t1 WHERE col1 = '1001';
col2 | lengthb
---------------+---------
AAAAA | 10
(1 row)
SELECT col2,LENGTHB(col2) FROM t1 WHERE col1 = '1004';
col2 | lengthb
---------------+---------
*** | 16
(1 row)
描述
使用一系列的字符填充一个字符串，使其达到指定的长度。
语法
LPAD(x bigint, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
LPAD(x integer, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
LPAD(x smallint, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
LPAD(x numeric, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
LPAD(str text, len integer) returns text
LPAD(str text, len integer, paddingStr text | char | varchar2 | nvarchar2) returns text
LPAD(str clob, len integer) returns clob
LPAD(str clob, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns clob
LPAD(str char, len integer) returns varchar2
LPAD(str char, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
LPAD(str varchar, len integer) returns varchar2
LPAD(str varchar, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
LPAD(str varchar2, len integer) returns varchar2
LPAD(str varchar2, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
LPAD(str nvarchar2, len integer) returns nvarchar2
LPAD(str nvarchar2, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns nvarchar2
通用规则
LPAD 函数将在字符串 str 的开始处反复填充填充字符 paddingStr，直到字符串达到长度 len，然后返回结果。
如果字符串是 CHAR 类型，则填充字符将添加到字符串中，而不会删除尾随空格。
在结果字符串中，全角字符被认为长度为2，半角字符被认为长度为1。如果一个全角字符不能被包含在结果字符串中，因为只有一个半角字符的空间可用，那么该字符串将用单字节空格填充。
如果第一个参数类型是 text 类型，返回值的数据类型是 text。如果第一个参数类型是 clob 类型，返回值的数据类型是 clob。如果第一个参数类型是 nvarchar2 类型，返回值的数据类型是 nvarchar2。 如果第一个参数类型是 bigint，character，character varying，integer，numeric，smallint，varchar2 类型，返回值的数据类型是 varchar2。
注意
上面的 LPAD 规范使用 orafce 实现，其行为与 LightDB 的 LPAD 不同。必须修改 search_path 参数才能使其按照 orafce 规范工作。
信息
LightDB 的 LPAD 的通用规则如下：
如果字符串是 CHAR 类型，则删除尾随空格，然后将填充字符添加到字符串中。
结果长度是字符数。
参见
有关如何编辑 search_path 的信息，请参阅《使用 orafce 的注意事项》。
有关 LPAD 的信息，请参阅 LightDB 文档中的 "SQL 语言" > "函数和操作符" > "字符串函数和操作符"。
示例
在下面的示例中，返回一个通过使用 "a" 填充字符串 "abc" 的左侧而形成的 10 个字符的字符串。
SELECT LPAD('abc',10,'a') FROM DUAL;
lpad
------------
aaaaaaaabc
(1 row)
描述
从字符串的开头删除指定的字符。
语法
LTRIM(str text) returns text
LTRIM(str text, trimChars text) returns text
LTRIM(str clob) returns clob
LTRIM(str clob, trimChars clob) returns clob
通用规则
LTRIM 函数返回一个从字符串 str 的开头删除了 trimChars 的字符串。
如果指定了多个修剪字符，则删除与修剪字符匹配的所有字符。如果省略了 trimChars，则删除所有前导半角空格。
返回值的数据类型是 TEXT 如果第一个参数类型为 `text` 类型，CLOB 如果第一个参数类型为 `clob` 类型。
注意
上面的 LTRIM 规范使用 orafce 实现，其行为与 LightDB 的 LTRIM 不同。必须修改 search_path 参数才能使其按照 orafce 规范工作。
信息
LightDB的LTRIM的通用规则如下：
如果字符串是CHAR类型，则会先去除尾随空格，然后移除修剪字符。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关LTRIM的信息，请参阅LightDB文档中“SQL语言”>“函数和操作符”>“字符串函数和操作符”。
示例
在以下示例中，将返回从“aabcab”的开头中移除“ab”的字符串。
SELECT LTRIM('aabcab','ab') FROM DUAL;
ltrim
-------
cab
(1 row)
描述
返回表示区域设置（COLLATE）的词法顺序的字节字符串。
语法
NLSSORT(str text) returns bytea
NLSSORT(str text, locale text) returns bytea
NLSSORT(str text, 'NLS_SORT=SCHINESE_PINYIN_M') returns bytea
通用规则
NLSSORT用于在区域设置（COLLATE）的排序顺序中进行比较和排序，该区域设置与默认区域设置不同。
可以为区域设置指定的值根据数据库服务器的操作系统而异。
如果省略了区域设置，则需要使用set_nls_sort预先设置区域设置。要使用set_nls_sort设置区域设置，请执行SELECT语句。
返回值的数据类型是BYTEA。
使用SELECT语句设置set_nls_sort的示例
SELECT set_nls_sort('en_US.UTF8');
注意
如果指定区域编码，请确保它与数据库编码匹配。
参见
有关可以指定的语言环境信息，请参阅LightDB文档中的“服务器管理”>“本地化”>“语言环境支持”部分。
示例
Table 9.100. 表(t3)的组成
|
列1 |
列2 |
|---|---|
|
1001 |
aabcababc |
|
2001 |
abcdef |
|
3001 |
aacbaab |
在以下示例中，返回按“da_DK.UTF8”排序表t3的列col2的结果。
SELECT col1,col2 FROM t3 ORDER BY NLSSORT(col2,'da_DK.UTF8');
col1 | col2
------+------------
2001 | abcdef
1001 | aabcababc
3001 | aacbaab
(3 row)
描述
在字符串中搜索正则表达式，并返回匹配项的计数。
语法
REGEXP_COUNT(string text, pattern text) returns integer
REGEXP_COUNT(string text, pattern text, startPos integer) returns integer
REGEXP_COUNT(string text, pattern text, startPos integer, flags text) returns integer
通用规则
REGEXP_COUNT函数返回pattern在源string中出现的次数。 它返回一个整数，表示pattern出现的次数。 如果没有找到匹配项，则该函数返回0。 如果参数中有一个为空，则返回 NULL，但 'flags' 参数可以为空。
搜索从string中指定的起始位置startPos开始，默认从string的开头开始。
startPos是正整数，不允许使用负值从string的末尾开始搜索。
flags是一个字符表达式，它允许您改变函数的默认匹配行为。
flags的值可以包括以下一个或多个字符：
'i': 不区分大小写匹配。
'c': 区分大小写和重音敏感匹配。
'n': 句点（.）匹配换行符。默认情况下，句点不匹配换行符。
'm': 将源字符串视为多行。
'x': 忽略空格字符。默认情况下，空格字符与它们自身匹配。
如果省略了flags，那么：
默认情况下区分大小写和重音。
句点（.）不匹配换行符。
源字符串被视为单行。
注意
如果使用REGEXP_COUNT函数，则需要事先在search_path中指定"oracle"。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
示例
SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d') FROM DUAL;
regexp_count
--------------
0
(1 row)
SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'm') FROM DUAL;
regexp_count
--------------
0
(1 row)
SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'n') FROM DUAL;
regexp_count
--------------
1
(1 row)
SELECT REGEXP_COUNT('a'||CHR(10)||'d', '^d$', 1, 'm') FROM DUAL;
regexp_count
--------------
1
(1 row)
描述
返回在字符串中找到模式匹配的开始或结束位置。
语法
REGEXP_INSTR(string text, pattern text) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer, flags text) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer, flags text) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer, flags text, group integer) returns integer
通用规则
REGEXP_INSTR函数返回一个整数，表示匹配子字符串的开始或结束位置，具体取决于return_opt参数的值。如果没有找到匹配项，则该函数返回0。
搜索从string中指定的起始位置startPos开始，默认从string的开头开始。
startPos是正整数，不允许使用负值从string的末尾开始搜索。
occurrence是一个正整数，表示应该搜索string中pattern的第几个出现次数。默认值为1，表示在string中搜索第一个出现的pattern。
return_opt允许您指定与出现次数相关的返回内容：
当return_opt = 0时，返回第一次出现的第一个字符的位置。这是默认值。
当return_opt = 1时，返回出现后面一个字符的位置。
flags是一个字符表达式，它允许您改变函数的默认匹配行为。有关详细信息，请参阅REGEXP_COUNT。
对于带有捕获组的pattern，group是一个正整数，表示应该返回pattern中的哪个捕获组。捕获组可以嵌套，它们按其在pattern中出现左括号的顺序进行编号。 如果group为零，则返回与模式匹配的整个子字符串的位置。如果group大于pattern中的捕获组数量，则函数返回零。 空的group值返回NULL。 group的默认值为零。
注意
如果使用REGEXP_INSTR函数，则需要事先在search_path中指定"oracle"。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
示例
SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))') FROM DUAL;
regexp_instr
--------------
1
(1 row)
SELECT REGEXP_INSTR('1234567890', '(4(56)(78))', 3) FROM DUAL;
regexp_instr
--------------
4
(1 row)
SELECT REGEXP_INSTR('123 123456 1234567, 1234567 1234567 12', '[^ ]+', 1, 6) FROM DUAL;
regexp_instr
--------------
37
(1 row)
SELECT REGEXP_INSTR('199 Oretax Prayers, Riffles Stream, CA', '[S|R|P][[:alpha:]]{6}', 3, 2, 1) FROM DUAL;
regexp_instr
--------------
28
(1 row)
描述
在查询的WHERE子句中使用，使得查询返回与给定模式匹配的行。
语法
REGEXP_LIKE(string text, pattern text) returns boolean
REGEXP_LIKE(string text, pattern text, flags text) returns boolean
通用规则
REGEXP_LIKE类似于LIKE条件，但它执行正则表达式匹配，而不是LIKE执行的简单模式匹配。
返回一个布尔值，当模式匹配字符串时为true，否则为false。 如果参数中有一个为空，则返回 NULL，但 'flags' 参数可以为空。
flags是一个字符表达式，它允许您更改函数的默认匹配行为。有关详细信息，请参见REGEXP_COUNT。
注意
如果使用REGEXP_LIKE函数，需要预先指定“oracle”作为search_path。
参见
有关如何编辑search_path的信息，请参见“使用orafce的注意事项”。
示例
SELECT REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'm') FROM DUAL;
regexp_like
-------------
f
(1 row)
SELECT REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'n') FROM DUAL;
regexp_like
-------------
t
(1 row)
描述
返回与调用函数中指定的模式匹配的字符串。
语法
REGEXP_SUBSTR(string text, pattern text) returns text
REGEXP_SUBSTR(string text, pattern text, startPos integer) returns text
REGEXP_SUBSTR(string text, pattern text, startPos integer, occurrence numeric) returns text
REGEXP_SUBSTR(string text, pattern text, startPos integer, occurrence numeric, flags text) returns text
REGEXP_SUBSTR(string text, pattern text, startPos integer, occurrence numeric, flags text, group int) returns text
REGEXP_SUBSTR(string CLOB, pattern text) returns clob
通用规则
REGEXP_SUBSTR返回与将POSIX正则表达式模式与字符串匹配所得到的匹配子字符串。如果未找到匹配，则该函数返回NULL。 如果参数中有一个为空，则返回 NULL，但 'flags' 参数可以为空。 函数将字符串作为 TEXT 或 CLOB 数据返回，返回的字符集与第一个参数相同。
搜索从字符串中指定的起始位置startPos开始，默认从字符串的开头开始。
startPos是一个正整数，不允许使用负值从字符串的末尾开始搜索。
occurrence是一个正整数，表示在字符串中搜索第几个模式。 默认值为1，表示在字符串中搜索第一个模式。如果输入是一个浮点数，会向下取整。
flags是一个字符表达式，它允许您更改函数的默认匹配行为。有关详细信息，请参见REGEXP_COUNT。
对于具有捕获组的模式，*group*是一个正整数，表示应由函数返回模式中的哪个捕获组。捕获组可以嵌套，它们按照它们在模式中左括号出现的顺序进行编号。 如果group为零，则返回与模式匹配的整个子字符串的位置。 如果group值超过模式中捕获组的数量，则函数返回NULL。 空的group值返回NULL。 group的默认值为零。
注意
如果使用REGEXP_SUBSTR函数，需要预先指定“oracle”作为search_path。
参见
有关如何编辑search_path的信息，请参见“使用orafce的注意事项”。
示例
SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+') FROM DUAL;
regexp_substr
----------------
, zipcode town
(1 row)
SELECT regexp_substr('number of your street, zipcode town, FR', ',[^,]+', 24) FROM DUAL;
regexp_substr
---------------
, FR
(1 row)
SELECT regexp_substr('number of your street, zipcode town, FR', ',[^,]+', 1, 2) FROM DUAL;
regexp_substr
---------------
, FR
(1 row)
SELECT regexp_substr('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0) FROM DUAL;
regexp_substr
---------------
12345678
(1 row)
SELECT regexp_substr('1234567890 1234567890', '(123)(4(56)(78))', 1, 1.9, 'i', 0) FROM DUAL;
regexp_substr
---------------
12345678
(1 row)
描述
返回与调用函数中指定的模式匹配的字符串。
语法
REGEXP_REPLACE(string text, pattern text, replace_string text, startPos integer,
occurrence integer, flags text) returns text
REGEXP_REPLACE(string clob, pattern text, replace_string text) returns clob
通用规则
REGEXP_REPLACE 返回修改后的源字符串，其中源字符串中出现的 POSIX 正则表达式模式会被替换为指定的替换字符串。 如果未找到匹配项或查询的出现次数超过了匹配项的数量，则返回未修改的源字符串。 函数返回值为 TEXT 类型如果第一个参数不是 CLOB 类型，CLOB 类型如果第一个参数是 CLOB 类型。
搜索和替换从指定的起始位置 startPos 开始，在 string 中进行，默认从 string 的开头开始。
startPos 是一个正整数，不允许使用负值从 string 的末尾开始搜索。
occurrence 是一个正整数，表示应搜索和替换 string 中 pattern 的哪个出现次数。默认值为 0，表示替换 string 中所有的 pattern。
flags 是一个字符表达式，可让您更改函数的默认匹配行为。有关详细信息，请参见 REGEXP_COUNT。
注意
如果使用 REGEXP_REPLACE 函数，则需要提前指定 "oracle" 作为 search_path。
参见
有关如何编辑 search_path 的信息，请参阅 "使用 orafce 的注释"。
示例
SELECT regexp_replace('512.123.4567 612.123.4567', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') FROM DUAL;
regexp_replace
-------------------------------
(512) 123-4567 (612) 123-4567
(1 row)
SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9);
regexp_replace
----------------------------------------
number your street, zipcode town, FR
(1 row)
SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9, 2);
regexp_replace
---------------------------------------------
number your street, zipcode town, FR
(1 row)
描述
返回将每个 search_string 的出现替换为 replacement_string 的字符串。
语法
REPLACE(string text, pattern text) returns text
REPLACE(string text, pattern text, replace_string text) returns text
REPLACE(string uuid, pattern text, replace_string text) returns text
REPLACE(string CLOB, pattern text) returns CLOB
REPLACE(string CLOB, pattern text, replace_string text) returns CLOB
通用规则
如果省略或为 null，则将删除 search_string 的所有出现。如果 search_string 为 null，则返回 string。
函数返回值为 TEXT 类型如果第一个参数不是 CLOB 类型，CLOB 类型如果第一个参数是 CLOB 类型。
注意
上述 REPLACE 规范使用 orafce 来实现其行为，这与 LightDB 的 REPLACE 不同。必须修改 search_path 参数，以使其按照 orafce 规范进行操作。
信息
LightDB 的 REPLACE 的通用规则如下：
如果 replace_string 或 search_string 为 null，则返回 null。
参见
有关如何编辑 search_path 的信息，请参阅 "使用 orafce 的注释"。
示例
SELECT REPLACE('abcdAbcdasd', 'a') FROM DUAL;
replace
-----------
bcdAbcdsd
(1 row)
SELECT REPLACE('abcdAbcdasd', 'a','c') FROM DUAL;
replace
-------------
cbcdAbcdcsd
(1 row)
SELECT REPLACE('abcdAbcdasd', NULL,'c') FROM DUAL;
replace
-------------
abcdAbcdasd
(1 row)
SELECT REPLACE('abcdAbcdasd', 'a',NULL) FROM DUAL;
replace
-----------
bcdAbcdsd
(1 row)
描述
使用一系列字符将字符串右侧填充到指定的长度。
语法
RPAD(x bigint, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
RPAD(x integer, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
RPAD(x smallint, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
RPAD(x numeric, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
RPAD(str text, len integer) returns text
RPAD(str text, len integer, paddingStr text | char | varchar2 | nvarchar2) returns text
RPAD(str clob, len integer) returns clob
RPAD(str clob, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns clob
RPAD(str char, len integer) returns varchar2
RPAD(str char, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
RPAD(str varchar, len integer) returns varchar2
RPAD(str varchar, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
RPAD(str varchar2, len integer) returns varchar2
RPAD(str varchar2, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
RPAD(str nvarchar2, len integer) returns nvarchar2
RPAD(str nvarchar2, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns nvarchar2
通用规则
RPAD 返回在使用填充字符 paddingStr 反复填充字符串 str 的末尾直到字符串达到长度 len 后的结果。
如果字符串是 CHAR 类型，则添加填充字符时不会删除尾随空格。
在结果字符串中，全角字符被认为长度为 2，半角字符长度为 1。如果由于只有一个半角字符的空间可用而无法包含全角字符，则使用单字节空格填充字符串。
如果第一个参数类型是 text 类型，返回值的数据类型是 text。如果第一个参数类型是 clob 类型，返回值的数据类型是 clob。如果第一个参数类型是 nvarchar2 类型，返回值的数据类型是 nvarchar2。 如果第一个参数类型是 bigint，character，character varying，integer，numeric，smallint，varchar2 类型，返回值的数据类型是 varchar2。
注意
上述 RPAD 规范使用 orafce 来实现其行为，这与 LightDB 的 RPAD 不同。必须修改 search_path 参数，以使其按照 orafce 规范进行操作。
信息
LightDB 的 RPAD 的通用规则如下：
如果字符串是 CHAR 类型，则删除尾随空格，然后将填充字符添加到字符串中。
结果长度是字符数。
参见
有关如何编辑 search_path 的信息，请参阅 "使用 orafce 的注释"。
有关 RPAD 的信息，请参阅 LightDB 文档中的 "SQL 语言" > "函数和运算符" > "字符串函数和运算符"。
示例
在以下示例中，返回通过使用 "a" 右侧填充字符串 "abc" 形成的长度为 10 个字符的字符串。
SELECT RPAD('abc',10,'a') FROM DUAL;
rpad
------------
abcaaaaaaa
(1 row)
描述
从字符串末尾删除指定的字符。
语法
RTRIM(str text) returns text
RTRIM(str text, trimChars text) returns text
RTRIM(str clob) returns clob
RTRIM(str clob, trimChars text) returns clob
通用规则
RTRIM 返回从字符串 str 末尾删除 trimChars 后的字符串。
如果指定了多个修剪字符，则删除与修剪字符匹配的所有字符。如果省略了 trimChars，则删除所有尾随半角空格。
返回的字符串是 TEXT 类型如果第一个参数是字符类型，CLOB 类型如果第一个参数是 CLOB 类型。
注意
上述 RTRIM 规范使用 orafce 来实现其行为，这与 LightDB 的 RTRIM 不同。必须修改 search_path 参数，以使其与 orafce 规范的行为相同。
信息
LightDB 的 RTRIM 的通用规则如下：
如果字符串是 CHAR 类型，则删除尾随空格，然后删除修剪字符。
请参见
参阅“使用orafce的注意事项”了解如何编辑search_path。
请参阅LightDB文档中的“SQL语言” > “函数和操作符” > “字符串函数和操作符”，了解有关RTRIM的信息。
示例
在下面的示例中，将从“aabcab”的结尾中删除“ab”字符串并返回。
SELECT RTRIM('aabcab','ab') FROM DUAL;
rtrim
-------
aabc
(1 row)
描述
SOUNDEX返回一个包含char的语音表示的字符字符串。 此函数可让您比较不同拼写但在英语中发音相似的单词。
语法
SOUNDEX(str text) returns text
通用规则
语音表示在Donald E. Knuth的《计算机程序设计艺术》第3卷：“排序和搜索”中定义。
保留字符串的第一个字母并删除以下字母的所有其他出现次数：a，e，h，i，o，u，w，y。
将剩下的字母按以下方式分配数字：
b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6
如果原始名称（在步骤1之前）中有两个或多个具有相同数字的字母相邻，或者除了任何插入的h和w之外相邻，则保留第一个字母并省略所有具有相同数字的相邻字母的其余部分。
返回前4个字节，右侧补0。
注意
如果使用SOUNDEX函数，则需要事先在search_path中指定“oracle”。
请参见
参阅“使用orafce的注意事项”了解如何编辑search_path。
示例
在下面的示例中，将从“aabcab”的结尾中删除“ab”字符串并返回。
SELECT SOUNDEX('SMYTHE')=SOUNDEX('Smith') as same FROM DUAL;
same
------
t
(1 row)
描述
使用字符指定位置和长度提取字符串的一部分。
语法
SUBSTR(str text, startPos integer) returns text
SUBSTR(str text, startPos numeric) returns text
SUBSTR(str text, startPos integer, len integer) returns text
SUBSTR(str text, startPos numeric, len numeric) returns text
SUBSTR(str CLOB, startPos number) returns CLOB
SUBSTR(str CLOB, startPos number, len number) returns CLOB
通用规则
SUBSTR从字符串str的位置startPos开始提取并返回一个子字符串，长度为len个字符。
当startPos为正数时，它将是从字符串开头算起的字符数。
当startPos为0时，它将被视为1。
当startPos为负数时，它将是从字符串末尾算起的字符数。
当未指定len时，将返回字符串末尾的所有字符。如果len小于1，则返回NULL。
对于startPos和len，请指定整数或NUMERIC类型。如果指定了包含小数位的数字，则将其截断为整数。
函数返回值为 TEXT 类型如果第一个参数是字符类型，CLOB 类型如果第一个参数是 CLOB 类型。
注意
有两种类型的SUBSTR。一种与上述描述相同，另一种与SUBSTRING相同。必须修改search_path参数才能使其与上述规范的行为相同。
如果未实施更改，则SUBSTR与SUBSTRING相同。
信息
LightDB的SUBSTRING的通用规则如下：
无论起始位置是正数、0还是负数，起始位置都将从字符串开头算起。
当未指定len时，将返回字符串末尾的所有字符。
如果未提取字符串或len小于1，则返回空字符串。
请参见
请参阅LightDB文档中的“The SQL Language” > “Functions and Operators” > “String Functions and Operators”中的SUBSTRING信息。
示例
在下面的示例中，提取了字符串“ABCDEFG”的部分内容。
SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;
Substring
-----------
CDEF
(1 row)
SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;
Substring
-----------
CDEF
(1 row)
描述
使用字节指定位置和长度提取字符串的一部分。
语法
SUBSTRB(str text, startPos integer) returns varchar2
SUBSTRB(str text, startPos integer, len integer) returns varchar2
通用规则
SUBSTR从字符串str的字节位置startPos开始提取并返回一个子字符串，长度为len个字节。
当startPos为0或负数时，提取从起始位置减1并向左移动相应位置数后找到的位置开始。
当未指定len时，将返回字符串末尾的所有字节。
如果未提取字符串或len小于1，则返回空字符串。
对于startPos和len，请指定SMALLINT或INTEGER类型。
返回值的数据类型是VARCHAR2。
注意
SUBSTRB的外部规范与orafce添加的SUBSTR不同，符合LightDB的SUBSTRING。
示例
在下面的示例中，提取了字符串“aaabbbccc”的部分内容。
SELECT SUBSTRB('aaabbbccc',4,3) FROM DUAL;
substrb
-----------
bbb
(1 row)
SELECT SUBSTRB('aaabbbccc',-2,6) FROM DUAL;
substrb
-----------
aaa
(1 row)
描述
拼接两个字符串
语法
CONCAT(str1 TEXT, str2 TEXT) returns TEXT
CONCAST(str1 CLOB, str2 CLOB) returns CLOB
通用规则
拼接两个不同类型的值时，该函数返回值类型为导致无损转换的数据类型。
该函数和拼接操作符 || 等价。
函数返回值为 TEXT 类型如果第一个参数是字符类型，CLOB 类型如果第一个参数是 CLOB 类型。
样例
SELECT concat('a', 'b') FROM DUAL;
concat
--------
ab
(1 row)
SELECT concat(to_clob('a'), 'b') FROM DUAL;
concat
--------
ab
(1 row)
描述
UPPER 返回所有字母为大写的字符串
语法
UPPER(string TEXT) returns TEXT
UPPER(string CLOB) returns CLOB
通用规则
函数返回值为 TEXT 类型如果第一个参数是字符类型，CLOB 类型如果第一个参数是 CLOB 类型。
样例
SELECT UPPER('hello world') FROM DUAL;
upper
-------------
HELLO WORLD
(1 row)
SELECT UPPER(to_clob('hello world')) FROM DUAL;
upper
-------------
HELLO WORLD
(1 row)
支持以下日期/时间函数：
ADD_MONTHS
DBTIMEZONE
LAST_DAY
MONTHS_BETWEEN
NEXT_DAY
ROUND
SESSIONTIMEZONE
SYSDATE
TRUNC
TZ_OFFSET
注意
如果日期/时间函数中只显示了DATE类型，则这些函数可在orafce和LightDB中使用。
描述
对日期添加月份。
语法
ADD_MONTHS(date DATE, months INTEGER) returns DATE
ADD_MONTHS(date TIMESTAMP WITH TIME ZONE, months INTEGER) returns TIMESTAMP
通用规则
ADD_MONTHS返回date加上months。
对于date，请指定DATE或TIMESTAMP WITH TIME ZONE类型。
对于months，请指定SMALLINT或INTEGER类型。
如果months指定为负值，则从日期中减去月数。
返回值的数据类型是DATE或TIMESTAMP。
注意
如果使用orafce的DATE类型，则需要提前指定search_path为“oracle”。
请参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
示例
下面的示例显示了在日期2016年5月1日上添加3个月的结果。
SELECT ADD_MONTHS(DATE'2016/05/01',3) FROM DUAL;
add_months
---------------------
2016-08-01 00:00:00
(1 row)
描述
返回数据库时区的值。
语法
DBTIMEZONE() returns text
通用规则
DBTIMEZONE返回数据库的时区值。
返回值的数据类型为TEXT。
注意
如果使用DBTIMEZONE，则需要提前在search_path中指定“oracle”。
数据库的时区默认设置为“GMT”。 要更改时区，请更改“orafce.timezone”参数。 下面是使用SET语句的示例。
SET orafce.timezone = 'Japan';
可以使用任何设置服务器参数的方法来设置orafce.timezone设置。
如果使用设置了orafce.timezone的SQL语句，可能会显示以下消息，但是参数设置已启用，所以可以忽略此消息。
WARNING: unrecognized configuration parameter "orafce.timezone"
可以在“orafce.timezone”中设置的时区与“TimeZone”服务器参数的设置相同。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关时区的信息，请参阅LightDB文档中的“The SQL Language” > “Data Types” > “Date/Time Types”。
示例
在以下示例中，返回DBTIMEZONE结果。
SELECT DBTIMEZONE() FROM DUAL;
dbtimezone
------------
GMT
(1 row)
描述
返回指定日期所在月份的最后一天。
语法
LAST_DAY(date DATE) returns DATE
LAST_DAY(date TIMESTAMPTZ) returns TIMESTAMPTZ
通用规则
LAST_DAY返回指定日期所在月份的最后一天。
对于date，请指定DATE或TIMESTAMPTZ类型。
返回值的数据类型为DATE或TIMESTAMPTZ。
注意
如果使用orafce的DATE类型，则需要提前在search_path中指定“oracle”。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
示例
在以下示例中，返回“2016年2月1日”的最后一天。
SELECT LAST_DAY(DATE'2016/02/01') FROM DUAL;
last_day
---------------------
2016-02-29 00:00:00
(1 row)
描述
返回两个日期之间的月数。
语法
MONTHS_BETWEEN(date1 DATE, date2 DATE) returns NUMERIC
MONTHS_BETWEEN(date1 TIMESTAMP WITH TIME ZONE, date2 TIMESTAMP WITH TIME ZONE) returns NUMERIC
通用规则
MONTHS_BETWEEN返回date1和date2之间月份的差异。
对于date1和date2，请指定DATE或TIMESTAMPTZ类型。
如果date2比date1早，返回值将为负数。
如果两个日期是同一天，或者这两个日期都是它们所属月份的最后一天，则返回一个整数。 如果这两个日期的天数不同，则将一个月视为31天，并返回一个值，该值加上日期差的除以31的结果。
返回值的数据类型为DATE或NUMERIC。
注意
如果使用orafce的DATE类型，则需要提前在search_path中指定“oracle”。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
示例
在以下示例中，返回2016年3月15日和2015年11月15日之间月份的差异。
SELECT MONTHS_BETWEEN(DATE'2016/03/15', DATE'2015/11/15') FROM DUAL;
months_between
----------------
4
(1 row)
描述
返回指定日期之后的第一个特定星期几的日期。
语法
NEXT_DAY(date DATE, dayOfWk TEXT) returns DATE
NEXT_DAY(date DATE, dayOfWk INTEGER) returns DATE
NEXT_DAY(date TIMESTAMP WITH TIME ZONE, dayOfWk TEXT) returns TIMESTAMP
NEXT_DAY(date TIMESTAMP WITH TIME ZONE, dayOfWk INTEGER) returns TIMESTAMP
通用规则
NEXT_DAY 返回符合条件的日期，即在指定日期 date 之后的第一个 dayOfWk 的实例。
对于 date，请指定为 DATE 或 TIMESTAMPTZ WITH TIME ZONE 类型。
指定表示星期几的数字值或字符串。
可指定的星期几值
Table 9.101. NEXT_DAY 函数中设置 dayOfWk 的值
|
设置示例 |
概述 |
|---|---|
|
1 |
可指定数字 1 到 7 表示星期日到星期六 |
|
'Sun' 或 'Sunday' |
英文显示的星期几 |
|
'*' |
日文显示的星期几 |
返回值的数据类型为 DATE 或 TIMESTAMP。
注意
如果使用 orafce 的 DATE 类型，则需要提前指定 search_path 为 "oracle"。
使用日语输入日期的能力是由 orafce 私有规范提供的。 当使用除 NEXT_DAY 之外的日期/时间函数（如 TO_DATE）时，无法使用日语输入日期。
参见
有关如何编辑 search_path 的信息，请参阅 "使用 orafce 的注意事项"。
示例
在下面的示例中，返回 "2016 年 5 月 1 日" 或之后的第一个星期五的日期。
SELECT NEXT_DAY(DATE'2016/05/01', 'Friday') FROM DUAL;
next_day
---------------------
2016-05-06 00:00:00
(1 row)
描述
对日期进行四舍五入。
语法
ROUND(date DATE) returns DATE
ROUND(date DATE, fmt TEXT) returns DATE
ROUND(date TIMESTAMP WITH TIME ZONE) returns TIMESTAMP WITH TIME ZONE
ROUND(date TIMESTAMP WITH TIME ZONE, fmt TEXT) returns TIMESTAMP WITH TIME ZONE
ROUND(date TIMESTAMP WITHOUT TIME ZONE) returns TIMESTAMP WITHOUT TIME ZONE
ROUND(date TIMESTAMP WITHOUT TIME ZONE, fmt TEXT) returns TIMESTAMP WITHOUT TIME ZONE
通用规则
ROUND 函数返回按格式模型 fmt 指定的单位进行四舍五入的日期。
对于 date，请指定为 DATE 或 TIMESTAMPTZ 类型。
以字符串形式指定格式模型。
可指定的格式模型值
Table 9.102. ROUND 函数中设置格式模型
|
格式模型 |
舍入单位 |
|---|---|
|
Y,YY,YYY,YYYY, SYYYY,YEAR,SYEAR |
年 |
|
I,IY,IYY,IYYY |
年（包括符合 ISO 标准的日历周的值） |
|
Q |
季度 |
|
WW |
周（一年的第一天） |
|
IW |
周（该周的星期一） |
|
W |
周（该月的第一个星期日所在的周） |
|
DAY,DY,D |
周（该周的星期日） |
|
MONTH,MON,MM,RM |
月 |
|
CC,SCC |
世纪 |
|
DDD,DD,J |
日 |
|
HH,HH12,HH24 |
小时 |
|
MI |
分钟 |
如果小数位数被四舍五入：对于年，舍入边界为 7 月 1 日； 对于月份，日期为 16 日；对于周，星期四为舍入边界。
如果省略 fmt，则按天四舍五入日期。
如果在日期中指定了 LightDB 的 DATE 类型，则返回值的数据类型将为 DATE 类型。 如果在日期中指定了 TIMESTAMP 类型，则数据类型将为 TIMESTAMP WITH TIME ZONE， 无论是否使用时区。
示例
在下面的示例中，返回按本周的星期日进行四舍五入的 "2016 年 6 月 20 日 18:00:00" 的结果。
SELECT ROUND(TIMESTAMP'2016/06/20 18:00:00','DAY') FROM DUAL;
round
------------------------
2016-06-19 00:00:00+09
(1 row)
描述
返回会话的时区。
语法
SESSIONTIMEZONE() returns text
通用规则
SESSIONTIMEZONE 返回会话之间的时区值。
返回值的数据类型为TEXT。
注意
如果使用SESSIONTIMEZONE，需要提前将search_path指定为"oracle"。
SESSIONTIMEZONE返回的值将成为"TimeZone"服务器参数中设置的值。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
示例
在下面的示例中，返回会话的时区。
SELECT SESSIONTIMEZONE() FROM DUAL;
sessiontimezone
-----------------
Japan
(1 row)
描述
返回系统日期。
语法
SYSDATE() returns oracle.date
通用规则
SYSDATE返回系统日期。
返回值的数据类型是orafce的DATE类型。
注意
如果使用SYSDATE，需要提前将search_path指定为"oracle"。
SYSDATE返回的日期依赖于orafce数据库的时区值。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关数据库时区值的信息，请参阅"DBTIMEZONE"。
有关时区的信息，请参阅LightDB文档中的“The SQL Language” > “Data Types” > “Date/Time Types”。
示例
在下面的示例中，返回系统日期。
SELECT SYSDATE() FROM DUAL;
sysdate
---------------------
2016-06-22 08:06:51
(1 row)
描述
截取日期部分。
语法
TRUNC(date DATE) returns DATE
TRUNC(date DATE, fmt TEXT) returns DATE
TRUNC(date TIMESTAMP WITH TIME ZONE) returns TIMESTAMP WITH TIME ZONE
TRUNC(date TIMESTAMP WITH TIME ZONE, fmt TEXT) returns TIMESTAMP WITH TIME ZONE
TRUNC(date TIMESTAMP WITHOUT TIME ZONE) returns TIMESTAMP WITHOUT TIME ZONE
TRUNC(date TIMESTAMP WITHOUT TIME ZONE, fmt TEXT) returns TIMESTAMP WITHOUT TIME ZONE
通用规则
TRUNC返回按格式模型fmt指定的单位截断的日期。
对于date，请指定DATE或TIMESTAMP类型。
将格式模型指定为字符串。可以指定的值与ROUND相同。
如果省略fmt，则按天截断日期。
如果在日期中指定了LightDB的DATE类型，则返回值的数据类型将是该DATE类型。 如果在日期中指定了TIMESTAMP类型，则返回值的数据类型为TIMESTAMP WITH TIME ZONE， 无论是否使用时区。
参见
有关可以为格式模型指定的值的信息，请参阅“ROUND”。
示例
在下面的示例中，返回截取了日期部分的"August 10, 2016 15:30:00"。
SELECT TRUNC(TIMESTAMP'2016/08/10 15:30:00','DDD') FROM DUAL;
trunc
------------------------
2016-08-10 00:00:00+09
(1 row)
描述
返回时区偏移量。
语法
TZ_OFFSET(text) returns text
通用规则
您可以输入一个有效的时区区域名称，或一个距UTC的时区偏移量（仅返回该偏移量本身）。
要获取time_zone_name的有效值列表，请查询pg_timezone_names的name列。
注意
如果使用TZ_OFFSET，需要提前将search_path指定为"oracle"。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
请参考LightDB文档中的"服务器管理" > "服务器配置" > "设置参数"，了解如何设置服务器参数。
示例
SELECT TZ_OFFSET('Asia/Shanghai') FROM DUAL;
tz_offset
-----------
08:00:00
(1 row)
(1 row)
支持以下数据类型格式化函数：
CONVERT
NUMTOYMINTERVAL
TO_BLOB(raw)
TO_CHAR
TO_DATE
TO_MULTI_BYTE
TO_NUMBER
TO_SINGLE_BYTE
ASCIISTR
ASCII
SQLCODE_TO_SQLSTATE
TO_CLOB
RAWTOHEX
ROWIDTOCHAR
描述
CONVERT将一个字符字符串从一个字符集转换为另一个字符集。
语法
CONVERT(str TEXT,dest_char_set TEXT,source_char_set TEXT DEFAULT 'utf8') returns TEXT
通用规则
str参数是要转换的值。
dest_char_set参数是将str转换为的字符集的名称。
source_char_set参数是数据库中存储str的字符集的名称。默认值为数据库字符集。
目标字符集和源字符集参数都可以是文字或包含字符集名称的列。
为了在字符转换中实现完全对应，目标字符集必须包含源字符集中定义的所有字符的表示。如果目标字符集中不存在某个字符，则会出现替换字符。替换字符可以作为字符集定义的一部分进行定义。
注意
如果使用CONVERT转换字符串，则需要事先指定"oracle"用于search_path。
参见
请参考"使用orafce的注意事项"了解如何编辑search_path。
请参考LightDB文档中的"服务器管理" > "服务器配置" > "设置参数"，了解如何设置服务器参数。
示例
SELECT CONVERT('娴嬭瘯', 'gbk', 'utf8') from dual; FROM DUAL;
convert
---------
测试
(1 row)
描述
NUMTOYMINTERVAL将数字转换为INTERVAL YEAR TO MONTH文字。
语法
NUMTOYMINTERVAL(n numeric, interval_unit text) returns interval
通用规则
interval_unit的值指定n的单位，并且必须解析为以下字符串值之一：'YEAR'，'MONTH'。
interval_unit不区分大小写。
注意
如果使用NUMTOYMINTERVAL指定日期/时间值，则需要事先指定"oracle"用于search_path。
参见
请参考"使用orafce的注意事项"了解如何编辑search_path。
请参考LightDB文档中的"服务器管理" > "服务器配置" > "设置参数"，了解如何设置服务器参数。
示例
SELECT NUMTOYMINTERVAL(1,'YEAR') FROM DUAL;
numtoyminterval
-----------------
1 year
(1 row)
描述
TO_BLOB(raw)将RAW值转换为BLOB值。
语法
TO_BLOB(raw) returns blob
注意
如果使用TO_BLOB(raw)函数，则需要事先指定"oracle"用于search_path。
参见
请参考"使用orafce的注意事项"了解如何编辑search_path。
请参考LightDB文档中的"服务器管理" > "服务器配置" > "设置参数"，了解如何设置服务器参数。
示例
SELECT TO_BLOB(UTL_RAW.CAST_TO_RAW('测试')) FROM DUAL;
to_blob
----------------
\xe6b58be8af95
(1 row)
描述
将一个值转换为字符串。
语法
TO_CHAR(num SMALLINT) returns TEXT
TO_CHAR(num INTEGER) returns TEXT
TO_CHAR(num BIGINT) returns TEXT
TO_CHAR(num REAL) returns TEXT
TO_CHAR(num DOUBLE PRECISION) returns TEXT
TO_CHAR(num NUMERIC) returns TEXT
TO_CHAR(date TIMESTAMP) returns TEXT
TO_CHAR(TEXT) returns TEXT
TO_CHAR(date TIMESTAMP, fmt TEXT) returns TEXT
TO_CHAR(date TIMESTAMP WITH TIME ZONE, fmt TEXT) returns TEXT
TO_CHAR(date TIMESTAMP, fmt TEXT, nls_date_language TEXT) returns TEXT
TO_CHAR(date TIMESTAMP WITH TIME ZONE, fmt TEXT, nls_date_language TEXT) returns TEXT
通用规则
TO_CHAR将特定的数字或日期/时间值转换为字符串。
对于num，请指定数字数据类型。
对于date，请指定DATE或TIMESTAMP类型。 另外，您可以事先为nls_date_format变量设置日期/时间格式，则无需 TO_CHAR 调用时指定。 以下是使用SET语句进行设置的示例。
SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
无论显示指定还是调用时指定，格式符大小写效果相同。
对于fmt，用于设置数据格式。 当第一个参数是数字类型时，请参见数字值的模版模式。 当第一个参数是日期类型时，请参见日期值的模板模式。
对于nls_date_language，用于设置日期的显示语言环境。
返回值的数据类型为TEXT。
注意
如果使用TO_CHAR来指定日期/时间值，则需要事先将search_path设置为"oracle"。
可以使用任何设置服务器参数的方法来设置nls_date_format设置。
如果设置了nls_date_format，则在执行SQL语句时可能会显示以下消息，但是参数设置已启用，因此可以忽略此消息。
WARNING: unrecognized configuration parameter "nls_date_format"
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理” > “服务器配置” > “设置参数”。
示例
在以下示例中，将数字值“123.45”作为字符串返回。
SELECT TO_CHAR(123.45) FROM DUAL;
to_char
---------
123.45
(1 row)
SELECT to_char (sysdate, 'YYYY-MM-DD', 'NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''' );
to_char
------------
2023-12-04
(1 row)
描述
根据指定的格式将字符串转换为日期。
语法
TO_DATE(str TEXT) returns TIMESTAMP
TO_DATE(str TEXT, fmt TEXT) returns TIMESTAMP
TO_DATE(oracle.date) returns TIMESTAMP
通用规则
TO_DATE将字符串str根据指定的格式fmt转换为日期。
指定一个表示日期/时间的字符串。
指定所需的日期/时间格式。如果省略，则使用nls_date_format变量中指定的格式。 如果未设置nls_date_format变量，则使用现有的日期/时间输入解释。 以下是使用SET语句进行设置的示例。
SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
返回值的数据类型为TIMESTAMP。
注意
上述TO_DATE规范使用orafce来实现，其行为与LightDB的TO_DATE不同。 必须修改search_path参数，以使其按照orafce规范运行。
可以使用任何设置服务器参数的方法来设置nls_date_format设置。
如果设置了nls_date_format，则在执行SQL语句时可能会显示以下消息，但是参数设置已启用，因此可以忽略此消息。
WARNING: unrecognized configuration parameter "nls_date_format"
信息
LightDB中TO_DATE指定数据类型格式的通用规则如下：
返回值的数据类型是LightDB的DATE类型。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关LightDB中TO_DATE的信息，请参阅LightDB文档中的“The SQL Language” > “Functions and Operators” > “Data Type Formatting Functions”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理” > “服务器配置” > “设置参数”。
有关现有日期/时间输入解释的信息，请参阅LightDB文档中的“日期/时间支持” > “日期/时间输入解释”。
示例
在以下示例中，将字符串“2016/12/31”转换为日期并返回。
SELECT TO_DATE('2016/12/31','YYYY/MM/DD') FROM DUAL;
to_date
---------------------
2016-12-31 00:00:00
(1 row)
SELECT to_date('2009-01-02'::oracle.date);
to_date
---------------------
2009-01-02 00:00:00
(1 row)
描述
将单字节字符串转换为多字节字符串。
语法
TO_MULTI_BYTE(str TEXT) returns TEXT
通用规则
TO_MULTI_BYTE将字符串str中的半角字符转换为全角字符，并返回转换后的字符串。
指定一个表示日期/时间的字符串。
指定所需的日期/时间格式。如果省略，则使用nls_date_format变量中指定的格式。 如果未设置nls_date_format变量，则使用现有的日期/时间输入解释。 以下是使用SET语句进行设置的示例。
Only halfwidth alphanumeric characters, spaces and symbols can be converted.
返回值的数据类型是TEXT。
示例
在以下示例中，将“abc123”转换为全角字符并返回。
SELECT TO_MULTI_BYTE('abc123') FROM DUAL;
to_multi_byte
---------------
******
(1 row)
“******”是多字节的“abc123”。
描述
根据指定的格式将值转换为数字。
语法
TO_NUMBER(str TEXT) returns NUMERIC
TO_NUMBER(num NUMERIC) returns NUMERIC
TO_NUMBER(num NUMERIC, fmt NUMERIC) returns NUMERIC
通用规则
TO_NUMBER将指定的值按照指定的格式fmt转换为数字值。
对于num，指定一个数字数据类型。
对于str，指定一个表示数字值的字符串。 数字值必须由可转换字符组成。
指定所需的数字数据格式。 指定的数字值将被处理为数据类型表达式。
返回值的数据类型是 NUMERIC。
详见
有关数字值格式的信息，请参阅LightDB文档中的"The SQL Language" > "Functions and Operators" > "Data Type Formatting Functions"。
示例
在以下示例中，将数字字面值“-130.5”转换为数字值并返回。
SELECT TO_NUMBER(-130.5) FROM DUAL;
to_number
-----------
-130.5
(1 row)
描述
将多字节字符串转换为单字节字符串。
语法
TO_SINGLE_BYTE(str TEXT) returns TEXT
通用规则
TO_SINGLE_BYTE将字符串str中的全角字符转换为半角字符，并返回转换后的字符串。
只有可以显示为半角的全角字母数字字符、空格和符号才能被转换。
返回值的数据类型为TEXT。
示例
在以下示例中，“******”被转换为半角字符并返回。 “******”是多字节字符串“xyz999”。
SELECT TO_SINGLE_BYTE('******') FROM DUAL;
to_single_byte
----------------
xyz999
(1 row)
描述
ASCIISTR以一个字符串或解析为字符串的表达式作为其参数，并返回数据库字符集中字符串的ASCII版本。
语法
ASCIISTR(str TEXT) returns TEXT
通用规则
非ASCII字符被转换为形式\ xxxx，其中xxxx表示UTF-16代码单元。
返回值的数据类型为TEXT。
示例
在以下示例中，“Ä”被转换为“\00C4”。
SELECT ASCIISTR('ABÄCDE') FROM DUAL;
asciistr
----------------
AB\00C4CDE
(1 row)
描述
ASCII以一个字符串或解析为字符串的表达式作为其参数，并返回第一个字符的数据库字符集中的值。
语法
ASCIISTR(str TEXT) returns BIGINT
通用规则
如果您的数据库字符集是7位ASCII，则此函数返回ASCII值。如果您的数据库字符集是UTF-8 Code，则此函数返回UTF-8值。
返回值的数据类型为BIGINT。
示例
在以下示例中，“Ä”被转换为UTF-8中的“50052”。
SELECT ORACLE.ASCII('Ä') FROM DUAL;
ascii
-------
50052
(1 row)
描述
将整数错误代码转换为长度为5的字符串错误代码。
语法
SQLCODE_TO_SQLSTATE(code INTEGER) returns TEXT
通用规则
只能转换0和715827882之间的整数错误代码。
返回值的数据类型为TEXT。
示例
在以下示例中，转换错误码2。
select SQLCODE_TO_SQLSTATE(2);
sqlcode_to_sqlstate
---------------------
20000
(1 row)
描述
将字符字符串或数值转换为CLOB值。
语法
TO_CLOB(str TEXT) returns CLOB
通用规则
TO_CLOB 转换值为CLOB值.
示例
CREATE TABLE testorafce_to_clob (
col_char CHAR(10),
col_varchar2 VARCHAR2(20),
col_varchar VARCHAR(20),
col_nchar NCHAR(10),
col_nvarchar2 NVARCHAR2(20),
col_smallint smallint,
col_integer integer,
col_bigint bigint,
col_decimal decimal,
col_numeric numeric,
col_real real,
col_double double precision,
col_clob CLOB,
col_raw raw(10)
);
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC1', 'Hello', 'world', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', 'AB');
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC2', 'Hello', 'world', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', '1');
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC3', 'Hello', 'world', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, to_clob('This is a CLOB'), '1AB456789');
SELECT to_clob(col_char) AS clob_char,
to_clob(col_varchar2) AS clob_varchar2,
to_clob(col_varchar) AS col_varchar,
to_clob(col_nchar) AS clob_nchar,
to_clob(col_nvarchar2) AS clob_nvarchar2,
to_clob(col_clob) AS clob_clob,
to_clob(col_smallint) AS col_smallint,
to_clob(col_integer) AS col_integer,
to_clob(col_bigint) AS col_bigint,
to_clob(col_decimal) AS col_decimal,
to_clob(col_numeric) AS col_numeric,
to_clob(col_real) AS col_real,
to_clob(col_double) AS col_double,
to_clob(col_raw) AS clob_nclob
FROM testorafce_to_clob order by col_char asc;
clob_char | clob_varchar2 | col_varchar | clob_nchar | clob_nvarchar2 | clob_clob | col_smallint | col_integer | col_bigint | col_decimal | col_numeric | col_real | col_double | clob_nclob
------------+---------------+-------------+------------+----------------+----------------+--------------+-------------+------------+-------------+-------------+----------+------------+------------
ABC1 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | AB
ABC2 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 01
ABC3 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 01AB456789
(3 rows)
描述
RAWTOHEX将传入值转换为包含其十六进制表示形式的字符值。
语法
RAWTOHEX(test_raw RAW) returns TEXT
RAWTOHEX(test_text TEXT) returns TEXT
RAWTOHEX(test_uuid UUID) returns VARCHAR2
通用规则
此函数返回一个值，该值具有构成传入值的字节的十六进制表示形式。每个字节由两个十六进制数字表示。
示例
select rawtohex(sys_guid());
rawtohex
----------------------------------
6466c654a5dc4755902b70d0e6ea6eb6
(1 row)
描述
ROWIDTOCHAR将传入值ORACLE的tid类型的ROWID转换为varchar2形式的字符值。
语法
ROWIDTOCHAR(test_rowid TID) returns VARCHAR2
通用规则
此函数返回一个值，该值具有构成传入值的字节转化为的varchar2的表示形式。最大长度为18位。
示例
drop TABLE if EXISTS rowidtochar_t1;
create table rowidtochar_t1
(
id int not null
);
insert into rowidtochar_t1 values(1);
select rowidtochar(rowid) from rowidtochar_t1;
rowidtochar
-------------
(0,1)
(1 row)
支持以下用于比较的函数：
DECODE
LNNVL
NANVL
NVL
NVL2
描述
比较值，如果它们匹配，则返回相应的值。
语法
DECODE(expr, srch, result)
DECODE(expr, srch, result, default)
DECODE(expr, srch1, result1, srch2, result2)
DECODE(expr, srch1, result1, srch2, result2, default)
DECODE(expr, srch1, result1, srch2, result2, srch3, result3)
DECODE(expr, srch1, result1, srch2, result2, srch3, result3, default)
通用规则
DECODE按顺序逐个比较要转换的值表达式和搜索值。 如果值匹配，则返回相应的结果值。 如果没有匹配的值，则返回指定的默认值。 如果没有指定默认值，则返回NULL值。
如果指定了相同的搜索值超过一次，则返回的结果值是列出的第一个搜索值的结果值。
结果值和默认值中可以使用以下数据类型：
CHAR
VARCHAR
VARCHAR2
NCHAR
NCHAR VARYING
NVARCHAR2
TEXT
INTEGER
BIGINT
NUMERIC
DATE
TIME WITHOUT TIME ZONE
TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP WITH TIME ZONE
要转换的值和搜索值必须指定相同的数据类型。 但是，请注意，如果在搜索值中指定了文字，可以指定不同的数据类型，且要转换的值表达式包含可以转换的数据类型。
如果结果值和默认值都是文字，则这些值的数据类型如下所示：
如果所有值都是字符串文字，则所有值都将成为字符类型。
如果有一个或多个数字文字，则所有值都将变为数值类型。
如果有一个或多个文字转换为日期时间/时间类型，则所有值都将变为日期时间/时间类型。
如果结果值和默认值包含文字和非文字的混合，则文字将转换为非文字的数据类型。
所有结果值和默认值都必须指定相同的数据类型。 但是，如果可以转换结果值或默认值的任何数据类型，则可以指定不同的数据类型-这些数据类型如下所示：
Table 9.103. DECODE可以转换的数据类型组合（摘要）
| 其他结果值或默认值 | ||||
|---|---|---|---|---|
| 数值类型 | 字符类型 | 日期/时间类型 | ||
| 结果值（任何） | 数值类型 | Y | N | N |
| 字符类型 | N | Y | N | |
| 日期/时间类型 | N | N | S(*1) | |
Y：可以转换
S：可转换一些数据类型
N：无法转换
*1：可转换日期/时间类型的数据类型如下所示：
Table 9.104. DECODE可转换的结果值和默认值日期/时间数据类型
| 其他结果值或默认值 | |||||
|---|---|---|---|---|---|
| DATE | TIME WITHOUT TIME ZONE | TIMESTAMP WITHOUT TIME ZONE | TIMESTAMP WITH TIME ZONE | ||
| 结果值（任何） | DATE | Y | N | Y | Y |
| TIME WITHOUT TIME ZONE | N | Y | N | N | |
| TIMESTAMP WITHOUT TIME ZONE | Y | N | Y | Y | |
| TIMESTAMP WITH TIME ZONE | Y | N | Y | Y | |
Y：可以转换
N：无法转换
返回值的数据类型将是结果值或默认值中最长且精度最高的数据类型。
示例
在下面的示例中，将比较并转换表t1中col3的值为不同的值。 如果col3值匹配搜索值1，则返回的结果值是“one”。 如果col3值不与搜索值1、2或3中的任何一个匹配，则返回默认值“other number”。
SELECT col1,
DECODE(col3, 1, 'one',
2, 'two',
3, 'three',
'other number') "num-word"
FROM t1;
col1 | num-word
------+----------
1001 | one
1002 | two
1003 | three
(3 rows)
描述
确定指定条件的值是TRUE还是FALSE。
语法
LNNVL(cond BOOLEAN) returns BOOLEAN
通用规则
LNNVL确定指定条件的值是TRUE还是FALSE。 如果条件的结果是FALSE或NULL，则返回TRUE。 如果条件的结果是TRUE，则返回FALSE。
在条件中指定了返回TRUE或FALSE的表达式。
返回值的数据类型是BOOLEAN。
示例
在以下示例中，当col3的值为2000或更低或为空时，返回表t1的col1和col3。
SELECT col1,col3 FROM t1 WHERE LNNVL( col3 > 2000 );
col1 | col3
------+------
1001 | 1000
1002 | 2000
2002 |
(3 row)
描述
当值不是数字（NaN）时返回替代值。
语法
NANVL(expr FLOAT4, substitute FLOAT4) returns FLOAT4
NANVL(expr FLOAT8, substitute FLOAT8) returns FLOAT8
NANVL(expr NUMERIC, substitute NUMERIC) returns NUMERIC
NANVL(expr FLOAT4, substitute VARCHAR) returns FLOAT4
NANVL(expr FLOAT8, substitute VARCHAR) returns FLOAT8
NANVL(expr NUMERIC, substitute VARCHAR) returns NUMERIC
通用规则
NANVL函数在指定的值不是数字（NaN）时返回替代值。 替代值可以是数字或可转换为数字的字符串。
对于expr和substitute，请指定数字数据类型。 如果expr和substitute具有不同的数据类型， 它们将转换为具有更大长度或精度的数据类型，并返回该数据类型。
对于substitute，您还可以指定表示数字值的字符串。
如果为替代值指定了字符串，则返回值使用的数据类型与expr的数据类型相同。
示例
在以下示例中，如果表t1中的col1的值是NaN值，则返回“0”。
SELECT col1, NANVL(col3,0) FROM t1;
col1 | nanvl
------+-------
2001 | 0
(1 row)
描述
当值为NULL时返回替代值。
语法
NVL(expr1 anyelement, expr2 anyelement) return anyelement
通用规则
NVL函数在指定的值为NULL时返回替代值。 当expr1为NULL时，返回expr2。 当expr1不为NULL时返回expr1。
请为expr1和expr2指定相同的数据类型。 然而，如果在expr2中指定了常量，并且数据类型也可以由expr1转换， 则可以指定不同的数据类型。 在这种情况下，expr2的转换将根据expr1的数据类型进行调整， 因此expr1为NULL值时返回的expr2的值将以expr1的 数据类型转换。 对于类型（numeric、int）和（bigint、int）来说，这是不必要的。
示例
在以下示例中，如果表t1中的col1的值是NULL值，则返回“IS NULL”。
SELECT col2, NVL(col1,'IS NULL') "nvl" FROM t1;
col2 | nvl
------+---------
aaa | IS NULL
(1 row)
描述
根据值是否为NULL或非NULL返回替代值。
语法
NVL2(expr anyelement, substitute1 anyelement, substitute2 anyelement) return anyelement
通用规则
NVL2函数根据指定的值是否为NULL返回替代值。 当expr为NULL时，返回substitute2。 当它不为NULL时，返回substitute1。
请为expr、substitute1和substitute2指定相同的数据类型。 然而，如果在substitute1或substitute2中指定了文字，而且数据类型也可以由 expr转换，则可以指定不同的数据类型。在这种情况下，substitute1或 substitute2将根据expr的数据类型进行调整，因此当expr 为NULL值时返回的substitute2的值将以expr的数据类型转换。
示例
在以下示例中，如果表t1中列col1中的值为NULL，则返回“IS NULL”，如果不为NULL，则返回“IS NOT NULL”。
SELECT col2, NVL2(col1,'IS NOT NULL','IS NULL') FROM t1;
col2 | nvl2
------+---------
aaa | IS NULL
bbb | IS NOT NULL
(2 row)
支持以下聚合函数：
ANY_VALUE
BIT_AND_AGG
BIT_OR_AGG
BIT_XOR_AGG
KURTOSIS_POP
KURTOSIS_SAMP
LISTAGG
MEDIAN
SKEWNESS_POP
SKEWNESS_SAMP
WY_CONCAT
描述
ANY_VALUE函数返回表达式（expr）的一个非确定性值。
语法
ANY_VALUE(expr anyelement) returns anyelement
通用规则
使用ANY_VALUE来优化包含GROUP BY子句的查询。ANY_VALUE返回一组中一个表达式的值。它被优化以返回第一个值。
它确保不会为任何传入的行进行比较，并消除了将每个列指定为GROUP BY子句的一部分的必要性。
由于它不比较值，在GROUP BY查询中，ANY_VALUE比MIN或MAX更快地返回一个值。
根据GROUP BY规范，返回每个组中的任何值。如果组中所有行的表达式值都为NULL，则返回NULL。
ANY_VALUE的结果是非确定性的。
注意
如果使用ANY_VALUE，则需要事先指定search_path为"oracle"。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理” > “服务器配置” > “设置参数”。
示例
create table test_any_value(key1 int, key2 int);
insert into test_any_value values(1, 1);
select any_value(key2) from test_any_value group by key1;
any_value
-----------
1
(1 row)
描述
BIT_AND_AGG是一个位运算聚合函数，返回位与运算的结果。
语法
BIT_AND_AGG(numeric) returns int
通用规则
您可以将BIT_AND_AGG作为GROUP BY查询、窗口函数或分析函数的一部分使用。
对于一组给定的值，位运算聚合的结果总是确定性的，与排序无关。
返回值的数据类型是int。
注意
如果使用BIT_AND_AGG，则需要事先指定search_path为"oracle"。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理” > “服务器配置” > “设置参数”。
示例
select bit_and_agg(column1) from (values (1),(2),(4),(8)) x;
bit_and_agg
-------------
0
(1 row)
描述
BIT_OR_AGG是一个位运算聚合函数，返回位或运算的结果。
语法
BIT_OR_AGG(numeric) returns int
通用规则
您可以将BIT_OR_AGG作为GROUP BY查询、窗口函数或分析函数的一部分使用。
对于一组给定的值，位运算聚合的结果总是确定性的，与排序无关。
返回值的数据类型是int。
注意
如果使用BIT_OR_AGG，则需要事先指定search_path为"oracle"。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参考LightDB文档中的"服务器管理" > "服务器配置" > "设置参数"一节。
示例
select bit_or_agg(column1) from (values (1),(2),(4),(8)) x;
bit_or_agg
------------
15
(1 row)
描述
BIT_XOR_AGG是一个位运算聚合函数，返回位异或操作的结果。
语法
BIT_XOR_AGG(numeric) returns int
通用规则
您可以将BIT_XOR_AGG作为GROUP BY查询、窗口函数或分析函数的一部分使用。
对于给定的一组值，位聚合的结果始终是确定性的，并且与排序无关。
返回值的数据类型是int。
注意
如果使用BIT_XOR_AGG函数，需要提前在search_path中指定"oracle"。
参见
有关如何编辑search_path的信息，请参考"使用orafce的注意事项"。
有关如何设置服务器参数的信息，请参考LightDB文档中的"服务器管理" > "服务器配置" > "设置参数"一节。
示例
select bit_xor_agg(column1) from (values (1),(3),(4),(8)) x;
bit_xor_agg
-------------
14
(1 row)
描述
总体峰度函数KURTOSIS_POP主要用于确定给定分布中的异常值特征。
语法
KURTOSIS_POP(numeric) returns numeric
通用规则
expr中的NULL值将被忽略。
如果组中所有行的expr值都为NULL，则返回NULL。
如果expr中只有一个或两个行，则返回0。
对于给定的一组值，总体峰度（KURTOSIS_POP）和样本峰度（KURTOSIS_SAMP）的结果始终是确定性的。但是，KURTOSIS_POP和KURTOSIS_SAMP的值是不同的。随着数据集中值的数量增加，计算出的KURTOSIS_SAMP和KURTOSIS_POP值之间的差异会减小。
注意
如果使用KURTOSIS_POP函数，需要提前在search_path中指定"oracle"。
参见
有关如何编辑search_path的信息，请参考"使用orafce的注意事项"。
有关如何设置服务器参数的信息，请参考LightDB文档中的"服务器管理" > "服务器配置" > "设置参数"一节。
示例
select kurtosis_pop(column1) from (values (1),(2),(4),(8)) x;
kurtosis_pop
-------------------------
-1.09897920604914942667
(1 row)
描述
样本峰度(KURTOSIS_SAMP)函数主要用于确定给定分布中的异常值特征。
语法
KURTOSIS_SAMP(numeric) returns numeric
总则
expr中的NULL值将被忽略。
如果组中的所有行都具有NULL表达式值，则返回NULL。
如果expr中只有一行或两行，则返回0。
对于一组给定的值，样本峰度(KURTOSIS_SAMP)和总体峰度(KURTOSIS_POP)的结果始终是确定性的。 然而，KURTOSIS_SAMP和KURTOSIS_POP的值是不同的。随着数据集中值的数量增加，计算出的KURTOSIS_SAMP和KURTOSIS_POP的差异减小。
注意
如果使用KURTOSIS_SAMP函数，则需要提前指定“oracle”作为search_path。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理”>“服务器配置”>“设置参数”。
示例
select kurtosis_samp(column1) from (values (1),(2),(4),(8)) x;
kurtosis_samp
--------------------
0.7576559546313793
(1 row)
描述
返回一个由字符串值组成的连接的、带分隔符的列表。
语法
LISTAGG(strExpr TEXT) returns TEXT
LISTAGG(strExpr TEXT, delimiter TEXT) []returns TEXT
LISTAGG(strExpr TEXT, delimiter TEXT ON OVERFLOW TRUNCATE) []returns TEXT
LISTAGG(strExpr TEXT, delimiter TEXT ON OVERFLOW ERROR) []returns TEXT
总则
LISTAGG连接和分隔一组字符串值，并返回结果。
对于delimiter，请指定一个字符串。 如果省略了分隔符，则返回一个没有分隔符的字符串列表。
在LightDB中，LISTAGG支持在函数中使用distinct和within group子句。 例如：LISTAGG(distinct c1) within group(order by c1)
在LightDB中，LISTAGG支持在函数中使用on overflow truncate。 例如：LISTAGG(c1, ':' on overflow truncate)
在LightDB中，LISTAGG支持在函数中使用on overflow error。 例如：LISTAGG(c1, ':' on overflow error)
在LightDB中，LISTAGG支持在over子句中使用within group子句。 在这种情况下，我们还不能在函数中使用distinct。 例如：LISTAGG(c1) within group(order by c1) over(partition by c2)
返回值的数据类型为TEXT。
示例
在下面的示例中，返回由表t1中列col2的值组成的、以“:”为分隔符的结果。
SELECT LISTAGG(col2,':') FROM t1;
listagg
-------------------
AAAAA:BBBBB:CCCCC
(1 row)
在下面的示例中，使用over中的within group。
CREATE TABLE EMP
( EMPNO NUMBER(4, 0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4, 0),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2, 0),
DNAME VARCHAR2(100),
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7369, 'SMITH', 'CLERK', 7902, to_date('1980-12-17','yyyy-mm-dd'), 800.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('1981-02-20','yyyy-mm-dd'), 1600.00, 300.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7521, 'WARD', 'SALESMAN', 7698, to_date('1981-02-22','yyyy-mm-dd'), 1250.00, 500.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7566, 'JONES', 'MANAGER', 7839, to_date('1981-04-02','yyyy-mm-dd'), 2975.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('1981-09-28','yyyy-mm-dd'), 1250.00, 1400.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7698, 'BLAKE', 'MANAGER', 7839, to_date('1981-05-01','yyyy-mm-dd'), 2850.00, null, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7782, 'CLARK', 'MANAGER', 7839, to_date('1981-06-09','yyyy-mm-dd'), 2450.00, null, 10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7788, 'SCOTT', 'ANALYST', 7566, to_date('1987-04-19','yyyy-mm-dd'), 3000.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7839, 'KING', 'PRESIDENT',null, to_date('1981-11-17','yyyy-mm-dd'), 5000.00, null, 10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7844, 'TURNER', 'SALESMAN', 7698, to_date('1981-09-08','yyyy-mm-dd'), 1500.00, 0.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7876, 'ADAMS', 'CLERK', 7788, to_date('1987-05-23','yyyy-mm-dd'), 1100.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7900, 'JAMES', 'CLERK', 7698, to_date('1981-12-03','yyyy-mm-dd'), 950.00, null, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7902, 'FORD', 'ANALYST', 7566, to_date('1981-12-03','yyyy-mm-dd'), 3000.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7934, 'MILLER', 'CLERK', 7782, to_date('1982-01-23','yyyy-mm-dd'), 1300.00, null, 10, null);
select listagg(ename, ',') within group(order by ename) over(partition by deptno) as enames,
deptno,
ename
from EMP;
enames | deptno | ename
--------------------------------------+--------+--------
CLARK,KING,MILLER | 10 | CLARK
CLARK,KING,MILLER | 10 | KING
CLARK,KING,MILLER | 10 | MILLER
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | ADAMS
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | FORD
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | JONES
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | SCOTT
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | SMITH
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | ALLEN
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | BLAKE
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | JAMES
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | MARTIN
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | TURNER
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | WARD
(14 rows)
描述
计算一组数字的中位数。
语法
MEDIAN(numExpr REAL) returns REAL
MEDIAN(numExpr DOUBLE PRECISION) returns DOUBLE PRECISION
通用规则
MEDIAN返回一组数字的中位数。
数字必须是数值数据类型。
如果数字是REAL类型，则返回值的数据类型为REAL；如果指定了其他类型，则为DOUBLE PRECISION。
示例
在下面的示例中，返回表t1中列col3的中位数。
SELECT MEDIAN(col3) FROM t1;
median
--------
2000
(1 row)
描述
SKEWNESS_POP是一个聚合函数，主要用于确定给定分布中的对称性。
语法
SKEWNESS_POP(numeric) returns numeric
通用规则
忽略expr中的NULL值。
如果组中所有行的expr值都为NULL，则返回NULL。
如果expr中有一行或两行，则返回0。
对于给定的值集，总体偏度（SKEWNESS_POP）和样本偏度（SKEWNESS_SAMP）的结果始终是确定性的。但是，SKEWNESS_POP和SKEWNESS_SAMP的值是不同的。随着数据集中值的数量增加，SKEWNESS_SAMP和SKEWNESS_POP的计算值之间的差异会减小。
注意
如果使用SKEWNESS_POP函数，则需要事先指定search_path为“oracle”。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理”>“服务器配置”>“设置参数”。
示例
SELECT SKEWNESS_POP(column1) from (values (1),(2),(4),(8)) x;
skewness_pop
------------------------
0.65680773449969915746
(1 row)
描述
SKEWNESS_SAMP是一个聚合函数，主要用于确定给定分布中的对称性。
语法
SKEWNESS_SAMP(numeric) returns numeric
通用规则
忽略expr中的NULL值。
如果组中所有行的expr值都为NULL，则返回NULL。
如果expr中有一行或两行，则返回0。
对于给定的值集，总体偏度（SKEWNESS_POP）和样本偏度（SKEWNESS_SAMP）的结果始终是确定性的。但是，SKEWNESS_POP和SKEWNESS_SAMP的值是不同的。随着数据集中值的数量增加，SKEWNESS_SAMP和SKEWNESS_POP的计算值之间的差异会减小。
注意
如果使用SKEWNESS_SAMP函数，则需要事先指定search_path为“oracle”。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理”>“服务器配置”>“设置参数”。
示例
SELECT SKEWNESS_SAMP(column1) from (values (1),(2),(4),(8)) x;
skewness_samp
------------------------
1.13762436695768880892
(1 row)
描述
返回一个由字符串值组成的连接的、带逗号分隔符的列表。
语法
WY_CONCAT(strExpr TEXT) returns CLOB
总则
WY_CONCAT连接和分隔一组字符串值，并返回结果。
返回值的数据类型为CLOB。
示例
在下面的示例中，返回由表t1中列col2的值组成的、以“,”为分隔符的结果。
SELECT WY_CONCAT(col2) FROM t1;
wy_concat
-------------------
AAAAA,BBBBB,CCCCC
(1 row)
支持以下返回内部信息的函数:
DUMP
NLS_CHARSET_ID
NLS_CHARSET_NAME
SYS_CONTEXT
USERENV
描述
返回一个值的内部信息。
语法
DUMP(expr TEXT) returns VARCHAR
DUMP(expr TEXT, fmt INTEGER) returns VARCHAR
通用规则
DUMP命令以符合输出格式的显示格式返回指定表达式的内部信息。
数据类型的内部代码（Typ）、数据长度（Len）和数据的内部表达式将作为内部信息输出。
可以为表达式指定任何数据类型。
数据的内部表达式的显示格式（基数n）由输出格式指定。可以指定的基数为8、10和16。如果省略，则默认使用10。
返回值的数据类型为VARCHAR。
注意
DUMP命令输出的信息将是完整的内部信息。因此，由于产品更新等原因，这些值可能会发生变化。
示例
在下面的示例中，将返回表t1中列col1的内部信息。
SELECT col1, DUMP(col1) FROM t1;
col1 | dump
------+------------------------------------
1001 | Typ=25 Len=8: 32,0,0,0,49,48,48,49
1002 | Typ=25 Len=8: 32,0,0,0,49,48,48,50
1003 | Typ=25 Len=8: 32,0,0,0,49,48,48,51
(3 row)
描述
NLS_CHARSET_ID返回与字符集名称字符串对应的字符集ID编号。
语法
NLS_CHARSET_ID(str text) returns integer
通用规则
无效的字符集名称返回null。
注意
字符集名称来自于Oracle，请仅使用此函数进行兼容性。
如果使用NLS_CHARSET_ID函数，则需要提前指定search_path为"oracle"。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理”>“服务器配置”>“设置参数”。
示例
下面的示例返回字符集的字符集ID：
SELECT NLS_CHARSET_ID('AL32UTF8') FROM DUAL;
nls_charset_id
----------------
873
(1 row)
描述
NLS_CHARSET_NAME返回与ID编号对应的字符集名称。
语法
NLS_CHARSET_NAME(str text) returns integer
通用规则
如果number不能被识别为有效的字符集ID，则此函数将返回null。
注意
字符集名称来自于Oracle，请仅使用此函数进行兼容性。
如果使用NLS_CHARSET_NAME函数，则需要提前指定search_path为"oracle"。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理”>“服务器配置”>“设置参数”。
示例
下面的示例返回与字符集ID编号873对应的字符集：
SELECT NLS_CHARSET_NAME(873) FROM DUAL;
nls_charset_name
------------------
AL32UTF8
(1 row)
描述
返回与上下文命名空间相关联的参数在当前时刻的值。
语法
SYS_CONTEXT(namespace text, parameter text, length int4 default 256) returns text
通用规则
对于命名空间和参数，可以指定字符串或解析为指定命名空间或属性的字符串的表达式。
上下文命名空间必须已经创建，相关联的参数及其值也必须已设置。
命名空间必须是有效的标识符。参数名称可以是任何字符串。 它不区分大小写，但长度不能超过4000字节。
注意
LightDB提供以下内置命名空间：
USERENV - 描述当前会话。 命名空间USERENV的预定义参数列在表Table 9.105中。
SYS_SESSION_ROLES - 指示指定的角色当前是否为会话启用。
Table 9.105. 命名空间USERENV的预定义参数
|
参数 |
概述 |
|---|---|
|
CLIENT_IDENTIFIER |
数据库会话所使用的程序的名称 |
|
CLIENT_INFO |
数据库会话所使用的程序的名称 |
|
CLIENT_PROGRAM_NAME |
数据库会话所使用的程序的名称 |
|
CDB_NAME |
当前数据库 |
|
CON_ID |
始终为1 |
|
CON_NAME |
当前数据库 |
|
CURRENT_SCHEMA |
当前模式 |
|
CURRENT_SCHEMAID |
当前模式ID |
|
CURRENT_USER |
当前用户 |
|
CURRENT_USERID |
当前用户ID |
|
DATABASE_ROLE |
角色是以下之一：PRIMARY，PHYSICAL STANDBY |
|
DB_NAME |
当前数据库 |
|
DB_UNIQUE_NAME |
当前数据库 |
|
HOST |
连接客户端的主机名称 |
|
INSTANCE |
始终为1 |
|
INSTANCE_NAME |
始终为 'LightDB' |
|
IP_ADDRESS |
连接客户端的机器的IP地址 |
|
ISDBA |
如果用户已被认证为具有DBA特权，则返回TRUE |
|
LANG |
语言的缩写名称，比现有的 'LANGUAGE' 参数更简短 |
|
LANGUAGE |
您的会话当前使用的语言和区域设置，以及数据库字符集 |
|
MODULE |
数据库会话所使用的程序的名称 |
|
NETWORK_PROTOCOL |
用于通信的网络协议 |
|
NLS_DATE_FORMAT |
会话的日期格式 |
|
ORACLE_HOME |
数据主目录的完整路径名 |
|
PID |
当前进程ID |
|
SERVER_HOST |
listen_addresses |
|
SERVICE_NAME |
当前数据库 |
|
SESSION_USER |
当前用户 |
|
SESSION_USERID |
当前用户ID |
|
SESSIONID |
当前会话进程ID |
|
SID |
当前会话进程ID |
注意
如果使用 SYS_CONTEXT，则需要提前指定 search_path 为 "oracle"。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理”>“服务器配置”>“设置参数”。
示例
下面的语句返回登录到数据库的用户的名称：
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;
sys_context
-------------
lightdb
(1 row)
描述
返回有关当前会话的信息
语法
USERENV(parameter text) returns text
通用规则
这些信息对于编写特定于应用程序的审计跟踪表或确定当前会话使用的特定于语言的字符可能很有用。
表Table 9.105描述了参数参数的值。
注意
如果使用USERENV，则需要事先指定“oracle”作为search_path。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理”>“服务器配置”>“设置参数”。
示例
下面的示例返回当前会话的LANGUAGE参数：
SELECT USERENV('LANGUAGE') "Language" FROM DUAL;
Language
-------------
en_US.UTF-8
(1 row)
orafce支持以下日期类型的日期时间操作符。
Table 9.106. 日期时间操作符
| 操作 | 示例 | 结果 |
|---|---|---|
| + | DATE'2016/01/01' + 10 | 2016-01-11 00:00:00 |
| - | DATE'2016/03/20' - 35 | 2016-02-14 00:00:00 |
| - | DATE'2016/09/01' - DATE'2015/12/31' | 245 |
Note
如果使用orafce的DATE类型的日期时间操作符，则需要事先指定“oracle”作为search_path。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
oracle兼容模式支持以下JSON函数。
JSON_OBJECT
JSON_SERIALIZE
JSON_VALUE
JSON_TABLE
JSON_MERGEPATCH
描述
根据由VALUE关键字分隔的键和值列表，构建一个JSON对象。
语法
JSON_OBJECT(expr VALUE expr, ...) returns json
通用规则
返回值的数据类型为json。兼容varchar2。
注意
对JSON_OBJECT(expr VALUE expr, ...)函数调用，不能在函数前面加模式限定。
键值对数量不能超过100。
示例
在下面的示例中，将返回键值对构成的json文档。
SELECT JSON_OBJECT('name' VALUE 'John', 'age' VALUE 30) AS json_obj FROM dual;
json_obj
-------------------------------
{"name" : "John", "age" : 30}
(1 row)
描述
JSON数据作为输入并返回其文本表示。
语法
JSON_SERIALIZE(varchar2) returns json
通用规则
返回值的数据类型为json。兼容varchar2。
注意
JSON_SERIALIZE(varchar2)函数对输入JSON数据格式检查，如果格式有误将返回null。
示例
在下面的示例中，将JSON数据作为输入并返回其文本表示。
lightdb@oraddd=# SELECT JSON_SERIALIZE ('{"a": {"b":"foo"}}') as json_string FROM dual;
json_string
--------------------
{"a": {"b":"foo"}}
(1 row)
描述
SQL/JSON函数JSON_VALUE在JSON数据中查找指定的标量JSON值。
语法
JSON_VALUE(expr, path_expression) returns text
通用规则
对于expr，请指定一个计算结果为文本文字的表达式。
该函数使用路径表达式path_expression来评估expr，找到与路径表达式匹配或满足该路径表达式的标量JSON值。路径表达式必须是文本文字。
注意
对JSON_VALUE(expr, path_expression)函数调用，不能在函数前面加模式限定。
示例
在下面的示例中，在JSON数据中查找指定路径的标量JSON值。
select JSON_VALUE('{"key1":"v1","key2":"v2"}', '$.key1');
json_value
------------
v1
(1 row)
描述
SQL/JSON 函数JSON_TABLE创建JSON数据的关系视图。它将JSON数据评估的结果映射到关系行和列中。您可以使用SQL查询该函数返回的结果作为虚拟关系表。JSON_TABLE的主要目的是为JSON数组中的每个对象创建一行关系数据，并将该对象中的JSON值输出为单独的SQL列值。
语法
JSON_TABLE(expr, path_expression COLUMNS( column_name Typename [ FORMAT JSON ] PATH path_expression [, column_name [ FORMAT JSON ] PATH path_expression] ))
通用规则
该函数首先将路径表达式（称为SQL/JSON行路径表达式）应用于提供的JSON数据。与行路径表达式匹配的JSON值称为行源，因为它会生成一行关系数据。
COLUMNS子句评估行源，在行源中查找特定的JSON值，并将这些JSON值作为一行关系数据的各个列中的SQL值返回。
注意
您必须仅在SELECT语句的FROM子句中指定JSON_TABLE。
示例
在下面的示例中，查询JSON数据中的特定JSON值。
SELECT * FROM JSON_TABLE(
'[{"name":"John","age":30},{"name":"Jane","age":25}]',
'$[*]' COLUMNS (
name VARCHAR2(100) PATH '$.name',
age NUMBER PATH '$.age'
)
);
name | age
------+-----
John | 30
Jane | 25
(2 rows)
描述
JSON_MERGEPATCH是一种标准化功能，用于使用名为合并补丁的JSON文档修改目标JSON文档。该函数在RFC7396中进行了描述。
语法
JSON_MERGEPATCH(target_expr, patch_expr)
通用规则
target_expr 求值为JSON值目标文档。
patch_expr 求值为JSON值补丁文档。
注意
JSON_MERGEPATCH 根据目标文档求值补丁文档以生成结果文档。如果目标或补丁文档为NULL，则结果也为NULL。
示例
在下面的示例中，补丁文档合并到目标文档生成结果文档。
SELECT JSON_MERGEPATCH('{"name":"John","age":30}', '{"age":31,"city":"New York"}') AS json_merged FROM dual;
json_merged
-------------------------------------------------
{"age": 31, "city": "New York", "name": "John"}
(1 row)
以下函数用于其他目的：
EMPTY_CLOB
EMPTY_BLOB
ORA_HASH
VSIZE
DEPS_SAVE_AND_DROP_DEPENDENCIES/DEPS_RESTORE_DEPENDENCIES
PLVSTR.IS_PREFIX
PLVDATE.ISLEAPYEAR
TIMESTAMP_TO_SCN
描述
返回一个空的CLOB
语法
EMPTY_CLOB() RETURNS clob
通用规则
可以用来初始化一个 CLOB 变量，或者在 INSERT 或 UPDATE 语句中初始化 CLOB 列为 EMPTY. EMPTY 意味着该 CLOB 已被初始化但没有被赋值。
长度为 0 但不为 NULL.
注意
如果使用EMPTY_CLOB函数，则需要事先指定“oracle”作为search_path。
参见
有关如何编辑search_path的信息，请参阅“使用orafce的注意事项”。
有关如何设置服务器参数的信息，请参阅LightDB文档中的“服务器管理”>“服务器配置”>“设置参数”。
示例
select length(empty_clob()) FROM DUAL;
length
--------
0
(1 row)
select count(*) from dual where empty_clob() is null;
count
-------
0
(1 row)
create table foo (a int, b clob default empty_clob());
insert into foo(a) values(1);
insert into foo values (2, 'hello');
select count(*) from foo where b is not null;
count
-------
2
(1 row)
select count(*) from foo where length(b) > 0;
count
-------
1
(1 row)
描述
返回一个空的 BLOB
语法
EMPTY_BLOB() RETURNS blob
通用规则
可以用来初始化一个 BLOB 变量，或者在 INSERT 或 UPDATE 语句中初始化 BLOB 列为 EMPTY. EMPTY 意味着该 BLOB 已被初始化但没有被赋值。
长度为 0 但不为 NULL
样例
select length(empty_blob()) FROM DUAL;
length
--------
0
(1 row)
select count(*) from dual where empty_blob() is null;
count
-------
0
(1 row)
create table foo (a int, b blob default empty_blob());
insert into foo(a) values(1);
insert into foo values (2, to_blob('616263'));
select count(*) from foo where b is not null;
count
-------
2
(1 row)
select count(*) from foo where length(b) > 0;
count
-------
1
(1 row)
描述
ORA_HASH是一个计算给定表达式哈希值的函数。此函数可用于操作，例如分析数据子集和生成随机样本。
语法
ORA_HASH(p_data anyelement,p_buckets int4, p_seed int4) RETURNS int
通用规则
参数p_data确定要计算哈希值的数据。 参数expr通常表示列名，对于其长度没有限制。
可选参数p_buckets确定哈希函数返回的最大桶值。 您可以指定0到2147483647之间的任何值。默认值为0。
可选参数p_seed使LightDB能够为相同的数据集生成许多不同的结果。LightDB将哈希函数应用于expr和p_seed的组合。 您可以指定0到2147483647之间的任何值。默认值为0。
返回值的数据类型为int。
注意
如果使用ORA_HASH，则需要事先指定“oracle”作为search_path。
参见
请参考“使用orafce的注意事项”以获取如何编辑search_path的信息。
请参考LightDB文档中的“服务器管理” > “服务器配置” > “设置参数”章节，以获取如何设置服务器参数的信息。
示例
select ora_hash('abcdAbcdasd'::text) FROM DUAL;
ora_hash
-----------
303228277
(1 row)
描述
VSIZE函数返回expr内部表示的字节数。如果expr为null，则该函数返回null。
语法
VSIZE(anyelement) RETURNS integer
通用规则
pg_column_size函数与之相同。
注意
如果使用VSIZE函数，则需提前将search_path指定为“oracle”。
参见
请参考“使用orafce的注意事项”以获取如何编辑search_path的信息。
请参考LightDB文档中的“服务器管理” > “服务器配置” > “设置参数”章节，以获取如何设置服务器参数的信息。
示例
select vsize(123456789) FROM DUAL;
vsize
-------
4
(1 row)
描述
这些函数用于修改具有视图依赖关系的表。 递归备份所有依赖视图，然后修改基表，最后重新创建所有备份视图。
DEPS_SAVE_AND_DROP_DEPENDENCIES函数用于保存和删除表的依赖关系。
DEPS_RESTORE_DEPENDENCIES函数用于恢复表的依赖关系。
语法
DEPS_SAVE_AND_DROP_DEPENDENCIES(name, name default current_schema()::name, jsonb default '{}'::jsonb) RETURNS void
DEPS_RESTORE_DEPENDENCIES(name, name default current_schema()::name, jsonb default '{}'::jsonb)
通用规则
在lt_catalog下创建。
依赖关系保存在lt_catalog.deps_saved_ddl表中。
dry_run参数用于不实际删除依赖关系运行函数，默认值为false。
verbose参数用于显示调试日志，默认值为false。
populate_materialized_view参数用于启用或禁用使用WITH [NO] DATA标志刷新创建的物化视图，默认值为false。
注意
如果使用DEPS_SAVE_AND_DROP_DEPENDENCIES/DEPS_RESTORE_DEPENDENCIES函数，则无需提前将search_path指定为“oracle”。
示例
create table test_t(key1 int);
create view test_t_v as select * from test_t;
begin;
select deps_save_and_drop_dependencies('test_t');
alter table test_t modify key1 number;
select * from deps_restore_dependencies('test_t');
commit;
描述
PLVSTR.IS_PREFIX会在一个字符串或数字是另一个字符串或数字的前缀时返回true。
语法
PLVSTR.IS_PREFIX(p bigint, prefix bigint) RETURNS boolean
PLVSTR.IS_PREFIX(p integer, prefix integer) RETURNS boolean
PLVSTR.IS_PREFIX(p text, prefix text) RETURNS boolean
通用规则
这个功能在测试字符串或数字是否以某些内容开头时非常有用。
例子
select plvstr.is_prefix(111, 11) FROM DUAL;
is_prefix
-----------
t
(1 row)
select plvstr.is_prefix('abc', 'AB') from dual;
is_prefix
-----------
f
(1 row)
描述
判断某个年份是否是闰年
语法
PLVDATE.ISLEAPYEAR(date) RETURNS boolean
示例
select plvdate.isleapyear(date '2023-01-01') FROM DUAL;
isleapyear
-----------
f
select plvdate.isleapyear(date '2020-01-01') from dual;
isleapyear
-----------
t
描述
timestamp_to_scn函数返回一个事务 ID, 该 ID 对应的事务提交时间戳小于或等于给定的时间戳。
语法
TIMESTAMP_TO_SCN(timestamptz) RETURNS bigint
样例
set orafce.timezone = 'Asia/Shanghai';
select timestamp_to_scn(sysdate) FROM DUAL;
timestamp_to_scn
-----------
12345