首页 > 数据库 >Oracle中的单行函数

Oracle中的单行函数

时间:2023-04-07 16:46:32浏览次数:43  
标签:01 date 函数 dual 单行 oracle 2022 Oracle select

一. 定义

  单行函数为查询的表或视图的每一行返回一个结果行。这些函数可以出现在可以出现在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

相关文章

  • 获取Python函数信息的方法
    Python的反射机制可以动态获取对象信息以及动态调用对象,本文介绍如何获取对象中的函数注释信息以及参数信息。定义一个Person类:classPerson():deftalk(self,name,age,height=None):"""talkfunction:return:"""print(f"Mynamei......
  • C++虚函数
    形式:virtual函数类型函数名()(在派生类和基类里都要写)静态成员函数不能是虚函数1.通过指针实现多态对于基类的对象:调用基类的虚函数对于派生类的对象:调用派生类的虚函数#include<iostream>usingnamespacestd;classA{ public: virtualvoidPrint() { cout<<"printA"......
  • C++知晓某个key值,调用相应的类函数
    1、类函数中定义一个map表typedefint(CClassTest::*pfnMethodExe)(std::stringstrInput,intnInputNum); std::map<std::string,pfnMethodExe>m_fnMethodExecute;CClassTest为类名,typedefint中的int为函数返回值,可以为其他值2、key值和函数对应关系放入map表中m_fnMeth......
  • Python中的时间函数strftime与strptime对比
    一、striftime将给定格式的日期时间对象转换为字符串。日期时间对象=>字符串,控制输出格式.date、datetime、time对象都支持strftime(format) 方法,可用来创建由一个显式格式字符串所控制的表示时间的字符串。用法:datetime.strftime(format)importdatetimedt=datetime.dateti......
  • Oracle JDK 和 OpenJDK 有什么区别?
    可能在看这个问题之前很多人和我一样并没有接触和使用过OpenJDK。那么OracleJDK和OpenJDK之间是否存在重大差异?下面我通过收集到的一些资料,为你解答这个被很多人忽视的问题。对于Java7,没什么关键的地方。OpenJDK项目主要基于Sun捐赠的HotSpot源代码。此外,OpenJDK......
  • 自制 js 的 trim、right、left、instrRev、instr、mid 函数
    //用惯了VB系列语法的instr和mid函数,在js里面处理字符串总是感觉束手束脚//跟Basic语法比起来,其他语言的字符串处理函数的确非常不方便!!!因此,必须自己写个仿Basic语法的javascript的常用字符串处理函数库来用!!!//以下是经过多次修正,便于js处理字符串的函数functi......
  • Keil Arm中使用malloc函数的方法
    出处 http://blog.sina.com.cn/s/blog_a189aca10102vabo.htmlKeilArm中使用malloc函数的方法:1、需要使用微库:如果不选用微库的话会报错,例如:.\Output\SaiWu.axf:Error:L6915E:Libraryreportserror:__use_no_semihostingwasrequested,but_ttywrchwasreferenced2......
  • 题目 1031: [编程入门]自定义函数之字符串反转
    在主函数中输入一个字符串(不包含空格),写一个新函数将字符串按反序存放,并在主函数中输出反序后的字符串gets()能把字符串写入数组里,我只需要再写一个新数组,把array数组的最后一个元素赋值给新数组的第一个元素,把array的倒数第二个赋值给新数组的第二个……这样一个一个赋值,万一阿......
  • Oracle中的伪列
    一.rownum1.rownum等于某值时的查询条件:只有rownum=1,有数据显示。rownum=n(n>2)没有数据,如果想要实现,则可以搭配子查询使用selecta.*,rownumfromempwhererownum=1;2.rownum大于某值时的查询条件:Oracle对于rownum>=n这个条件是不成立的,即使是n=1,也没有任何数据返......
  • MPI通信相关函数
    MPI环境操作初始化MPI_INIT结束MPI_FIANLIZE MPI通信子操作MPI_COMM_SIZEMPI数据类型通信函数SEND()函数格式intMPI_Send(void*buf,intcount,MPI_Datatypedatatype,intdest,inttag,MPI_Commcomm)buf:发送数据buffercount:发送数据长度datatype......