其他函数
TABLE()
支持Oracle的表函数功能，表函数可接受查询语句或游标作为输入参数，并可输出多行数据。该函数可以平行执行，并可持续输出数据流，被称作管道式输出。应用表函数可将数据转换分阶段处理，并省去中间结果的存储和缓冲表。
vastbase=# select * from table(generate_series(1,10)); generate_series ----------------- 1 2 3 4 5 6 7 8 9 10 (10 rows)lnnvl
lnnvl用于某个语句的where子句中的条件，如果条件为true就返回false；如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND,OR, or BETWEEN中。
vastbase=# SELECT empno,ename,sal,comm FROM emp e WHERE LNNVL(e.comm <1000); empno | ename | sal | comm -------+--------+---------+--------- 7369 | SMITH | 800.00 | 7566 | JONES | 2975.00 | 7654 | MARTIN | 1250.00 | 1400.00 7698 | BLAKE | 2850.00 | 7782 | CLARK | 2450.00 | 7839 | KING | 5000.00 | 7900 | JAMES | 950.00 | 7902 | FORD | 3000.00 | 7934 | MILLER | 1300.00 | (9 rows)bitand(number1,number2)
返回number1和number2按位与的值，返回值为BIT类型。
vastbase=# select bitand(11,2); bitand -------- 2 (1 row)empty_blob()
返回一个空BLOB类型。
vastbase=# select empty_blob() ; empty_blob ------------ (1 row)REGEXP_COUNT(source_char, pattern [, position [,match_param]])
REGEXP_COUNT 返回pattern 在source_char串中出现的次数。如果未找到匹配，则函数返回0。
match_param 变量支持下面几个值：- 'i'用于不区分大小写的匹配
- 'c'用于区分大小写的匹配
- 'n'允许句点(.)作为通配符去匹配换行符。如果省略该参数，则句点将不匹配换行符
- 'm'将源串视为多行。即Oracle将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作一行。
'x'忽略空格字符。默认情况下，空格字符与自身相匹配。
vastbase=#select REGEXP_COUNT('GEORGE','GE',1,'i') from DUAL; regexp_count -------------- 2 (1 row)
- 'i'用于不区分大小写的匹配
CAST（element as data_type）
CAST将特定数据类型或集合类型的值转换为另一种数据类型或集合类型的值。
vastbase=# select cast('123' AS VARCHAR2(3)); varchar --------- 123 (1 row)COALESCE
COALESCE返回表达式列表中的第一个非空表达式。
vastbase=# select COALESCE (null,'',3) from dual; coalesce ---------- 3 (1 row)replace
replace将出现search_string并替换为replace_string的情况下返回char。
vastbase=# SELECT REPLACE(123123123,'1','123213') "Changes"; Changes -------------------------- 123213231232132312321323 (1 row)sys_connect_by_path
SYS_CONNECT_BY_PATH仅在分层查询中有效。它返回从根到节点的列值的路径，对于CONNECT BY条件返回的每一行，列值用char分隔。
vastbase=# CREATE TABLE sys_cbp_test2 (id INTEGER NOT NULL PRIMARY KEY,parent_id INTEGER ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sys_cbp_test2_pkey" for table "sys_cbp_test2" CREATE TABLE vastbase=# INSERT INTO sys_cbp_test2 vastbase-# SELECT 1, NULL FROM dual vastbase-# UNION ALL SELECT 2, 1 FROM dual vastbase-# UNION ALL SELECT 3, 2 FROM dual vastbase-# UNION ALL SELECT 4, 3 FROM dual vastbase-# UNION ALL SELECT 5, 1 FROM dual vastbase-# UNION ALL SELECT 6, 5 FROM dual vastbase-# UNION ALL SELECT 7, 2 FROM dual vastbase-# UNION ALL SELECT 20, NULL FROM dual vastbase-# UNION ALL SELECT 21, 20 FROM dual vastbase-# UNION ALL SELECT 22, 21 FROM dual; INSERT 0 10 vastbase=# SELECT id, parent_id, Level a, sys_connect_by_path (TO_CHAR (id), '/') AS Path vastbase-# FROM sys_cbp_test2 vastbase-# START WITH parent_id IS NULL CONNECT BY prior id = parent_id; id | parent_id | a | path ----+-----------+---+----------- 1 | | 1 | /1 20 | | 1 | /20 2 | 1 | 2 | /1/2 5 | 1 | 2 | /1/5 21 | 20 | 2 | /20/21 3 | 2 | 3 | /1/2/3 6 | 5 | 3 | /1/5/6 7 | 2 | 3 | /1/2/7 22 | 21 | 3 | /20/21/22 4 | 3 | 4 | /1/2/3/4 (10 rows)instrb
instrb函数用于搜索一个字符串在指定的字符串中出现的位置，起始搜索位置和出现的次数（第几次）可通过参数来指定。当起始搜索位置为负数时，表示从指定字符串的尾部往回开始搜索。返回值为字符串在被搜索的字符串中出现的位置，该位置是按字节数来计算。如果未搜索到字符串，则返回0。
vastbase=# select instrb('CORPORATE FLOOR','OR'); instrb -------- 2 (1 row) vastbase=# SELECT instrb('CORPORATE FLOOR','OR',5,2); instrb -------- 14 (1 row) vastbase=# SELECT instrb('CORPORATE FLOOR','OR',-3,2); instrb -------- 2 (1 row) vastbase=# SELECT instrb('测试 CORPORATE FLOOR','OR'); instrb -------- 9 (1 row)to_multi_byte
to_multi_byte函数可以实现字符半角转全角功能，即将输入的字符或字符串从单字节转换成多字节。
vastbase=# SELECT to_multi_byte('123$test'); to_multi_byte ------------------ １２３＄ｔｅｓｔ (1 row)to_single_byte
to_single_byte函数可以实现从全角到半角的转换，即将输入的字符或字符串从多字节转换成单字节。
vastbase=# SELECT to_single_byte('1 2 3 $ t e s t'); to_single_byte ----------------- 1 2 3 $ t e s t (1 row)tz_offset
tz_offset函数根据输入返回相对UTC的时区偏移量。tz_offset函数根据输入返回相对UTC的时区偏移量。输入时区名时，返回正值表示格林威治东部，反之为负值，若是0时区，则输出+00:00，时区偏移量精确到分钟；若是输入{ + | - }hh:mi，则原样输出，hh范围：0-15，mi范围：0-59。若输入SESSIONTIMEZONE/DBTMEZONE，SESSIONTIMEZONE/DBTMEZONE是返回时区名的宏变量，作为tz_offset参数，与输入时区名无异。
vastbase=# create table shopping(u_id int,goods text,num int); CREATE TABLE vastbase=# select tz_offset('PRC'); tz_offset ----------- +08:00 (1 row) vastbase=# select tz_offset('GMT'); tz_offset ----------- +00:00 (1 row) vastbase=# select tz_offset('America/Lima'); tz_offset ----------- -05:00 (1 row) vastbase=# select tz_offset('NZ-CHAT'); tz_offset ----------- +12:45 (1 row) vastbase=# select tz_offset('+12:12'); tz_offset ----------- +12:12 (1 row) vastbase=# select tz_offset(SESSIONTIMEZONE); tz_offset ----------- +08:00 (1 row)vsize
兼容Oracle的VSIZE函数返回表达式的真实存储长度，以字节表示。
如果expr为null，函数返回null。
expr表达式，可以为数值类型、字符类型、日期时间类型。drop table if exists t_col1; create table t_col1( id int primary key, a TINYINT, b SMALLINT, c INTEGER, d BIGINT, e BINARY_INTEGER ); vastbase=# insert into t_col1(id,a,b,c,d,e) values (1,1,1,1,1,1); INSERT 0 1 vastbase=# insert into t_col1(id,a,b,c,d,e) values (2,22,22,22,22,22); INSERT 0 1 vastbase=# select t1.*, vsize(a) ,vsize(b) ,vsize(c) ,vsize(d) ,vsize(e) from t_col1 t1; id | a | b | c | d | e | vsize | vsize | vsize | vsize | vsize ----+----+----+----+----+----+-------+-------+-------+-------+------- 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 4 | 8 | 4 2 | 22 | 22 | 22 | 22 | 22 | 1 | 2 | 4 | 8 | 4 (2 rows) vastbase=# select t1.*, vsize(a) ,vsize(b) ,vsize(c) ,vsize(d) ,vsize(e) from t_col1 t1 order by vsize(a) ; id | a | b | c | d | e | vsize | vsize | vsize | vsize | vsize ----+----+----+----+----+----+-------+-------+-------+-------+------- 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 4 | 8 | 4 2 | 22 | 22 | 22 | 22 | 22 | 1 | 2 | 4 | 8 | 4 (2 rows)wm_concat
WM_CONCAT函数可以实现行转列功能，即将查询出的某一列的值使用逗号进行隔开拼接，使之成为一条数据，实现字段合并。
vastbase=# create table shopping(u_id int,goods text,num int); CREATE TABLE vastbase=# insert into shopping VALUES(1,'Apple',2),(1,'Melon',4),(1,'Orange',3); INSERT 0 3 vastbase=# insert into shopping VALUES(2,'Pear',5); INSERT 0 1 vastbase=# insert into shopping VALUES(3,'Grape',1),(3,'Banana',1); INSERT 0 2 vastbase=# select u_id,WM_CONCAT(goods) as goods_sum FROM shopping GROUP BY u_id; u_id | goods_sum ------+-------------------- 1 | Apple,Melon,Orange 3 | Grape,Banana 2 | Pear (3 rows) vastbase=# SELECT u_id,WM_CONCAT(goods||'('||num||')') as goods_sum FROM shopping GROUP BY u_id; u_id | goods_sum ------+----------------------------- 1 | Apple(2),Melon(4),Orange(3) 3 | Grape(1),Banana(1) 2 | Pear(5) (3 rows)sys_guid
SYS_GUID可以生成一个全局唯一的字符串数据值。生成的依据主要是时间和随机数，具有全局唯一性。
vastbase=# SELECT * FROM sys_guid(); sys_guid -------------------------------------- c3515960-5183-b252-cc4e-cf4fef8a916a (1 row)ROUND(date/timestamp)
使ROUND函数支持对日期与时间的处理。语法：
ROUND(date [, fmt ])
ROUND(timestamp [, fmt ])
用于返回日期或时间四舍五入到格式模型fmt指定的单位。如果省略fmt，则将日期或时间四舍五入到最近的日期，相当于设置了fmt为dd。--date select round('2020-11-16'::date, 'yy'); select round('2020-05-13'::date, 'mm'); select round('2020-11-16'::date, 'dd'); select round('2020-05-13'::date); --timestamp --fmt最小可指定到分钟(mi) select round('2020-11-16 13:40:31+08'::timestamp with time zone, 'yy'); select round('2020-05-13 10:29:15+08'::timestamp with time zone, 'mi'); select round('2020-05-13 10:29:15+08'::timestamp with time zone); select round('2020-11-16 13:40:31+08'::timestamp without time zone, 'mm'); select round('2020-05-13 10:29:15+08'::timestamp without time zone, 'dd'); select round('2020-05-13 10:29:15+08'::timestamp without time zone, 'hh'); select round('2020-11-16 13:40:31+08'::timestamp without time zone); vastbase=# select round('2020-11-16'::date, 'yy'); round --------------------- 2021-01-01 00:00:00 (1 row) vastbase=# select round('2020-05-13'::date, 'mm'); round --------------------- 2020-05-01 00:00:00 (1 row) vastbase=# select round('2020-11-16'::date, 'dd'); round --------------------- 2020-11-16 00:00:00 (1 row) vastbase=# select round('2020-05-13'::date); round --------------------- 2020-05-13 00:00:00 (1 row) vastbase=# select round('2020-11-16 13:40:31+08'::timestamp with time zone, 'yy'); round ------------------------ 2021-01-01 00:00:00+08 (1 row) vastbase=# select round('2020-05-13 10:29:15+08'::timestamp with time zone, 'mi'); round ------------------------ 2020-05-13 10:29:00+08 (1 row) vastbase=# select round('2020-05-13 10:29:15+08'::timestamp with time zone); round ------------------------ 2020-05-13 00:00:00+08 (1 row) vastbase=# select round('2020-11-16 13:40:31+08'::timestamp without time zone, 'mm'); round --------------------- 2020-12-01 00:00:00 (1 row) vastbase=# select round('2020-05-13 10:29:15+08'::timestamp without time zone, 'dd'); round --------------------- 2020-05-13 00:00:00 (1 row) vastbase=# select round('2020-05-13 10:29:15+08'::timestamp without time zone, 'hh'); round --------------------- 2020-05-13 10:00:00 (1 row) vastbase=# select round('2020-11-16 13:40:31+08'::timestamp without time zone); round --------------------- 2020-11-17 00:00:00 (1 row)
