工作中,Oracle常用函数

1、序言

Oracle提供了不少内置函数,熟练使用这些函数,可以大大提高我们工作效率。函数可以接受0个或多个入参,并返回一个输出结果。

2、Oracle函数分类

Oracle函数分为单行函数和聚合函数

  • 单行函数:对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果。

    常见的单行函数有如下四种

    ① 数值型函数:对数字进行计算,返回一个数字。

    ②字符函数:对字符串操作。

    ③转换函数:可以将一种数据类型转换为另外一种数据类型。

    ④日期函数:对日期和时间进行处理。

    ⑤ null函数:处理null值的相关函数

  • 聚合函数:聚合函数同时可以对多行数据进行操作,并返回一个结果。

3、数值型函数

3.1 求绝对值函数

abs(n)函数

用于返回绝对值

SQL> select abs(10),abs(-10),abs('100') from dual;
 
 ABS(10) ABS(-10) ABS('100')
---------- ---------- ----------
 10 10 100

3.2 求余函数

mod(n2,n1)

返回n2除以n1的余数

SQL> select mod(5,2),mod(8/3,3),mod('10',2),mod(-10,6),mod(3,0) from dual;
 
 MOD(5,2) MOD(8/3,3) MOD('10',2) MOD(-10,6) MOD(3,0)
---------- ---------- ----------- ---------- ----------
 1 2.66666666 0 -4 3

3.3 判断数值正负函数

sign(n) 函数

n为正返回1,n为0返回0,n为负返回-1

SQL> select sign(-2),sign(2),sign(0.0),sign(-3*2) from dual;
 
 SIGN(-2) SIGN(2) SIGN(0.0) SIGN(-3*2)
---------- ---------- ---------- ----------
 -1 1 0 -1

3.4 三角函数

cos(n):返回余弦值 acos(n):返回反余弦值 sin(n):返回正弦值 asin(n) tan(n):返回正切值 atan(n):返回反正切值

SQL> select cos(3.1415926),acos(1),sin(0.5),asin(1),tan(1),atan(1) from dual;
 
COS(3.1415926) ACOS(1) SIN(0.5) ASIN(1) TAN(1) ATAN(1)
-------------- ---------- ---------- ---------- ---------- ----------
-0.99999999999 0 0.47942553 1.57079632 1.55740772 0.78539816

3.5 返回以指定数值为准整数的函数

ceil(n)函数

返回大于等于n的最小整数

SQL> select ceil(15),ceil(15.6),ceil(-10.2),ceil('10.2') from dual;
 
 CEIL(15) CEIL(15.6) CEIL(-10.2) CEIL('10.2')
---------- ---------- ----------- ------------
 15 16 -10 11

floor(n)函数

返回小于或等于n的最大整数

SQL> select floor(15),floor(15.6),floor(-10.2),floor('10.2') from dual;
 
 FLOOR(15) FLOOR(15.6) FLOOR(-10.2) FLOOR('10.2')
---------- ----------- ------------ -------------
 15 15 -11 10

3.6 指数、对数函数

sqrt(n)函数

返回n的平方根

SQL> select sqrt(4),sqrt('8.9') from dual;
 
 SQRT(4) SQRT('8.9')
---------- -----------
 2 2.983286778

power(n1,n2)函数

返回n1的n2次幂

SQL> select power(3,2),power('4',2),power(2.5,4),power(-5,2)from dual;
 
POWER(3,2) POWER('4',2) POWER(2.5,4) POWER(-5,2)
---------- ------------ ------------ -----------
 9 16 39.0625 25

log(n1,n2)

返回以n1为底n2的对数

SQL> select log(10,100),log(2.4,'10') from dual;
 
LOG(10,100) LOG(2.4,'10')
----------- -------------
 2 2.63011686739

3.7 四舍五入函数

round(x[,y]) 函数

  • 会四舍五入。在缺省 y 时,默认 y=0;
  • y 是正整数,就是四舍五入到小数点后 y 位;
  • y 是负整数,四舍五入到小数点左边|y|位
