Oracle SQL 单行函数

Oracle SQL 单行函数

开发工具与关键技术：Oracle、sql*plus、PLSQL、Developer
作者：王福驰
撰写时间：2019年04月01日

单行函数:
• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以转换数据类型
• 可以嵌套
• 参数可以是一列或一个值
一、 大小写控制函数
全大写 upper（）
例子：查询所有员工大写名

全小写 lower（）
例子：查询所有员工小写名

首字母大写 initcap（）

二、 字符控制函数
字符控制函数分别有：合并(CONCAT),截取(SUBSTR),字符长度(LENGTH),截取字符所在的位置(INSTR),左填充(LPAD),右填充( RPAD),去字符( TRIM),代替( REPLACE),
例子：写一个查询,用首字母大写，其它字母小写显示雇员的 全名，显示名字的长度，并给每列一个适当的标签，前10位：编号,不足部分用填充,左对齐，后10位：工资，不足部分用填充,右对齐，条件是满足所有雇员名字的开始字母是A 的雇员，
select concat(first_name,last_name) “全名”, length((first_name || last_name)) “长度” , LPAD (first_name,10,’’)||RPAD (salary,10,’’) “填充”,trim (‘h’from’hello’)
from employees where substr (first_name || last_name,1) like ‘A%’ and instr(first_name || last_name,‘A’,1,1)>0

三、 数字函数
数字函数分别有：四舍五入(ROUND)，截断(TRUNC)，求余(MOD)
查询员工一共入职多少天,当前时间-入职
select employee_id,first_name,ROUND((SYSDATE-hire_date),0),trunc((SYSDATE-hire_date),0),mod(trunc((SYSDATE-hire_date),0),30) from employees;

四、 日期函数
日期函数分别有：两个日期相减得到的月数(MONTHS_BETWEEN), 向指定日期相加若干个月(ADD_MONTHS),指定日期的下一个星期几对应的日期(NEXT_DAY),本月的最后一天(LAST_DAY), 日期四舍五入(ROUND)，日期截断 (TRUNC)
例子：计算2000年1月1日到现在有多少月，多少周(四舍五入),如果员工试用期6个月，查询职位不是MANAGER的员工姓名，入职日期,入职日期后的第一个星期一,入职当月的最后一天日期
select MONTHS_BETWEEN( sysdate, ‘01-1月-00’) “月” ,round( MONTHS_BETWEEN( sysdate, ‘01-1月-00’) /7 ,2) “周”,next_day(hire_date,‘星期一’),last_day(hire_date),
add_months(hire_date,6)
from employees where first_name!= ‘MANAGER’

五、 转换函数
转换函数分别有：to_char(函数对日期和数字的转换)，to_date(函数对字符的转换)，to_number(函数对字符的转换)
例子：计算2000年1月1日到现在有多少周（四舍五入）
select round((to_date(to_char(sysdate,‘YYYY-MM-DD’),‘YYYY-MM-DD’)-to_date(‘2000-01-01’,‘YYYY-MM-DD’))/7) from dual;

六、 通用函数
通用函数分别有NVL (expr1, expr2)、NVL2 (expr1, expr2, expr3)、NULLIF (expr1, expr2)、COALESCE (expr1, expr2, …, exprn)
例子：查询员工的姓名及其经理编号，要求对于没有经理的显示“没有经理”字符串。
select concat(first_name ,last_name), NVL (to_char(manager_id),‘没有经理’) from employees;

七、 Case函数
在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。

八、 嵌套函数
单行函数可以嵌套，嵌套函数的执行顺序是由内到外
各个部门中 最高工资中最低的那个部门的