Oracle函数
（1）Oracle字符型函数
oracle字符型函数
函数 | 说明 | 案例 | 结果 |
ASCII(X) | 求字符X的ASCII码 | select ASCII('A') FROM DUAL; | 65 |
CHR(X) | 求ASCII码对应的字符 | select CHR(65) FROM DUAL; | 'A' |
LENGTH(X) | 求字符串X的长度 | select LENGTH('ORACLE数据库')from DUAL; | 9 |
CONCATA(X,Y) | 返回连接两个字符串X和Y的结果 | select CONCAT('ORACLE','数据库') from DUAL; | ORACLE数据库 |
INSTR(X,Y[,START]) | 查找字符串X中字符串Y的位置，可以指定从Start位置开始搜索，不填默认从头开始 | SELECT INSTR('ORACLE数据库','数据') FROM DUAL; | 7 |
LOWER(X) | 把字符串X中大写字母转换为小写 | SELECT LOWER('ORACLE数据库') FROM DUAL; | oracle数据库 |
UPPER(X) | 把字符串X中小写字母转换为大写 | SELECT UPPER('Oracle数据库') FROM DUAL; | ORACLE数据库 |
INITCAP(X) | 把字符串X中所有单词首字母转换为大写，其余小写。 | SELECT INITCAP('ORACLE and mysql ') FROM DUAL; | Oracle And Mysql |
LTRIM(X[,Y]) | 去掉字符串X左边的Y字符串，Y不填时，默认的是字符串X左边去空格 | SELECT LTRIM('--ORACLE数据库','-') FROM DUAL; | ORACLE数据库 |
RTRIM(X[,Y]) | 去掉字符串X右边的Y字符串，Y不填时，默认的是字符串X右边去空格 | SELECT RTRIM('ORACLE数据库--','-') FROM DUAL; | ORACLE数据库 |
TRIM(X[,Y]) | 去掉字符串X两边的Y字符串，Y不填时，默认的是字符串X左右去空格 | SELECT TRIM('--ORACLE数据库--','-') FROM DUAL; | ORACLE数据库 |
REPLACE(X,old,new） | 查找字符串X中old字符，并利用new字符替换 | select replace('ORACLE数据库','ORACLE','关系型') fromdual; | 关系型数据库 |
SUBSTR(X,start[,length]) | 截取字符串X，从start位置（其中start是从1开始）开始截取长度为length的字符串，length不填默认为截取到字符串X末尾 | SELECT SUBSTR('ORACLE数据库',1,6) FROM DUAL; | ORACLE |
RPAD(X,length[,Y]) | 对字符串X进行右补字符Y使字符串长度达到length长度 | SELECT RPAD('ORACLE',9,'-') from DUAL; | ORACLE--- |
LPAD(X,length[,Y]) | 对字符串X进行左补字符Y使字符串长度达到length长度 | SELECT LPAD('ORACLE',9,'-') from DUAL; | ---ORACLE |
（2）Oracle日期函数
1、SYSDATE函数：该函数没有参数，可以得到系统的当前时间。
select sysdate from dual;
2、SYSTIMESTAMP函数：该函数没有参数，可以得到系统的当前时间，该时间包含时区信息，精确到微秒。
select systimestamp from dual;
3、DBTIMEZONE函数：该函数没有输入参数，返回数据库时区。
select dbtimezone from dual;
4、
to_char函数和to_date函数：转换日期时间格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --日期转指定格式的字符串select to_date('2019-11-12','yyyy-mm-dd') from dual; --字符串转日期格式
5、ADD_MONTHS（r,n）函数：该函数返回在指定日期r上加上月份数n后的日期。其中
r：指定的日期。n：要增加的月份数，如果N为负数，则表示减去的月份数。
select to_char(add_months(
sysdate,2),'yyyy-mm-dd') from dual;6、LAST_DAY(r)函数：返回指定r日期的当前月份的最后一天日期。
select to_char(last_day(sysdate),'yyyy-mm-dd') from dual;
7、NEXT_DAY(r,c)函数：返回指定R日期的后一周的与r日期字符（c：表示星期几,1：周天，2：周1）对应的日期。
select to_char(next_day(sysdate,1),'yyyy-mm-dd') from dual;
8、EXTRACT（time）函数：返回指定time时间当中的年、月、日、分等日期部分。
select
extract(year from sysdate) as year0,
extract(year from timestamp '2018-11-12 15:36:01') as year,
extract(month from timestamp '2018-11-12 15:36:01') as month,
extract(day from timestamp '2018-11-12 15:36:01') as day,
extract(hour from timestamp '2018-11-12 15:36:01') as hour,
extract(minute from timestamp '2018-11-12 15:36:01') as minute,
extract(second from timestamp '2018-11-12 15:36:01') as second
from dual;9、substr(to_char(））--截取年、月、日、时、分、秒
select
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),1,4) s_year ,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),6,2) s_month,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),9,2) s_day,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),13,2) s_hour,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),16,2) s_minute,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),19,2) s_second
from dual;10、MONTHS_BETWEEN(r1,r2)函数：该函数返回r1日期和r2日期直接的月份。当r1>r2时，返回的是正数，假如r1和r2是不同月的同一天，则返回的是整数，否则返回的小数。当r1<r2时，返回的是负数
select months_between(to_date('2019-10-1','yyyy-mm-dd'),to_date('2019-8-5','yyyy-mm-dd')) from dual;
11、ROUND（r[,f]）函数：将日期r按f的格式进行四舍五入。如果f不填，则四舍五入到最近的一天。
select sysdate, --当前时间
round(sysdate, 'yyyy') as year, --按年
round(sysdate, 'mm') as month, --按月
round(sysdate, 'dd') as day, --按天
round(sysdate) as mr_day, --默认不填按天
round(sysdate, 'hh24') as hour --按小时
from dual;12、TRUNC（r[,f]）函数：将日期r按f的格式进行截取。如果f不填，则截取到当前的日期。
select sysdate, --当前时间
trunc(sysdate, 'yyyy') as year, --按年
trunc(sysdate, 'mm') as month, --按月
trunc(sysdate, 'dd') as day, --按天
trunc(sysdate) as mr_day, --默认不填按天
trunc(sysdate, 'hh24') as hour --按小时
from dual;
（3）Oracle数值型函数
Oracle数值型函数
函数 | 解释 | 案例 | 结果 |
ABS(X) | 求数值X的绝对值 | select abs(-5) from dual; | 5 |
COS(X) | 求数值X的余弦 | select cos(0.5) from dual; | 0.87758... |
ACOS(X) | 求数值X的反余弦 | select acos(0.5) from dual; | 1.04719... |
CEIL(X) | 求大于或等于数值X的最小整数 | select ceil(5.5) from dual; | 6 |
FLOOR(X) | 求小于或等于数值X的最大整数 | select floor(5.5) from dual; | 5 |
log(x,y) | 求x为底y的对数 | select log(2,32) from dual; | 5 |
mod(x,y) | 求x除以y的余数 | select mod(15,10) from dual; | 5 |
power(x,y) | 求x的y次幂 | select power(2,5) from dual; | 32 |
sqrt(x) | 求x的平方根 | select sqrt(25) from dual; | 5 |
round(x[,y]) | 求数值x在y位进行四舍五入。 | select round(5.615, 2), round(5.615), round(6.45,-1) from dual; | 5.62 | 6 |10 |
y不填时，默认为y=0; | |||
当y>0时，是四舍五入到小数点右边y位。 | |||
当y<0时，是四舍五入到小数点左边|y|位。 | |||
trunc(x[,y]) | 求数值x在y位进行直接截取 | select trunc(5.615, 2), trunc(5.615), trunc(5.615,-1) from dual; | 5.61 | 5 | 0 |
y不填时，默认为y=0; | |||
当y>0时，是截取到小数点右边y位。 | |||
当y<0时，是截取到小数点左边|y|位。 |
（4） Oracle转换函数
1、to_char()函数：将DATE或者NUMBER转换为字符串
2、 to_date()函数：将number、char转换为date
3、 to_number()函数：将char转换为number
4、CAST(expr AS type_name)函数：用于将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型。expr为列名或值，type_name数据类型。
SELECT CAST('123.4' AS int) from dual;
结果：123
可进行四舍五入操作：SELECT CAST('123.447654' AS decimal(5,2)) as result from dual;
decimal(5,2)表示值总位数为5，精确到小数点后2位。
结果：123.45
5、TO_MULTI_BYTE(c1)函数：将字符串c1中的半角转化为全角。TO_MULTI_BYTE和TO_SINGLE_BYTE是相反的两个函数.
select to_multi_byte('高A') text from dual;6、to_single_byte(c1)函数：将字符串c1中的全角转化为半角。
select to_single_byte('高Ａ') text from dual;7、TIMESTAMP_TO_SCN(timestamp)函数：用于根据输入的timestamp返回所对应的scn值，其中timestamp用于指定日期时间。作为对于闪回操作(flashback)的一个增强，Oracle10g提供了函数对于SCN和时间戳进行相互转换。
select timestamp_to_scn(sysdate) scn from dual;
结果：9709105
8、SCN_TO_TIMESTAMP(number)函数：根据输入的scn值返回对应的大概日期时间，其中number用于指定scn值.
select to_char(scn_to_timestamp(9709105), 'yyyy-mm-dd hh24:mi:ss')from dual;
结果：2018-05-21 18:23:35
9、CONVERT(string,dest_set[,source_set])函数：将字符串string从source_set所表示的字符集转换为由dest_set所表示的字符集.如果source_set没有被指定,它缺省的被设置为数据库的字符集.
ZHS16GBK表示采用GBK编码格式、16位（两个字节）简体中文字符集
WE8ISO8859P1(西欧、8位、ISO标准8859P1编码)
AL32UTF8（其中AL代表ALL,指适用于所有语言）、zhs16cgb231280
select convert('中国','US7ASCII','WE8ISO8859P1') "conversion" from dual;
结果：O??u
10、TRANSLATE(str1 USING zfj)函数：将字符串转变为数据库字符集(char_cs)或民族字符集(nchar_cs)
Select TRANSLATE(‘中国’ using nchar_cs) from dual;
结果：中国
11、ASCIISTR(s)函数：将任意字符集的字符串转变为数据库字符集的ASCII字符串。
Select ASCIISTR ('1A_中文') from dual;
结果：1A_\4E2D\6587
12、UNISTR(str1)函数：输入字符串返回相应的UNICODE字符
Select UNISTR ('\4E2D'),UNISTR ('\6587'),UNISTR ('\0300'),UNISTR ('\00E0') from dual;
结果：中 文 ` à
13、COMPOSE(string)函数：这个函数以UNICODE字符串为参数，返回一个规范化的字符串。比如，它可以接受一个字母和一个组合标记，比如说‘a'（Unicode 字符0097）和沉音符（Unicode 字符0300），然后创建一个单独的由两个标记组合而成的字符(à)。
Select COMPOSE('a'||unistr('\0300')) from dual;
结果：à
14、DECOMPOSE(string)函数：返回一个Unicode字符串。它是string的规范分解。
SELECT DECOMPOSE ('Châteaux') FROM DUAL;
结果：Cha^teaux
15、CHARTOROWID(c1)函数：将字符数据类型CHAR或VARCHAR2转换为ROWID值.参数c1是长度为18的字符串，必须符合rowid格式.CHARTOROWID是ROWIDTOCHAR的反函数.
在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同.
SELECT chartorowid('AAAADeAABAAAAZSAAA') FROM DUAL;
结果：AAAADeAABAAAAZSAAA
16、ROWIDTOCHAR(rowid)函数：转换rowid值为varchar2类型，rowid固定参数，返回长度为18的字符串。
SELECT ROWIDTOCHAR(rowid) FROM DUAL;
结果：AAAAB0AABAAAAOhAAA
17、INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]函数：该数据类型常用来表示一段时间差, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2.
select INTERVAL '123-2' YEAR(3) TO MONTH from dual;
表示: 123年2个月, "YEAR(3)" 表示年的精度为3, 可见"123"刚好为3为有效数值, 如果该处YEAR(n), n<3就会出错, 注意默认是2
结果：+123-02
18、HEXTORAW(string)函数：将string一个十六进制构成的字符串转换为二进制RAW数值.String中的每两个字符表示了结果RAW中的一个字节.HEXTORAW和RAWTOHEX为相反的两个函数.当出现比f大的字母时(以a最小z最大)就会报错
select hextoraw('abcdef') from dual;
结果：ABCDEF
19、RAWTOHEX(rawvalue)函数：将raw串转换为十六进制. rawvalue中的每个字节都被转换为一个双字节的字符串.
select rawtohex('AA') from dual;
结果：4141 结果之所以是4141是因为A的ASCII为65，65转换为十六进制就是41。
20、TO_LOB (long_column)函数：将LONG或LONG ROW列的数据转变为相应的LOB类型。但需要注意的是，TO_LOB一般只用在CREATE TABLE或INSERT TABLE语句后面的子查询中。在其他地方使用会报错，比如UPDATE语句。
（5） Oracle聚合函数
函数 | 说明 | 备注 |
count(col) | 计数 | |
sum(col) | 求和 | |
avg(col) | 均值 | |
min(col)/max(col) | 最小/最大值 |
