内置数据类型与内置函数
1. 内置数据类型
char |
varchar |
varchar2 |
number |
binary_float |
binary_double |
date |
timestamp |
timestamp with time zone |
timestamp with local time zone |
interval year to month |
interval day to second |
raw |
long |
2. 内置函数类型
sysdate |
systimestamp |
add_months |
last_day |
next_day |
months_between |
current_date |
current_timestamp |
new_time |
tz_offset |
trunc |
instrb |
substr |
substrb |
trim |
ltrim |
rtrim |
length |
lengthb |
rawtohex |
replace |
regexp_replace |
regexp_substr |
regexp_instr |
regexp_like |
to_number |
to_char |
to_date |
to_timestamp |
to_timestamp_tz |
to_yminterval |
to_dsinterval |
numtodsinterval |
numtoyminterval |
localtimestamp |
from_tz |
sys_extract_utc |
sessiontimezone |
hextoraw |
uid |
USERENV |
asciistr |
to_multi_byte |
to_single_byte |
compose |
decompose |
3. 内置函数说明
1、兼容sysdate函数,功能:查看对应的日期与时间,测试用例如下: 查询当前系统的日期:
select sysdate() from dual;
sysdate
------------
2023-07-06
(1 row)
查询往前推1天的日期:
select sysdate()-1 from dual;
?column?
------------
2023-07-05
(1 row)
2、兼容systimestamp函数,功能:返回本机数据库上当前系统日期和时间(包括微秒和时区),测试用例如下: 查询当前日期的日期和时间:
select systimestamp() from dual;
systimestamp
-----------------------------------
2023-07-06 10:18:31.674322 +08:00
(1 row)
3、兼容add_months函数,功能:函数将一个月数(n)添加一个日期,并返回相隔n月的同一天,支持参数:date, number;测试用例如下: 查询当前日期(七月六日)的下个月的同一天:
select add_months(sysdate(),1) from dual;
add_months
------------
2023-08-06
(1 row)
查询当前日期的上个月的同一天:
select add_months(sysdate(),-1) from dual;
add_months
------------
2023-06-06
(1 row)
4、兼容last_day函数,功能:返回指定日期所在月份的最后一天,支持参数:date,测试用例如下: 查询当天所在月份的最后一天:
select last_day(sysdate())from dual;
last_day
------------
2023-07-31
(1 row)
查询某一天所在月份的最后一天:
select last_day(to_date('2019-09-01'))from dual;
last_day
------------
2019-09-30
(1 row)
5、兼容next_day函数,功能:返回指定日期的下一个日期。支持参数:date, integer /date ,text, 说明:当函数中第二个参数传的星期数比现有星期数小时,会返回下一个星期的日期;当函数中第二个参数所传的日期比现有星期数大,会返回本周相应星期日期。测试用例如下: 查询当前日期的下一天:
select next_day(sysdate(),1) from dual;
next_day
------------
2023-07-07
(1 row)
查询当前日期的下个星期五:
select next_day(sysdate(),'FRIDAY') from dual;
next_day
------------
2023-07-07
(1 row)
6、兼容months_between函数,功能:返回日期类型的date1和date2之间相差的月份,支持参数:date,date,说明:如果date1晚于date2,返回正数;如果date1早于date2返回负数;如果date1和date2是某月里的同一天,返回结果为整数;如果不是同一天,会在每月31天的基础上返回带有小数部分的结果。测试用例如下: 查询不同月份同一天之间相差的月份:
select months_between(to_date('2023-07-06'),to_date('2023-08-06')) from dual;
months_between
----------------
-1
(1 row)
查询不同月份不同日期之间相差的月份:
select months_between(to_date('2023-07-06'),to_date('2023-08-05')) from dual;
months_between
--------------------
-0.967741935483871
(1 row)
7、兼容current_date函数,功能:返回当前时区的当前日期,测试用例如下: 查询当前时区的当前日期:
select current_date from dual;
current_date
--------------
2023-07-06
(1 row)
8、兼容current_timestamp函数,功能:返回当前时区的当前日期与当前时间,包含当前时区信息。支持参数:integer, 说明:返回的时间可调整精度。测试用例如下: 查询当前时区的当前日期与时间:
select current_timestamp from dual;
current_timestamp
-----------------------------------
2023-07-06 10:27:01.440600 +08:00
(1 row)
查询当前时区的当前日期与时间(精度调整为前三位小数):
select current_timestamp(3) from dual;
current_timestamp
-----------------------------------
2023-07-06 10:27:14.182000 +08:00
(1 row)
9、兼容new_time函数,功能:返回某个时间在某时区所对应的在另一个时区的日期,支持参数:date, text, text ,测试用例如下: 返回当前日期在另一个时区对应的日期:
select sysdate() bj_time,new_time(sysdate(),'PDT','GMT')los_angles from dual;
bj_time | los_angles
------------+------------
2023-07-06 | 2023-07-06
(1 row)
10、兼容tz_offset函数,功能:返回给定时区与标准时区的偏移量,支持参数:text,测试用例如下: 返回给定时区与标准时区偏移量:
select tz_offset('US/Eastern') from dual;
tz_offset
-----------
-04:00
(1 row)
11、兼容trunc函数,功能:可以截取日期,得到想要的数值,如年,月,日,时,分,支持参数:date/date,text,测试用例如下: 截取当前日期:
select trunc(sysdate()) from dual;
trunc
------------
2023-07-06
(1 row)
截取年,返回值只有年是正确的,月和日不是准确值:
select trunc(sysdate(),'yyyy') from dual;
trunc
------------
2023-01-01
(1 row)
截取月,返回值只有月是正确的,年和日不是准确值:
select trunc(sysdate(),'mm') from dual;
trunc
------------
2023-07-01
(1 row)
12、兼容instrb函数,功能:字符串查找函数,返回字符串的位置,支持参数: varchar2, text, number DEFAULT 1, number DEFAULT 1,以下为测试用例: 返回CORPORATE FLOOR中默认第一次出现OR时字符串的位置:
SELECT INSTRB('CORPORATE FLOOR','OR') "Instring in bytes" FROM DUAL;
Instring in bytes
-------------------
2
(1 row)
返回corporate floor中从第五个字符开始查询,第二次出现or时字符串的位置:
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL;
Instring in bytes
-------------------
14
(1 row)
13、兼容substr函数,功能:截取字符串函数,以字符为单位截取,支持参数:text, integer, 测试用例如下: 截取’今天天气很好’中从第五个字符开始,往后的字符串:
SELECT SUBSTR('今天天气很好',5) "Substring with bytes" FROM DUAL;
Substring with bytes
----------------------
很好
(1 row)
14、兼容substrb函数,功能:截取字符串函数,以字节为单位截取,支持参数:varchar2, number/varchar2, number,number,测试用例如下: 截取’今天天气很好’中从第五个字节开始,往后的字符串:
SELECT SUBSTRB('今天天气很好',5) "Substring with bytes" FROM DUAL;
Substring with bytes
----------------------
天气很好
(1 row)
截取’今天天气很好’中从第五个字节开始,第八个字节结束的字符串:
SELECT SUBSTRB('今天天气很好',5,8) "Substring with bytes" FROM DUAL;
Substring with bytes
----------------------
天气
(1 row)
15、兼容trim函数,功能:去除指定字符串的左右空格或对应数据,支持参数:varchar2 /varchar2,varchar2,测试用例如下: 去除' aaa bbb ccc '的左右空格:
select trim(' aaa bbb ccc ')trim from dual;
trim
-------------
aaa bbb ccc
(1 row)
去除’aaa bbb ccc’中的aaa:
select trim('aaa bbb ccc','aaa')trim from dual;
trim
----------
bbb ccc
(1 row)
16、兼容ltrim函数,功能:去除指定字符串的左侧空格或对应数据,支持参数:varchar2 /varchar2,varchar2,测试用例如下: 去除' abcdefg '的左侧空格:
select ltrim(' abcdefg ')ltrim from dual;
ltrim
------------
abcdefg
(1 row)
从’abcdefg’左侧开始遍历,一旦存在某字符出现在’fegab’中就去除,不存在则返回结果:
select ltrim('abcdefg','fegab')ltrim from dual;
ltrim
-------
cdefg
(1 row)
17、兼容rtrim函数,功能:去除指定字符串的右侧空格,测试用例如下: 去除' abcdefg '的右侧空格:
select rtrim(' abcdefg ')rtrim from dual;
rtrim
----------------
abcdefg
(1 row)
从’abcdefg’右侧开始遍历,一旦存在某字符出现在’fegab’中就去除,不存在则返回结果:
select rtrim('abcdefg','fegab')rtrim from dual;
rtrim
-------
abcd
(1 row)
18、兼容length函数,功能:求取指定字符串字符的长度,支持参数:char/integer/varchar2测试用例如下: 查询223的字符长度:
select length(223) from dual;
length
--------
3
(1 row)
查询'223’的字符长度:
select length('223') from dual;
length
--------
3
(1 row)
查询’ivorysql数据库’的字符长度:
select length('ivorysql数据库') from dual;
length
--------
11
(1 row)
19、兼容lengthb功能:求取指定字符串字节的长度,支持参数:char/bytea/varchar2测试用例如下: 查询’ivorysql’的字节长度:
select lengthb('ivorysq'::char) from dual;
lengthb
---------
1
(1 row)
查询'0x2C’的字节长度:
select lengthb('0x2C'::bytea) from dual;
lengthb
---------
4
(1 row)
查询’ivorysql数据库’的字节长度:
select lengthb('ivorysql数据库'::varchar2) from dual;
lengthb
---------
17
(1 row)
20、兼容replace函数,功能:替换指定字符串中的字符或删除字符,支持参数:text, text, text/varchar2, varchar2, varchar2 DEFAULT NULL::varchar2, 测试用例如下: 替换’jack and jue’中的’j’为’bl':
select replace('jack and jue','j','bl') from dual;
replace
----------------
black and blue
(1 row)
删除’jack and jue’中的’j':
select replace('jack and jue','j') from dual;
replace
------------
ack and ue
(1 row)
21、兼容regexp_replace函数,此函数为replace函数的扩展。功能:用于通过正则表达式来进行匹配替换。支持参数:text, text, text /text, text, text, integer/varchar2, varchar2/varchar2, varchar2 varchar2, 测试用例如下: 将匹配到的数字替换为*#:
select regexp_replace('01234abcd56789','[0-9]','*#')from dual;
regexp_replace
--------------------------
*#*#*#*#*#abcd*#*#*#*#*#
(1 row)
从第二个数开始将匹配到的数字替换为*#:
select regexp_replace('01234abcd56789','[0-9]','*#',2)from dual;
regexp_replace
-------------------------
0*#*#*#*#abcd*#*#*#*#*#
删除'01234abcd56789’中的'01':
select regexp_replace('01234abcd56789','01')from dual;
regexp_replace
----------------
234abcd56789
(1 row)
用’xxx’替换01234abcd56789’中的012:
select regexp_replace('01234abcd56789','012','xxx')from dual;
regexp_replace
----------------
xxx34abcd56789
(1 row)
22、兼容regexp_substr函数,功能:拾取合符正则表达式描述的字符子串,支持参数:text, text,integer /text, text, integer, integer/ text, text, integer, integer, text /varchar2 ,varchar2,测试用例如下: 查询'012ab34’中从第一个数开始的012字串:
select regexp_substr('012ab34', '012',1) from dual;
regexp_substr
---------------
012
(1 row)
查询'012ab34’中从第一个数第一组开始的012字串:
select regexp_substr('012ab34', '012',1,1) from dual;
regexp_substr
---------------
012
(1 row)
查询'012a012Ab34’中从第一个数第一组开始不区分大小写的012字串:
select regexp_substr('012a012Ab34', '012A',1,1,'i') from dual;
regexp_substr
---------------
012a
(1 row)
查询'012a012Ab34’中从第一个数第一组开始区分大小写的012字串:
select regexp_substr('012a012Ab34', '012A',1,1,'c') from dual;
regexp_substr
---------------
012A
(1 row)
查询’数据库’中 '数据’子串:
select regexp_substr('数据库', '数据') from dual;
regexp_substr
---------------
数据
(1 row)
23、兼容regexp_instr函数,功能:用于标定符合正则表达式的字符子串的开始位置,支持参数:text, text,integer /text, text, integer, integer/ text, text, integer, integer, text/text, text, integer, integer, text, integer/ varchar2, varchar2,测试用例如下: 查询’abcaBcabc’中从第一个字符开始,出现abc子串的位置:
SELECT regexp_instr('abcaBcabc', 'abc', 1);
regexp_instr
--------------
1
(1 row)
查询’abcaBcabc’中从第一个字符开始,第三次出现abc子串的位置:
SELECT regexp_instr('abcaBcabc', 'abc', 1, 3);
regexp_instr
--------------
7
(1 row)
查询’abcabcabc’中从第一个字符开始,第二次出现abc子串后发生的位置:
SELECT regexp_instr('abcaBcabc', 'abc', 1, 2,1);
regexp_instr
--------------
7
(1 row)
查询’abcaBcabc’中从第一个字符开始,第一次出现abc子串后发生的位置(区分大小写):
SELECT regexp_instr('abcaBcabc', 'abc',1,2,1,'c');
regexp_instr
--------------
7
(1 row)
查询’数据库’中’库’出现的位置:
SELECT regexp_instr('数据库', '库');
regexp_instr
--------------
3
(1 row)
24、兼容regexp_like函数,功能:与like类似,用于模糊查询。支持参数:varchar2, varchar2 /varchar2, varchar2 varchar2, 首先创建一个regexp_like表用于测试用例查询:
create table t_regexp_like
(
id varchar(4),
value varchar(10)
);
insert into t_regexp_like values ('1','1234560');
insert into t_regexp_like values ('2','1234560');
insert into t_regexp_like values ('3','1b3b560');
insert into t_regexp_like values ('4','abc');
insert into t_regexp_like values ('5','abcde');
insert into t_regexp_like values ('6','ADREasx');
insert into t_regexp_like values ('7','123 45');
insert into t_regexp_like values ('8','adc de');
insert into t_regexp_like values ('9','adc,.de');
insert into t_regexp_like values ('10','abcbvbnb');
insert into t_regexp_like values ('11','11114560');
测试用例如下: 查询t_regexp_like表中带有abc的列:
select * from t_regexp_like where regexp_like(value,'abc');
id | value
----+----------
4 | abc
5 | abcde
10 | abcbvbnb
(3 rows)
查询t_regexp_like表中带有ABC的列(不区分大小写):
select * from t_regexp_like where regexp_like(value,'ABC','i');
id | value
----+----------
4 | abc
5 | abcde
10 | abcbvbnb
(3 rows)
25、兼容to_number函数,功能:是将一些处理过的按一定格式编排过的字符串变回数值型的格式,支持参数:text/text,text测试用例如下: 将字符串'-34,338,492’转换为数值型格式:
SELECT to_number('34,338,492', '99,999,999') from dual;
to_number
-----------
-34338492
(1 row)
将字符串'5.01-'转换为数值型格式:
SELECT to_number('5.01-', '9.99S');
to_number
-----------
-5.01
(1 row)
26、兼容to_char函数,功能:将数字或日期转换为字符类型,支持参数:date/date,text/timestamp/timestamp,text测试用例如下: 将当前系统日期转换为字符格式:
select to_char(sysdate()) from dual;
to_char
------------
2023-07-10
(1 row)
将当前系统日期转换为月份/日期/年字符格式:
select to_char(sysdate(),'mm/dd/yyyy') from dual;
to_char
------------
07/10/2023
(1 row)
将当前日期的timestamp格式转换为字符格式:
SELECT to_char(sysdate()::timestamp);
to_char
----------------------------
2023-07-10 09:46:44.000000
将当前日期的timestamp格式转换为月份/日期/年字符格式:
SELECT to_char(sysdate()::timestamp,'MM-YYYY-DD');
to_char
------------
07-2023-10
(1 row)
27、兼容to_date函数,功能:将字符类型转换为日期类型,支持参数:text/text,text测试用例如下: 将'2023/07/06’转换为日期类型:
select to_date('20230706') from dual;
to_date
------------
2023-07-06
(1 row)
将'-44-02-01’转换为日期类型:
SELECT to_date('-44,0201','YYYY-MM-DD');
to_date
------------
0044-02-01
(1 row)
28、兼容to_timestamp函数,功能:可以存储年、月、日、小时、分钟、秒,同时还可以存储秒的小数部分。支持参数:text/text,text测试用例如下: 查询'2018-11-02 12:34:56.025’以日期形式输出:
SELECT to_timestamp('20181102.12.34.56.025');
to_timestamp
----------------------------
2018-11-02 12:34:56.025000
(1 row)
查询’2011,12,18 11:38’以日期形式输出:
SELECT to_timestamp('2011,12,18 11:38 ', 'YYYY-MM-DD HH24:MI:SS');
to_timestamp
----------------------------
2011-12-18 11:38:00.000000
(1 row)
29、兼容to_timestamp_tz函数,功能:根据时间查询,时间字符串有T,Z并有毫秒,时区。测试用例如下: 查询'2016-10-9 14:10:10.123000’以日期形式输出:
SELECT to_timestamp_tz('2016-10-9 14:10:10.123000') FROM DUAL;
to_timestamp_tz
-----------------------------------
2016-10-09 14:10:10.123000 +08:00
(1 row)
查询'10-9-2016 14:10:10.123000 +8:30’以日期形式输出:
SELECT to_timestamp_tz('10-9-2016 14:10:10.123000 +8:30', 'DD-MM-YYYY HH24:MI:SS.FF TZH:TZM') FROM DUAL;
to_timestamp_tz
-----------------------------------
2016-09-10 13:40:10.123000 +08:00
(1 row)
30、兼容to_yminterval函数,功能:将一个字符串类型转化为年和月的时间差类型,支持参数:text, 测试用例如下: 查询'20110101’以后两个年零八个月后的日期:
select to_date('20110101','yyyymmdd')+to_yminterval('02-08') from dual;
?column?
------------
2013-09-01
(1 row)
31、兼容to_dsinterval函数,功能:将一个日期加上一定的小时或者天数变成另外一个日期,支持参数:text,测试用例如下: 查询当前系统时间加上9个半小时后的日期(当前为2023-07-06,18:00):
select sysdate()+to_dsinterval('0 09:30:00')as newdate from dual;
newdate
------------
2023-07-07
(1 row)
32、兼容numtodsinterval函数,功能:将数字转换成时间间隔类型的数据。支持参数:double precision, text测试用例如下: 转换100.00个小时为时间间隔类型数据:
SELECT NUMTODSINTERVAL(100.00, 'hour');
numtodsinterval
-------------------------------
+000000004 04:00:00.000000000
(1 row)
转换100分钟为时间间隔类型数据:
SELECT NUMTODSINTERVAL(100, 'minute');
numtodsinterval
-------------------------------
+000000000 01:40:00.000000000
(1 row)
33、兼容numtoyminterval函数,功能:将数字转换成日期间隔类型的数据。 支持参数:double precision,text,测试用例如下: 转换1.00,year为日期间隔:
SELECT NUMTOYMINTERVAL(1.00,'year');
numtoyminterval
-----------------
+000000001-00
(1 row)
转换1,mouth为日期间隔:
SELECT NUMTOYMINTERVAL(1,'month');
numtoyminterval
-----------------
+000000000-01
(1 row)
34、兼容localtimestamp函数,功能:返回会话中的日期和时间,支持参数:integer, 函数中增加参数为精度,测试用例如下: 返回当前会话中的日期和时间:
select localtimestamp from dual;
localtimestamp
----------------------------
2023-07-07 09:18:15.896472
(1 row)
返回当前会话中的日期和时间(精度为1):
select localtimestamp(1) from dual;
localtimestamp
----------------------------
2023-07-07 09:18:16.100000
(1 row)
35、兼容from_tz函数,功能:将时间从一个时区转换为另一个时区,支持参数;timestamp, text ,测试用例如下: 将'2000-03-28 08:00:00', '3:00’转换为当前时区:
SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') FROM DUAL;
from_tz
-----------------------------------
2000-03-28 13:00:00.000000 +08:00
(1 row)
36、兼容sys_extract_utc函数,功能:将一个timestamptz转换为UTC时区时间。支持参数:timestamp with time zone 测试用例如下: 查询转换timestamp '2000-03-28 11:30:00.00 -8:00’为UTC时区后的时间:
select sys_extract_utc(timestamp '2000-03-28 11:30:00.00 -8:00') from dual;
sys_extract_utc
----------------------------
2000-03-28 19:30:00.000000
(1 row)
37、兼容sessiontimezone函数,功能:查看时区详细信息,测试用例如下: 查看当前时区的详细信息:
select sessiontimezone() from dual;
sessiontimezone
-----------------
Asia/Shanghai
(1 row)
修改timezone后,查看时区相信信息:
set timezone = 'Asia/Hong_Kong';
SET
select sessiontimezone() from dual;
sessiontimezone
-----------------
Asia/Hong_Kong
(1 row)
38、兼容hextoraw函数,功能:将字符串表示的二进制数值转换为一个raw数值。支持参数:text,测试用例如下: 将字符串’abcdef’转换为raw数值:
select hextoraw('abcdef')from dual;
hextoraw
----------
\xabcdef
(1 row)
39、兼容uid函数,功能:获取数据库的实例名。测试用例如下: 获取当前数据库的实例名:
select uid() from dual;
uid
-----
10
(1 row)
40、兼容USERENV函数,功能:返回当前用户环境的信息,测试用例如下: 查看当前用户是否是dba,如果是返回ture:
select userenv('isdba')from dual;
get_isdba
-----------
TRUE
(1 row)
查看会话标志:
select userenv('sessionid')from dual;
get_sessionid
---------------
1
(1 row)
41、兼容ASCIISTR函数,功能:传入字符串,返回对应的ASCII字符,测试用例如下: 只有ASCII字符:
select asciistr('Hello, World!') from dual;
asciistr
---------------
Hello, World!
(1 row)
非ASCII字符:
select asciistr('你好') from dual;
asciistr
------------
\4F60\597D
同时包含ASCII字符和非ASCII字符:
select asciistr('ABÄCDE') from dual;
asciistr
------------
AB\00C4CDE
(1 row)
42、兼容TO_MULTI_BYTE函数, 功能:将字符串中的半角字符转换为全角字符: 输入半角字符,转换为全角字符:
select to_multi_byte('1.2'::text) ;
to_multi_byte
---------------
1.2
43、兼容TO_SINGLE_BYTE函数, 功能:将字符串中的半角字符转换为全角字符 输入全角字符,转换为半角字符:
select to_single_byte('1.2');
to_single_byte
----------------
1.2
44、兼容COMPOSE函数,功能:将基本字符和组合标记组合一个复合Unicode字符: 输入基本字符a和组合标记768, 返回法语à
select compose('a'||chr(768)) from dual;
compose
---------
à
(1 row)
45、兼容DECOMPOSE函数,功能:将复合Unicode字符(如带有重音或特殊符号的字符)分解为其基本字符和组合标记 输入法语é,返回基本字符e和组合标记301:
select asciistr(decompose('é')) from dual;
asciistr
----------
e\0301