第二章 Oracle 的函数

十五.Oracle的函数
Oracle 数据库中主要使用两种类型的函数:
①　单行函数:对每一个函数应用在表的记录中时,只能输入一行数据作为参数,返回一个结果, 比如:MOD(x,y)返回 x 除以 y 的余数(x 和 y 可以是两个整数,也可以是表中的整数列)。
常用的单行函数有:
字符函数:对字符串varchar2操作。
数字函数:对数字number进行计算,返回一个数字。
转换函数:可以将一种数据类型转换为另外一种数据类型。
日期函数:对日期和时间进行处理。
②　聚合函数:聚合函数同时可以对多行数据进行操作,并返回一个结果。比如 sum, max,min
字符串函数[]：

函数
说明
ASCII(x)
返回第一个字符 x 的 ASCII 码。
大写字母A是65，小写字母a 是97

Select ascii(‘SMITH’) from emp where ename=’SMITH’; 结果：83
Concat
(x,y) 连接字符串x和y。 只能有两个参数 ，
通过嵌套可连接多个
‘||’可以连接多个
select concat(concat(‘SMITH’,’&’),’%’) from emp where ename=’SMITH’; 结果：SMITH&%
Instr
(x,y ,z,a)
索引 在 x 中查找y instr(x,str)=3
从第z（某个位置） 个字符开始,
a次出现的位置。
返回第一个字符出现的位置信息
length(x) 返回 x 的长度。 汉字字母空格都是1个长度
lower(x) x 转换为小写。 针对26个英文字母有效，汉字原样输出
upper(x) x 转换为大写。针对26个英文字母有效，汉字原样输出
Initcap() 首字母大写，后面全部是小写
reverse() 反转,不能反转中文，会变为乱码，无效
Ltrim
(x[,trim_str]) 把 x 的左边截去 trim_str 字符串,如缺省则截去空格。
去除左侧的空格。

Rtrim
(x[,trim_str]) 去除右侧的空格
Trim
([trim_str FROM] x) 把 x 的两边截去 trim_str 字符串,如缺省则截去空格。
去除两侧的空格 【字符 from 字段】
Replace
(x,old,new) 在 x字段 中查找 old某个字符串,并替换为 new新的。
Substr
(x,y,z) 返回 x 的字串,从y 处开始,截取 z个长度的字符。
Substr(x,start,length)=’A’
如缺省 length,默认到结尾。 从字段中截取某个字符串
select substr(‘SMITH’,2,4) from emp where ename = ‘SMITH’
结果： MITH
Select ename from emp where ename = upper(‘&ename’)
会弹出输入名字的输入框
Select ltrim(‘== =HELLO===’, ‘=’) from dual
结果： 空格=HELLO===
Select ltrim(‘ASS ASA====’,’AB’) from dual
结果： SS ASA====
Select ltrim(‘ASS ASA====’,’BAS’) from dual
结果： 空格ASA====
Select ltrim(‘ASSASA====’,’BAS’) from dual
结果： ====
数字函数：

函数
说明
示例
abs(x) x 绝对值 Select ABS(-3) from dual;
acos(x) x 的反余弦 ACOS(1)=0
cos(x) 余弦 COS(1)=1.57079633
ceil(x) 上入整数 CEIL(5.8)=6 向上取整
floor(x 下舍整数 FLOOR(5.8)=5 向下取整
Log
(x,y) x的几次方是y LOG(2,8)=3

power(x,y)
x的y次幂是某个结果值
POWER(2,3)=8

sqrt(x) x 的平方根 SQRT(4)=2
Mod
(x,y) x除以y的余数 MOD(8,2)=0 8是偶数
可用来判断奇偶数

Round
(x[,y]) ROUND
(x[,y])
x在第y位的四舍五入
ROUND(3.456,2)=3.46
Y 一般是正整数
y如果是负数，
就是小数点往前数的四舍五入
ROUND(x)
不保留小数位的四舍五入

Trunc
(x[,y]) x在第y位截断
（忽略余数）
直接去掉小数点后面的，变为0 TRUNC(3.456,2)=3.45

日期函数：
add_months(d,n) d 表示日期,n 表示要加的月数，2月28/29天 自动转
last_day
(d) 返回指定日期当月的最后一天
在其他数据库中没有last_day函数
如要计算某个月的最后一天，需要用该月使用add_months加一个月，
再减去得到的下一个月的日期，获得要计算月的最后一天。

Round
(d[,fmt]) 四舍五入日期值,d 是日期,fmt 是格式模型。
默认 fmt 为 ddd,即当天。
前半天舍去,后半天作为第 二天。

1）如果fmt为“year”则舍入到某年的1月1日
即前半年舍去,后半年作为下一年。
2）如果fmt为“month”则舍入到某月的1日
即前月当月的15日之前 舍去,后半月作为下一月。
3）如果fmt为“day”则舍入到最近周的周日
即上半周舍去,下半周作为下一周周日。
Trunc
(d[,fmt]) 直接截取到对应格式的1日。
Extract (fmt FROM d) 取日期中的特定部分。

fmt 为:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。
其中 年月日 可以为 DATE 类型匹配,
也可以与 TIMESTAMP 时间戳 类型匹配;
但是 时分秒 必须与 TIMESTAMP 类型匹配。
HOUR 匹配的结果中没有加上时区,因此在中国运行的结果小 8 小时。

months_between(x,y) 计算两个日期之间 相减后的月数；

转换函数：
To_char
(d|n[,fmt]) 把日期和数字转换为制定格式的字符串。
SELECT TO_CHAR(SYSDATE,’YYYY”年”MM”月”DD”日”HH24:MI:SS’) “date”
FROM DUAL

参数
示例
说明
9
999
指定位置处显示数字。
. 9.9 指定位置返回小数点
, 99,99 指定位置返回一个逗号
999 数字开头返回一个美元符号
EEEE 9.99EEEE 科学计数法表示
L L999 数字前加一个本地货币符号
PR 999PR 如果数字是负数则用尖括号进行表示

To_date
(x ,fmt) 把x字符串以 fmt 格式转换为一个日期类型
只能针对字符串类型转换

To_number
(x,fmt) 把x（数字）以 fmt 格式转换为一个没有任何特殊符号的数字
只能针对字符串类型转换
其他单行函数：
NVL
(x,value) 如果 x 为空,返回 value,否则返回 x。
NVL2
(x,
value1,
value2)
如果 x 为空,返回 value2,否则返回 value1。
decode (x,value,
value1,
value2) 当x的列值为value 时，查询结果值为value1
否则值为value2
Case..end 简单Case函数和Case 搜索函数
也用于：update+表名 set列=case..
只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

===============================================================