Oracle中的单行函数
一. 定义
单行函数为查询的表或视图的每一行返回一个结果行。这些函数可以出现在可以出现在SELECT列中，WHERE子句，START WITH 和CONNECT BY子句以及HAVING子句中。
单行函数大致分为：数值函数，字符函数，日期时间函数，转换函数，和通用函数。
二. 数值函数
数值函数接受数值输入并返回数值。大多数数值函数接受返回精确到 38 个十进制数字的值。常用的数值函数有abs()，round()，trunc()，ceil()，floor()，mod()，power()，和sign()等。
1. abs()
abs(x)：返回数值的绝对值。
正数的绝对值是其本身，负数的绝对值是其相反数，零的绝对值是零。
select abs(-1),abs(1),abs(0) from dual; abs(-1) abs(1) abs(0) ---------------------------- 1 1 0
2. round()
round(x,y)：四舍五入，保留y位小数
其中y可以是正数，负数和零。当y=0时，y值可省略。
select round(123.456,2)，round(123.456,-2),round(123.456,0) from dual; round(123.456,2) round(123.456,-2) round(123.456,0) ---------------------------------------------------------- 123.46 100 123
3. trunc()
trunc(x,y)：截断，数值不会进行四舍五入
select trunc(123.456,2),trunc(123.456,-2),trunc(123.456,0)
from dual;
trunc(123.456,2) trunc(123.456,-2) trunc(123.456,0)
-----------------------------------------------------------
123.45 100 123
4.ceil()和floor()
ceil(x)：向上取整
floor(x)：向下取整
select ceil(123.456),floor(123.456),ceil(-123.456),floor(-123.456)
from dual;
ceil(123.456) floor(123.456) ceil(-123.456) floor(-123.456)
----------------------------------------------------------------------
124 123 -123 -124
5. mod()
mod(x,y)：取x除以y得到的余数
select mod(3,2),mod(7,2) from dual; mod(3,2) mod(7,2) --------------------- 1 1
6. sign()
sign(x)：判断数值的正负和是否为零值。
x为正数时，函数返回1。x为负数时，函数返回-1。x为零时，返回0。
select sign(-10),sign(10),sign(0)
from dual;
sign(-10) sign(10) sign(0)
-----------------------------------
-1 1 0
7. power()
语法：power(x,y)
底数x和指数y的数值可以是任何数值，但是当指数y为负数时，底数x必须为整数。
当y为正数时，返回值为x的y次方。
当y为负数时，返回值为x的y次方的倒数。
当y为小数时，表示对x进行开方。
select power(8,3),power(8,-3),power(8,1/3) from dual; power(8,3) power(8,-3) power(8,1/3) -------------------------------------------------- 512 0.001953125 2
二. 字符函数
接受字符类型的参数，并返回字符值（除了chr()函数之外）。
常用的字符函数有：chr()，ascii()，upper()，lower()，initcap()，trim()，ltrim()，rtrim()，lpad()，rpad()，instr()，substr()，length()，lengthb()，replace()和translate()函数等。
1. chr()和ascii()
chr()函数和ascii()函数是一对反函数，acii(x)是返回字符x相对应的阿斯克码值，而chr(x)则是阿斯克码值转换陈相对应的字符。
select ascii('a'),chr(ascii('a'))
from dual;
ascii('a') chr(ascii('a'))
-----------------------------
97 a
2. upper()，lower()和initcap()
upper(string)：返回字符串中的所有字母都是大写形式的string。
lower(string)：返回字符串中的所有字母都是小写形式的string。
initcap(string)：返回字符串每个单词第一个字母大写而单词中的其他字母小写的string。
select upper('Oracle'),lower('Oracle'),initcap('oracle')
from dual;
upper('Oracle') lower('Oracle') initcap('oracle')
----------------------------------------------------------
ORACLE oracle Oracle
3. trim()，ltrim()和rtrim()
ltrim(string1,string2)：返回删除从左边算起出现在string2中的字符的string1。
rtrim(string1,string2)：返回删除从右边算起出现在string2中的字符的string1。
注意：ltrim()和rtrim()函数中的string2可以是一个字符，也可以是字符串，当string2为字符串时，也要将其看作多个单独的字符，而不是一个整体。
# ltrim()和rtrim()的用法
select ltrim('abca','a'),ltrim('abca','b'),ltrim('abca','ab'),ltrim('abca','ac')
from dual;
ltrim('abca','a') ltrim('abca','b') ltrim('abca','ab') ltrim('abca','ac')
-----------------------------------------------------------------------------------
bca abca ca bca
# trim()的用法，既可以单独删除顶端和尾端的字符，也可以同时操作
select
trim(both 'a' from 'abca'),trim(leading 'a' from 'abca'),trim(trailing 'a' from 'abca')
from dual;
trim(both 'a' from 'abca') trim(leading 'a' from 'abca') trim(trailing 'a' from 'abca')
-------------------------------------------------------------------------------------------
bc bca abc
4. lpad()和rpad()
lpad(string1,n,string2)：从左边对字符串使用指定的字符串进行填充
rpad(string1,n,string2)：从右边对字符串使用指定的字符串机型填充
当n的值大于字符串string2的长度时，正常填充。当n的值小于指定填充字符串的长度时，将字符串string1截断成从左到右的n个字符
select lpad('hello',10,'oracle'),rpad('hello',10,'oracle')
from dual;
lpad('hello',10,'oracle') rpad('hello',10,'oracle')
---------------------------------------------------------
oraclhello hellooracl
# 当n小于指定填充字符串的长度时
select lpad('hello',3,'oracle'),rpad('hello',3,'oracle')
from dual;
lpad('hello',3,'oracle') rpad('hello',3,'oracle')
---------------------------------------------------------
hel hel
5. instr()
instr(string1,sting2,n1,n2)：从n1开始，查找字符串string1中第n2次出现字符串string2的位置，返回的是一个数字。
n1不写时，默认从第一位开始；n1为负数时,表示从后往前，倒着开始查找。
select instr('hello oracle','l'),instr('hello oracle','l',1,3),instr('hello oracle','l',-1,3)
from dual;
instr('hello oracle','l') instr('hello oracle','l',1,3) instr('hello oracle','l',-1,3)
----------------------------------------------------------------------------------------
3 11 3
6. substr()
substr(string1,n1,n2)：从n1的位置开始，截取n2位长度的字符串。
n1位负数时，表示倒着截取。
当截取的位数不够时，即截取到最后。
select
substr('hello oracle',1,5),substr('hello oracle',-5,1),substr('hello oracle',-5,6)
from dual;
substr('hello oracle',1,5) substr('hello oracle',-5,1) substr('hello oracle',-5,6)
----------------------------------------------------------------------------------------
hello r raccle
7. replace()
replace(string1,string2,string3)：将字符串string1中的string2，用字符串string3代替。
string3不写时，默认为用空替换。
select replace('hello oracle','oracle','python')
from dual;
replace('hello oracle','oracle','python')
-------------------------------------------
hello python
8. translate()
translate(string1,string2,string3)：逐一替换，将字符串string1中的string2逐一替换成string3。
当string2>string3时，string2中多出来的字符会被替换成空。
当string2<string3时，string3中多出来的字符失效。
select translate('hello oracle','elo','123')
from dual;
translate('hello oracle','elo','123')
---------------------------------------
h1223 3rac21
# 当string2的长度 > string3的长度
select translate('hello oracle','elo','12')
from dual;
translate('hello oracle','elo','12')
----------------------------------------
h122 rac21
# 当string2的长度 < string3的长度
select translate('hello oracle','el','123')
from dual;
translate('hello oracle','el','123')
-----------------------------------------
h122o orac21
三. 日期时间函数
1. sysdate
sysdate：返回数据库服务器所在的操作系统设置的当前日期和时间。
如果想要让sysdate函数返回当前系统的日期，可以修改当前会话的日期显示格式。
alter session set nls_date_format = 'yyyy-dd-mm'
想要继续显示当前系统的时间与日期，只需要将格式修改回来即可。
alter session set nls_date_format = 'yyyy-dd-mm hh24:mi:ss'
2. add_months()
add_months(date,months)：给给定的日期增加或减少指定数量的月份。
months：指定的月数可以是正数，负数和小数。当months为小数时，数会默认截断到整数位。
select
add_months(date'2022-01-01',1),add_months(date'2022-01-01',-1),add_months(date'2022-01-01',1.4),add_months(date'2022-01-01',1.9)
from dual;
add_months(date'2022-01-01',1) add_months(date'2022-01-01',-1) add_months(date'2022-01-01',1.4) add_months(date'2022-01-01',1.9)
----------------------------------------------------------------------------------------------------------------------------------
2022/2/1 2021/12/01 2022/2/1 2022/2/1
3. months_between()
months_between(date1,date2)：返回两个日期之间相差的月数。
select months_between(date'2023-01-01',date'2022-01-01')
from dual;
months_between(date'2023-01-01',date'2022-01-01')
--------------------------------------------------
12
4. last_day()
last_day(date)：返回指定日期所在月份的最后一天
select last_day(date'2022-01-01')
from dual;
last_day(date'2022-01-01')
----------------------------
2022/1/31
5. next_day()
next_day(date,chr)：返回给定日期指定的下一个周几
# 查看2022/1/1后的第一个星期三 select next_day(date'2022-01-01','星期三') from dual; next_day(date'2022-01-01','星期三') ---------------------------------------- 2022/1/5 # 查询2022年的第一个周三 # 需要考虑2022/1/1是周三的情况，所以我们指定的初始日期要定为2021/12/31，而不是2022/1/1 select next_day(date'2021-12-31','星期三') from dual; next_day(date'2021-12-31','星期三') ----------------------------------- 2022/1/5
利用next_day()函数，查询指定日期的下周周几，语法：next_day(next_day(date-1,'星期日'),'星期几')
select next_day(next_day(date'2022-01-01'-1,'星期日'),'星期三')
from dual;
next_day(next_day(date'2022-01-01'-1,'星期日'),'星期三')
-------------------------------------------------------
2022/1/5
6. round()
round(date,format)：按照指定的格式将日期四舍五入。
四舍五入的原则是：过半则入，不过则舍
format 四舍五入的具体情况
yyyy 月份为1-6，返回到本年年初；月份为7-12，返回到来年年初
mm 1-15号，返回到本月月初；6-31号，返回到下月月初
dd 0-11时，返回本日初；12-23时，返回下一天日初
hh 0-29分，返回本小时初；30-59分，返回下一个小时初
mi 0-29秒，返回本分钟初；30-59秒，返回下一个分钟初
q 小于本季度第二个月的15号，返回本季度初；大于本季度第二个月的15号，返回下一个季度初
d/dy 周日-周三，返回本周初；周四-周六，返回下周初
# 四舍五入到年初
select round(date'2022-06-30','yyyy'),round(date'2022-12-31','yyyy')
from dual;
round(date'2022-06-30','yyyy') round(date'2022-12-31','yyyy')
---------------------------------------------------------------------
2022/1/1 2023/1/1
# 四舍五入到月初
selecy round(date'2022-06-05','mm'),round(date'2022-06-30','mm')
from dual;
round(date'2022-06-05','mm') round(date'2022-06-30','mm')
---------------------------------------------------------------------
2022/6/1 2022/7/1
# 四舍五入到季度初
select round(date'2022-05-01'，'q'),round(date'2022-06-30','q')
from dual;
round(date'2022-05-01','q') round(date'2022-06-30','q')
--------------------------------------------------------------------
2022/4/1 2022/7/1
7. trunc()
trunc(date,format)：将给定的日期按照指定的格式截断到日期之初
和round不同，trunc()函数不会四舍五入，它的作用就是将日期截断到日期之初
# 截断到年初
select trunc(date'2022-06-30','yyyy'),trunc(date'2022-12-31','yyyy')
from dual;
trunc(date'2022-06-30','yyyy') trunc(date'2022-12-31','yyyy')
--------------------------------------------------------------------
2022/1/1 2022/1/1
# 截断到月初
select trunc(date'2022-01-15','mm'),runc(date'2022-01-31','mm')
from dual;
trunc(date'2022-01-15','mm') trunc(date'2022-01-31','mm')
-------------------------------------------------------------------
2022//1/1 2022/1/1
四. 通用函数
1. greatest()
greatest(参数1,参数2,参数3.......)：返回给定参数列表中的最大值
使用greatest()函数时，它的返回结果取决于第一个参数的数据类型，如果第一个参数是数值类型的，那么后续的所有参数都将隐式转换成数值类型。
这就要求所有参数的数据类型需要保持一致，或者能够转换成相应目标参数的数据类型，否则无法进行比较。
当参数中出现NULL时，返回值为NULL。
# 整数类型可以隐式转换成字符类型
select greatest('a','b',1)
from dual;
# 日期类型可以转换成字符类型
select greatest('a','b',date'2022-01-01')
from dual;
2. coalesce()
coalesce(参数1,参数2,参数3.....)：返回多个参数中第一个不为空的值
- 参数类型要保持一致，否则数据库会报错
select coalesce('a',null,'1')
from dual;
3. nvl()
nvl(参数1,参数2)：返回一个不为空的值，和coalesce()函数的用法相似
这里在使用nvl()函数时，想要处理哪个字段里的空值，就将相应字段放在参数1的位置，参数2的位置放置字段为空时的返回值
# 以oracle数据库自带的雇员表为例 select nvl(comm,0) from emp;
4. distinct
distinct：用来去重，使用distinct去重数据时，相应字段的数据要完全一致才可以去重，否则无法生效
5. nullif()
nullif(参数1,参数2)：比较两个参数，如果一致返回空NULL，如果不一致，则返回第一个参数
nullif()函数经常被用于0做除数的情况
# emp表中的字段comm中有空值和0，理论上是不能做除数的 # 这时可以利用nullif()函数将零值去除，转换成空 select sal,comm,sal/nullif(comm,0) from emp;
6. decode()
decode(字段,列中值1,返回值1,列中值2,返回值2.....,返回值n)
select decode(comm,null,0,comm) from dual;
case when的用法在一定程度上和decode函数的用法一样，可以达到同样的效果
select case when comm is null then 0 else comm end from emp;
五.转换函数
1. to_date()
to_date(char,format)：根据可选的格式将给定的字符串型日期转换成date数据类型的值
format 描述
yyyy 年
mm 月
dd 日
hh12 12小时制的小时
hh24 24小时制的小时
mi 分钟
ss 秒
select to_date('2022-01-01','yyyy-mm-dd'),to_date('20220101 121212','yyyy-mm-dd hh24:mi:ss')
from dual;
to_date('2022-01-01','yyyy-mm-dd') to_date('20220101 121212','yyyy-mm-dd hh24:mi:ss')
-----------------------------------------------------------------------------------------
2022/1/1 2022/1/1 12:12:12
- 如果给定的日期没有年份，则默认为本年
- 如果给定的日期没有月份，则默认为本月
- 如果给定的日期没有天数，则默认为一号
- 如果给定的日期没有时间，则默认为零时零分零秒
- 当日期之间有汉字时，将其作为分隔符，并在其两端加上双引号
# 不给定年份，或者说默认为今年
select to_date('01-01','mm-dd')
from dual;
# 不给定月份，或者默认为本月
select to_date('01','dd')
from dual;
# 只给定时间,查询日期为本月一号12时
select to_date('12:00:00','hh24:mi:ss')
# 带汉字的日期
select to_date('1月1日','mm"月"dd"日"')
from dual;
2. to_char()
①to_char(date,format)：将给定的日期或者时间按照指定的格式转换成字符串
format 描述
yyyy 年
mm 月
dd 日
hh24/hh12 24小时制的时/12小时制的时
mi 分
ss 秒
am/pm 上午/下午
d 以数字表达的星期，1表示星期日，2表示星期一.......
dy/day 星期
ddd 当天是当年的第几天
sss 当秒是当天的第几秒
select to_char(date'2022-01-01','yyyy'),to_char(date'2022-01-01','ddd'),to_char(date'2022-01-01','day')
from dual;
to_char(date'2022-01-01','yyyy') to_char(date'2022-01-01','ddd') to_char(date'2022-01-01','day)'
---------------------------------------------------------------------------------------------------------
2022 1 星期六
②to_char(数值,格式)：将数值按照给定的格式输出
格式：
0 占位符
9 占位符
L 本地货币
$ 美元
， 分隔符
. .
select to_char(100000000,'L999,999,990.999') from dual; char(100000000,'L999,999,990.999') --------------------------------------- ￥100,000,000.000
③to_char()：最基本的用法，将其他类型的值转换成字符类型的值
select to_char(date'2022-01-01'),to_char(1) from dual;
