第五章常用的系统函数
【目标】
了解Oracle中函数的分类
掌握Oracle中常用的系统函数
【内容】
一Oracle中函数分类
SQL函数分为单行函数和多行函数
按照功能分类: 字符函数,数学函数,日期函数,聚合函数,转换函数，系统函数
二Oracle中常用的系统函数
字符函数
Oracle中专门用来处理字符,常用的字符函数如下:
initcap(char): 首字母大写，后面的字符小写 ‘ABCD’==initcap(‘ABCD’) Abcd
lower(char):将字符串全部转换为小写 ’ABCD’=‘abcd’
upper(char):将字符串全部转换为大写 ‘abcd’‘ABCD’
length(char)：返回字符串长度‘abcd’=4
substr(char,m,n):取得字符串的子串，m代表开始位置，n代表结束位置
replace(char,s1,s2):将字符串char中s1替换为s2
instr(char,s1[,position])：查找字符串char中是否包含S1，postion代表从第几个字符开始查找。如果找到s1，就返回S1的位置；如果没有找到，返回0.
查找emp表中所有员工的姓名，要求名字以小写字母显示
Selectlower(ename) from emp;
查找emp表中所有员工的姓名，要求名字第一个字母大写，后面的字母小写。
Selectinitcap(ename) from emp;
方法二：
第一步：截取第一个字母，变大写
Selectupper(substr(ename,1,1)) from emp;
第二步：截取剩下的字符，变小写
Selectlower(substr(ename,2,length(ename)-1)) from emp;
第三步：合并第一步和第二步
Selectupper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1))from emp;
查询emp表中姓名是5个字符的员工名字。
Select enamefrom emp where ename like ‘_____’;
Select enamefrom emp where length(ename) = 5;
查询emp表中员工命名，要求显示姓名的前面3个字母
查询emp1表中员工的姓名，如果姓名中包含A，替换为‘abc‘
Selectreplace(ename,’A’,’abc’)from emp;
数学函数
round(n,[m]):四舍五入，没有m表示四舍五入到整数位
m为正数，四舍五入到小数点后面m位
m为负数，四舍五入到小数点前面m位
trunc(n,[m]): 截取字符串，没有m表示截取到整数位
m为正数，截取到小数点后面m位
m为负数，截取到小数点前面m位
floor(n)：返回小于或者等于n的最大整数，向下取整
ceil(n)：返回大于或者等于n的最大整数，向上取整
abs(n)：返回n的绝对追
power(m,n)：返回m的n次幂
mod(m,n)：取模
假设每个月30天，查询出emp表中员工姓名，工资，日薪(sal除以30，四舍五入到小数点后两位)
Selectename,sal,round(sal/30,2) from emp;
Trunc
Selectename,sal,trunc(sal/30,2) from emp;
floor向下取整，ceil向上取整
select floor(10.9) from dual;
Select ceil(10.9) fromdual;
abs(n) 取绝对值
select abs(-10) from dual;
power(m,n)： m的n次幂 power(5,2)
select power(5,2) from dual;
日期函数
日期函数用来处理日期的函数。
Sysdate:系统日期
Months_between(d1,d2):两个日期之间的月数
Add_months(d,n):在指定的日期上加上n个月
Last_day(d):返回指定日期所在月份的最后一天
round round(d,[fmt])
round函数返回日期值,此日期四舍五入为格式模型指定的单位。
round(d):指定格式为日
round(d, ‘year’)：指定格式为年
round(d,’month’)：指定格式为月
round(d,’day’)：指定格式为周
trunc trunc(d,[fmt])
trunc函数将指定日期截断为由格式模型指定的单位的日期
trunc(d):指定格式为日
trunc(d, ‘year’)：指定格式为年
trunc(d,’month’)：指定格式为月
trunc(d,’day’)：指定格式为周
案例：
查询当前日期与2007年5月29日之间有多少个月
Selectmonths_between(sysdate, ’29-5月-07’)from dual;
查询当前日期加上2个月之后的日期
Selectadd_months(sysdate, 2) from dual;
查询当前日期，和当前月份的而最后一天
Selectsysdate, last_day(sysdate) from dual;
round:
select sysdate,round(sysdate) from dual; 以日为标准四舍五入
select sysdate,round(sysdate,'year') from dual;
select sysdate,round(sysdate,‘month’) from dual;
select sysdate,round(sysdate,‘day") from dual;
trunc:
select sysdate, trunc(sysdate) from dual;
select sysdate, trunc(sysdate,”year”) from dual;
select sysdate, trunc(sysdate,”month”) from dual;
select sysdate, trunc(sysdate,”day”) from dual;
查询入职时间超过6个月的员工信息
Select *from emp where add_months(hiredate, 6) < sysdate;
查询emp表中工龄满30年的员工信息
Select *from emp where add_months(hiredate, 30*12) <= sysdate;
查询emp表中每个员工的姓名，入职日期，入职天数（取整数）
Selectename,hiredate, trunc(sysdate-hiredate) from emp;
查询emp表中每个月倒数第三天入职的员工信息
Select *from emp where last_day(hiredate)-2 = hiredate;
转换函数
转换函数是将数据从一种数据类型转换为另一种数据类型。
To_char():
修改系统的日期格式：altersession set nls_date_format=’yyyy-MM-dd’;
查询emp表中员工的姓名，入职日期，日期上加上时分秒，格式：yyyy-mm-dd hh24:mi:ss
Selectename, to_char(hiredate, ‘yyyy-mm-ddhh24:mi:ss’)from emp;
查询emp表中在1981年入职的员工信息
Select ename, hiredatefrom emp where to_char(hiredate,’yyyy’)= 1981;
查询emp表中员工的姓名，工资，并且在工资前面加上美元符号$
Selectename,to_char(sal,’$9999.99’)from emp;
Selectename,to_char(sal,’L9999.99’)from emp; L 本地货币符号，以电脑操作系统为准
To_date():
Selectto_date(‘19810526’,’yyyy-mm-dd’)from dual;
To_number():
Selectto_number(‘1911’)from dual;
分组函数(聚合函数)
Max(),min(),avg(),sum(),count()
查询emp表中最高工资 max()
查询emp表中最低工资 min()
查询emp表中的平均工资avg()
查询emp表中工资总和 sum()
查询emp表中记录的数量 count()
系统函数
用来显示数据库系统信息
sys_context('userenv’,参数)
常用参数：
terminal：当前会话客户对应的终端提示符
language: 语言
db_name: 数据库名
nls_date_format: 当前会话客户对应的日期格式
session_user：当前会话客户对应的数据库用户名
current_schema：当前会话客户对应的方案名
host：数据库所在的主机名
查询当前数据库的名字
Selectsys_context(‘userenv’,‘db_name’)from dual;