SQL> select round(3.564),round(3.456,2),round(3456.345,-2) from dual;
 
ROUND(3.564) ROUND(3.456,2) ROUND(3456.345,-2)
------------ -------------- ------------------
 4 3.46 3500

trunc(x[,y])函数

  • 直接截取,不四舍五入。在缺省 y 时,默认 y=0;
  • Y是正整数,就是四舍五入到小数点后 y位;
  • y 是负整数,四舍五入到小数点左边|y|位。
SQL> select trunc(3.564),trunc(3.456,2),trunc(3456.345,-2) from dual;
 
TRUNC(3.564) TRUNC(3.456,2) TRUNC(3456.345,-2)
------------ -------------- ------------------
 3 3.45 3400

4 字符型函数

4.1 ASSCII与字符转换函数

ASSCII(x) 函数

返回字符x首字母的ASSCII值

SQL> select ascii('荣'),ascii('Xiezhr'),ascii('xiezhr') from dual;
 
 ASCII('荣') ASCII('XIEZHR') ASCII('XIEZHR')
----------- --------------- ---------------
 51417 88 120

chr(n)函数

与ASSCII函数相反,将ASSCII码转换为字符

SQL> select chr(51417),chr(88),chr(120) from dual;
 
CHR(51417) CHR(88) CHR(120)
---------- ------- --------
荣 X x

4.2 获取字符串长度

length(string)

返回string所占的字节长度,单位是字节

SQL> select length('公众号XiezhrSpace') from dual;
 
 LENGTH('公众号XIEZHRSPACE')
---------------------------
 14

lengthb(string)

返回string所占的字符长度,单位是字符

SQL> select lengthb('公众号XiezhrSpace') from dual;
 
 LENGTHB('公众号XIEZHRSPACE')
----------------------------
 17

注意:上面例子,字符串是统一个,但是两个函数返回的值是不一样的。所以可以根据length(‘string’)=lengthb(‘string’)判断字符串是否含有中文

4.3 字符串截取函数

  • substr(x,start[,length])

  • 从start处开始,截取length个字符;

  • 缺省length,默认到结尾;

  • length为正,从左边截取;

  • length为负从右边截取

SQL> select substr('公众号XiezhrSpace',4) a,substr('公众号XiezhrSpace',4,11) b,substr('公众号XiezhrSpace',-11,11) c from dual;
 
A B C
----------- ----------- -----------
XiezhrSpace XiezhrSpace XiezhrSpace
  • substrb(x,start[,length])

函数与substr不同之处是按照字节截取

SQL> select substrb('公众号XiezhrSpace',7) a,substrb('公众号XiezhrSpace',7,17) b,substrb('公众号XiezhrSpace',-11,11) c from dual;
 
A B C
----------- ----------- -----------
XiezhrSpace XiezhrSpace XiezhrSpace

还有几个截取函数,但是不常用

  • substrc 以Unicode字符为单位截取
  • substr4 以UCS4代码点位单位
  • substr2 以UCS2代码点位单位

4.4 字符串连接函数

concat(x,y)

  • 效果和“||”连接一样,将字符串x和y连接起来
SQL> select concat('公众号','XiezhrSpace'),'公众号'||'XiezhrSpace' from dual;
 
CONCAT('公众号','XIEZHRSPACE') '公众号'||'XIEZHRSPACE'
------------------------------ -----------------------
公众号XiezhrSpace 公众号XiezhrSpace

4.5 字符串搜索函数

  • instr( string1, string2 [, start_position [, n ] ] )

  • 从start_position开始,目标字符串string2在源字符串string1中出现第n次的位置;

  • start_position为正表示从string1左边开始,为负表示从string1右边开始;

  • start_position、n可以去掉,表示string2在string1第一次出现位置

SQL> select instr('公众号XiezhrSpace','Xiezhr') a,instr('公众号XiezhrSpace','xiezhr') b,instr('公众号XiezhrSpace','Xiezhr',2,1) c,instr('公众号XiezhrSpace','Xiezhr',-1,1) d from dual;
 
 A B C D
