一. 定义
单行函数为查询的表或视图的每一行返回一个结果行。这些函数可以出现在可以出现在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;
标签:01,date,函数,dual,单行,oracle,2022,Oracle,select From: https://www.cnblogs.com/zhouyang-123/p/17268939.html