Oracle中的单行函数

一. 定义

　　单行函数为查询的表或视图的每一行返回一个结果行。这些函数可以出现在可以出现在SELECT列中，WHERE子句，START WITH 和CONNECT BY子句以及HAVING子句中。

　　单行函数大致分为：数值函数，字符函数，日期时间函数，转换函数，和通用函数。

二. 数值函数

　　数值函数接受数值输入并返回数值。大多数数值函数接受返回精确到 38 个十进制数字的值。常用的数值函数有abs()，round()，trunc()，ceil()，floor()，mod()，power()，和sign()等。

1. abs()

　　abs(x)：返回数值的绝对值。

　　正数的绝对值是其本身，负数的绝对值是其相反数，零的绝对值是零。

?

1 2 3 4 5 6

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值可省略。

?

1 2 3 4 5 6

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)：截断，数值不会进行四舍五入

?

1 2 3 4 5 6 7

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)：向下取整

?

1 2 3 4 5 6

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得到的余数

?

1 2 3 4 5 6

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。

?

1 2 3 4 5 6

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进行开方。

?

1 2 3 4 5 6

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)则是阿斯克码值转换陈相对应的字符。

?

1 2 3 4 5 6

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。

?

1 2 3 4 5 6

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为字符串时，也要将其看作多个单独的字符，而不是一个整体。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# 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个字符

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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为负数时,表示从后往前，倒着开始查找。

?

1 2 3 4 5 6

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位负数时，表示倒着截取。

　　当截取的位数不够时，即截取到最后。

?

1 2 3 4 5 6 7

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不写时，默认为用空替换。

?

1 2 3 4 5 6

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中多出来的字符失效。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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函数返回当前系统的日期，可以修改当前会话的日期显示格式。

?

1	alter session set nls_date_format = 'yyyy-dd-mm'

　　想要继续显示当前系统的时间与日期，只需要将格式修改回来即可。

?

1	alter session set nls_date_format = 'yyyy-dd-mm hh24:mi:ss'

2. add_months()

　　add_months(date,months)：给给定的日期增加或减少指定数量的月份。

　　months：指定的月数可以是正数，负数和小数。当months为小数时，数会默认截断到整数位。

?

1 2 3 4 5 6 7

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)：返回两个日期之间相差的月数。

?

1 2 3 4 5 6

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)：返回指定日期所在月份的最后一天

?

1 2 3 4 5 6

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)：返回给定日期指定的下一个周几

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# 查看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,'星期日'),'星期几')

?

1 2 3 4 5 6

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　　　　  周日-周三，返回本周初；周四-周六，返回下周初

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

# 四舍五入到年初 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()函数不会四舍五入，它的作用就是将日期截断到日期之初

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# 截断到年初 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。

?

1 2 3 4 5 6 7

# 整数类型可以隐式转换成字符类型 select greatest('a','b',1) from dual;   # 日期类型可以转换成字符类型 select greatest('a','b',date'2022-01-01') from dual;

2. coalesce()

　　coalesce(参数1,参数2,参数3.....)：返回多个参数中第一个不为空的值

• 参数类型要保持一致，否则数据库会报错

?

1 2	select coalesce('a',null,'1') from dual;

3. nvl()

　　nvl(参数1,参数2)：返回一个不为空的值，和coalesce()函数的用法相似

　　这里在使用nvl()函数时，想要处理哪个字段里的空值，就将相应字段放在参数1的位置，参数2的位置放置字段为空时的返回值

?

1 2 3

# 以oracle数据库自带的雇员表为例 select nvl(comm,0) from emp;

4. distinct

　　distinct：用来去重，使用distinct去重数据时，相应字段的数据要完全一致才可以去重，否则无法生效

5. nullif()

　　nullif(参数1,参数2)：比较两个参数，如果一致返回空NULL，如果不一致，则返回第一个参数

　　nullif()函数经常被用于0做除数的情况

?

1 2 3 4

# emp表中的字段comm中有空值和0，理论上是不能做除数的 # 这时可以利用nullif()函数将零值去除，转换成空 select sal,comm,sal/nullif(comm,0) from emp;

6. decode()

　　decode(字段,列中值1,返回值1,列中值2,返回值2.....,返回值n)

?

1 2	select decode(comm,null,0,comm) from dual;

　　case when的用法在一定程度上和decode函数的用法一样，可以达到同样的效果

?

1 2	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                    秒

?

1 2 3 4 5 6

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

• 如果给定的日期没有年份，则默认为本年
• 如果给定的日期没有月份，则默认为本月
• 如果给定的日期没有天数，则默认为一号
• 如果给定的日期没有时间，则默认为零时零分零秒
• 当日期之间有汉字时，将其作为分隔符，并在其两端加上双引号

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

# 不给定年份，或者说默认为今年 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                 当秒是当天的第几秒

?

1 2 3 4 5 6

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　　本地货币

　　$　　美元

　　，　 分隔符　

　　.　　.

?

1 2 3 4 5 6

select to_char(100000000,'L999,999,990.999') from dual;   char(100000000,'L999,999,990.999') ---------------------------------------  　　　　￥100,000,000.000

　　③to_char()：最基本的用法，将其他类型的值转换成字符类型的值

?

1 2	select to_char(date'2022-01-01'),to_char(1) from dual;