---------- ---------- ---------- ----------
 4 0 4 4
  • instrb( string1, string2 [, start_position [, n ] ] )

以字节为单位搜索

SQL> select instrb('公众号XiezhrSpace','Xiezhr') a,instrb('公众号XiezhrSpace','xiezhr') b,instrb('公众号XiezhrSpace','Xiezhr',2,1) c,instrb('公众号XiezhrSpace','Xiezhr',-1,1) d from dual;
 
 A B C D
---------- ---------- ---------- ----------
 7 0 7 7

跟字符串截取函数一样,还有几个搜索函数

  • instrc 以Unicode字符为单位截取
  • instr4 以UCS4代码点位单位
  • instr2 以UCS2代码点位单位

4.6 字母大小写转换函数

  • upper(n)函数

将字符串n全部转换为大写

SQL> select upper('xiezhrspace'),upper('x') from dual;
 
UPPER('XIEZHRSPACE') UPPER('X')
-------------------- ----------
XIEZHRSPACE X
  • lower(n)函数

将字符串n全部转换为小写

SQL> select lower('X'),lower('XIEZHRSPACE') from dual;
 
LOWER('X') LOWER('XIEZHRSPACE')
---------- --------------------
x xiezhrspace

4.7 字符串替换函数

replace(char,search_string[,replacement_string])

  • char是目标字符串
  • earch_string是要替换的字符串
  • replacement_string参数可选,用它替换被搜索到的字符串,如果参数不用表示从char中删除earch_string字符串**
SQL> select replace('公众号XiezhrSpace','公众号','公众号:')a,replace('公众号XiezhrSpace','公众号')b from dual;
 
A B
------------------ -----------
公众号:XiezhrSpace XiezhrSpace

4.8 字符串填充函数

  • **rpad(exp1,n[,exp2]) **

在字符串exp1右边用字符串exp2填充,直到整个字符串长度为n为止;如果exp2参数没有,则以空格填充

SQL> select rpad('xiezhr',10,'0'),rpad('xiezhr',10) from dual;
 
RPAD('XIEZHR',10,'0') RPAD('XIEZHR',10)
--------------------- -----------------
xiezhr0000 xiezhr
  • lpad(exp1,n[,exp2])

在字符串exp1左边用字符串exp2填充,直到整个字符串长度为n为止;如果exp2参数没有,则以空格填充

SQL> select lpad('xiezhr',10,'0'),lpad('xiezhr',10) from dual;
 
LPAD('XIEZHR',10,'0') LPAD('XIEZHR',10)
--------------------- -----------------
0000xiezhr xiezhr

4.9 删除字符串首尾指定字符函数

trim([leading|trailing|both][trim_target from trim_source])函数

  • leading:删除trim_source的前缀字符
  • trailing:删除trim_source的后缀字符
  • both: 删除trim_source的前缀和后缀字符
  • trim_target:删除的指定字符串,默认是空格
  • trim_source:被操作字符串
SQL> select trim(trailing '公' from '公众号XiezhrSpace公') as a ,trim(leading '我' from '我公众号XiezhrSpace')as b ,trim(' 公众号XiezhrSpace ')as c from dual;
 
A B C
----------------- ----------------- -----------------
公众号XiezhrSpace 公众号XiezhrSpace 公众号XiezhrSpace

rtrim(char[,str])函数

与rpad函数相反,将char右边出现在str中的字符删掉,str参数不叫则默认删除空格

SQL> select rtrim('公众号XiezhrSpace我的','我的') a,'公众号XiezhrSpace ' b from dual;
 
A B
----------------- -------------------
公众号XiezhrSpace 公众号XiezhrSpace

ltrim(char[,str])函数

将char右边出现在str中的字符删掉,str参数不叫则默认删除空格

SQL> select ltrim('我的公众号XiezhrSpace','我的') a ,' 公众号XiezhrSpace' b from dual;
 
A B
----------------- --------------------
公众号XiezhrSpace 公众号XiezhrSpace

5 日期型函数

5.1 系统时区、日期、时间函数

dbtimezone函数

函数没有参数,返回数据库时区

