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])函数
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 字符串截取函数
SQL> select substr('公众号XiezhrSpace',4) a,substr('公众号XiezhrSpace',4,11) b,substr('公众号XiezhrSpace',-11,11) c from dual; A B C----------- ----------- -----------XiezhrSpace XiezhrSpace XiezhrSpace
复制代码
函数与 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
复制代码
还有几个截取函数，但是不常用
4.4 字符串连接函数
concat（x,y）
SQL> select concat('公众号','XiezhrSpace'),'公众号'||'XiezhrSpace' from dual; CONCAT('公众号','XIEZHRSPACE') '公众号'||'XIEZHRSPACE'------------------------------ -----------------------公众号XiezhrSpace 公众号XiezhrSpace
复制代码
4.5 字符串搜索函数
从 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
复制代码
以字节为单位搜索
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 字母大小写转换函数
将字符串 n 全部转换为大写
SQL> select upper('xiezhrspace'),upper('x') from dual; UPPER('XIEZHRSPACE') UPPER('X')-------------------- ----------XIEZHRSPACE X
复制代码
将字符串 n 全部转换为小写
SQL> select lower('X'),lower('XIEZHRSPACE') from dual; LOWER('X') LOWER('XIEZHRSPACE')---------- --------------------x xiezhrspace
复制代码
4.7 字符串替换函数
replace(char,search_string[,replacement_string])
SQL> select replace('公众号XiezhrSpace','公众号','公众号:')a,replace('公众号XiezhrSpace','公众号')b from dual; A B------------------ -----------公众号:XiezhrSpace XiezhrSpace
复制代码
4.8 字符串填充函数
在字符串 exp1 右边用字符串 exp2 填充，直到整个字符串长度为 n 为止；如果 exp2 参数没有，则以空格填充
SQL> select rpad('xiezhr',10,'0'),rpad('xiezhr',10) from dual; RPAD('XIEZHR',10,'0') RPAD('XIEZHR',10)--------------------- -----------------xiezhr0000 xiezhr
复制代码
在字符串 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)函数
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)函数
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)
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 充足
复制代码
评论