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

Oracle中的单行函数

时间：2023-04-07 16:46:32浏览次数：37  
标签：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......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99