SQL> select dbtimezone from dual;
 
DBTIMEZONE
----------
+00:00

sysdate函数

函数没有参数,可以得到系统当前日期

SQL> select sysdate from dual;
 
SYSDATE
-----------
2021-02-27

systimestamp函数

函数没有参数,返回系统时间。时间包含时区信息,精确到微秒。函数可以用于返回远端数据库服务器时间

SQL> select systimestamp from dual;
 
SYSTIMESTAMP
--------------------------------------------------------------------------------
27-2月 -21 03.06.44.403049 下午 +08:00

5.2 为日期加上指定月份函数

add_months(date,i)函数

  • date:指定日期
  • i: 要加的月份。i为正,在date日期上加i月;i为负,在date日期上减i月
SQL> select add_months(to_date('2021-01-01','yyyy-mm-dd'),10) a ,add_months(to_date('2021-01-01','yyyy-mm-dd'),-10) b from dual;
 
A B
----------- -----------
2021-11-01 2020-03-01

5.3 返回指定月份最后一天

last_day(date)函数

返回date日期的最后一天

SQL> select last_day(to_date('2020-01-01','yyyy-mm-dd')) a from dual;
 
A
-----------
2020-01-31

5.4 返回指定日期后一周的函数

next_day(date,char)函数

SQL> select sysdate, next_day(sysdate,'星期二') a from dual;
 
SYSDATE A
----------- -----------
2021-02-27 2021-03-02

5.5 提取指定日期特定部分函数

extract(datetime)函数

从给定的datetime中得到年、月、日、时、分、秒

SQL> select sysdate "date",
 2 extract(year from sysdate)"year",
 3 extract(month from sysdate)"month",
 4 extract(day from sysdate)"day",
 5 extract(hour from systimestamp)"hour",
 6 extract(minute from systimestamp)"minute",
 7 extract(second from systimestamp)"second"
 8 from dual;
 
date year month day hour minute second
----------- ---------- ---------- ---------- ---------- ---------- ----------
2021-02-27 2021 2 27 7 25 3.72008

5.6 获取两个日期之间月份

month_between(date1,date2)

获取data1,date2日期之间的月份

SQL> select months_between(to_date('2021-03-02','yyyy-mm-dd'),to_date('2020-01-02','yyyy-mm-dd')) a from dual;
 
 A
----------
 14

5.7 日期四舍五入、截取函数

ronud(date[,fmt])

将date舍入到fmt指定形式,如果fmt参数不加,date被处理到最近一天

