目录1、序言2、Oracle函数分类3、数值型函数3.1 求绝对值函数3.2 求余函数3.3 判断数值正负函数3.4 三角函数3.5 返回以指定数值为准整数的函数3.6 指数、对数函数3.7 四舍五入函数4 字符型函数4.1 ASSCII与字符转换函数4.2 获取字符串长度4.3 字符串截取函数4.4 字符串连接函数4.5 字符串搜索函数4.6 字母大小写转换函数4.7 字符串替换函数4.8 字符串填充函数4.9 删除字符串首尾指定字符函数5 日期型函数5.1 系统时区、日期、时间函数5.2 为日期加上指定月份函数5.3 返回指定月份最后一天5.4 返回指定日期后一周的函数5.5 提取指定日期特定部分函数5.6 获取两个日期之间月份5.7 日期四舍五入、截取函数6 转换函数6.1 数据类型转换函数6.2 将字符串转换字符集6.3 数值转换成字符串6.4 字符转日期6.5 符串转数字函数7 null 函数7.1 返回表达式为null的函数7.2 排除指定条件函数7.3 替换null值函数8 聚合函数8.1 求平均值函数8.2 求记录数函数8.3 求最大值函数8.4 求最小值8.5 求和函数9 其他函数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        西瓜                                   34SQL> 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 充足

标签：