SQL> select round(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss'),'month') a,
 2 round(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss'),'day') b,
 3 round(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss')) c
 4 from dual;
 
A B C
----------- ----------- -----------
2020-01-01 2020-01-05 2020-01-04

trunc(date[,fmt])

将date截取到fmt指定形式,如果fmt参数不加,date被处理到最近一天

SQL> select trunc(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss'),'month') a,
 2 trunc(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss'),'day') b,
 3 trunc(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss')) c
 4 from dual;
 
A B C
----------- ----------- -----------
2020-01-01 2019-12-29 2020-01-03

6 转换函数

6.1 数据类型转换函数

一般用于数字与字符、字符与日期之间转换

SQL> select cast(sysdate as varchar2(12)) a,cast('123' as number) b, cast(123 as varchar2(3)) c from dual;
 
A B C
------------ ---------- ---
27-2月 -21 123 123

6.2 将字符串转换字符集

convert(char,a[,b])函数

  • char:待转换的字符串
  • a:转变后的字符集
  • b: char原来字符集
SQL> select convert('测试','US7ASCII','ZHS16GBK') a from dual;
 
A
------------------------------
??

6.3 数值转换成字符串

to_char(number[,fmt])函数

将数值、日期按照指定格式转换成字符串

SQL> select to_char(3.45)a,to_char(3.45,'99.9')b,to_char(sysdate,'yyyy-mm-dd')c from dual;
 
A B C
---- ----- ----------
3.45 3.5 2021-02-27

6.4 字符转日期

to_date(char,fmt)函数

将字符按照fmt格式转换

SQL> select to_date('2020-12-01','yyyy-mm-dd') a from dual;
 
A
-----------
2020-12-01

6.5 符串转数字函数

to_number(char[,fmt])函数

将字符串char 转换为数值

SQL> select to_number('34.562','9999.999') from dual;
 
TO_NUMBER('34.562','9999.999')
------------------------------
 34.562

7 null 函数

null值我们经常会遇到的,这时候我们就要学会怎么处理null值

7.1 返回表达式为null的函数

coalesce(expr)函数

返回列表中第一个不为null的表达式,如果都为null,则返回null

SQL> select coalesce(null,'9',null,'b')a,coalesce(null,null,null,null)b from dual;
 
A B
- -
9 

7.2 排除指定条件函数

lnnvl(condition)函数

返回满足condition条件以外的数据,包含null的条件,一般放到where语句中

SQL> select * from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 100
 1 1002 栗子 3
 1 1004 香蕉 34
 1 1004 西瓜 34
 
SQL> select * from productinfo where lnnvl(qty<100);
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 100

7.3 替换null值函数

nvl(expr1,expr2)函数

  • 当expr1为null时,返回expr2的值
  • 当expr1不为null时,返回expr1的值
SQL> select * from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 
 1 1002 栗子 3
 1 1004 香蕉 34
 1 1004 西瓜 34
 
SQL> select id,productcode,productname, nvl(qty,0) qty from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 0
 1 1002 栗子 3
 1 1004 香蕉 34
 1 1004 西瓜 34

nvl2(expr1,expr2,expr3)

  • 当expr1为null时,返回expr3
  • 当expr1不为null时,返回expr2
SQL> select * from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 
 1 1002 栗子 3
 1 1004 香蕉 34
 1 1004 西瓜 34
 
SQL> select id,productcode,productname, nvl2(qty,55,0) qty from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 0
 1 1002 栗子 55
 1 1004 香蕉 55
 1 1004 西瓜 55

8 聚合函数

8.1 求平均值函数

avg(expr)函数

该函数可以求指定列的平均值

SQL> select * from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 
 1 1002 栗子 3
 1 1004 香蕉 34
 1 1004 西瓜 34
SQL> select avg(qty) from productinfo;
 
 AVG(QTY)
----------
23.6666666

8.2 求记录数函数

count(expr)

SQL> select * from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 
 1 1002 栗子 3
 1 1004 香蕉 34
 1 1004 西瓜 34
 
SQL> select count(1) from productinfo;
 
 COUNT(1)
----------
 4

8.3 求最大值函数

max(expr)

 
SQL> select * from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 
 1 1002 栗子 3
 1 1004 香蕉 34
 1 1004 西瓜 34
 
SQL> select max(qty) from productinfo;
 
 MAX(QTY)
----------
 34

8.4 求最小值

min(expr)

SQL> select * from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 
 1 1002 栗子 3
 1 1004 香蕉 34
 1 1004 西瓜 34
 
SQL> select min(qty) from productinfo;
 
 MIN(QTY)
----------
 3

8.5 求和函数

sum(expr)

SQL> select * from productinfo;
 
 ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
 1 1001 苹果 
 1 1002 栗子 3
 1 1004 香蕉 34
 1 1004 西瓜 34
 
SQL> select sum(qty) from productinfo;
 
 SUM(QTY)
----------
 71

9 其他函数

decode(expr,search,result,defalut)函数

当expr满足search时候返回result,改过程可以重复多个,如果都没有匹配的结果则返回default

SQL> select t.*, decode(qty,null,'不足',3,'不足','充足')a from productinfo t;
 
 ID PRODUCTCODE PRODUCTNAME QTY A
---------- ----------- ------------------------------ ---------- ----
 1 1001 苹果 不足
 1 1002 栗子 3 不足
 1 1004 香蕉 34 充足
 1 1004 西瓜 34 充足
作者:xiezhr原文地址:https://www.cnblogs.com/xiezhr/p/17343493.html

%s 个评论

要回复文章请先登录注册