第三节----单行函数

|-如下以employees表为例

①单行函数:

操作数据对象接受参数返回一个结果，只对一行进行变换，
每行返回一个结果，可以转换数据类型，可以嵌套，参数
可以是一列或一个值。

②大小写控制函数

select lower('ATGUIGUJAVA'),UPPER('AtGuiGu Java'),initcap('AtGuiGu java') from dual;
select * from employees where lower(last_name) = 'king';
select * from employees where upper(last_name) = 'KING'

③字符控制函数

select concat('hello','world'),substr('helloworld',2,4),length('helloworld')from dual;
select instr('hellowJava','l')from dual;
select employee_id,last_name,lpad(salary,10,'*')from employees;
select employee_id,last_name,lpad(salary,10,' '),rpad(salary,10,' ')from employees
select trim('h' from 'helloworld')from dual;--去除首尾
select replace('abcdab','b','m')from dual;

④数字函数

ROUND: 四舍五入
ROUND(45.926, 2)--------------->45.93

TRUNC:	 截断
TRUNC(45.926, 2)--------------->45.92

MOD: 求余
MOD(1600, 300)--------------->100

select round(435.45,2),round(435.45),round(435.45,-2)from dual;
select trunc(435.45,1),trunc(435.45),trunc(435.45,-1)from dual;
select mod(1100,300)from dual;

⑤日期

在日期上加上或减去一个数字结果仍为日期。

两个日期相减返回日期之间相差的天数。
日期不允许做加法运算，无意义

可以用数字除24来向日期中加上或减去天数

select sysdate,sysdate+1,sysdate-3 from dual;
--多少天
select employee_id,last_name,sysdate-hire_date workde_days from employees;
select employee_id,last_name,trunc(sysdate-hire_date) workde_days from employees;
--多少月
select employee_id,last_name,(sysdate-hire_date)/30 workde_months from employees;

⑥日期函数

select employee_id,last_name,(sysdate-hire_date)/30,months_between(sysdate,hire_date) from employees;
select add_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期四') from dual;
--hire_date是每个月倒数第二天有哪些
select last_name,hire_date from employees where  hire_date = last_day(hire_date)-1;
select last_day(sysdate) from dual;
select round(sysdate,'month'),round(sysdate,'mm'),trunc(sysdate,'hh') from dual;

⑦转换函数

Oracle 自动完成下列转换：
隐式转换：

select '12'+2 from dual;
select sysdate + '2' from dual;

显式转换：

日期转换：

select employee_id,hire_date from employees where to_char(hire_date,'yyyy-mm-dd')= '1994-06-07';
select employee_id,hire_date from employees where to_date('1994-06-07','yyyy-mm-dd') = hire_date;

select to_char(1234567.89,'999,999,999.99') from dual;
select to_char(1234567.89,'000,000,999.99') from dual;
select to_char(1234567.89,'L000,000,999.99') from dual;
select to_number('￥001,234,567.89','L000,000,999.99') from dual;
select to_number('$001,234,567.89','$000,000,999.99') from dual;

⑧通用函数

这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)

--年薪
select employee_id,last_name,salary*12*(1+nvl(commission_pct,0))"annual sal" from employees;

--输出last_name,department_id，当department_id为null时，显示‘没有部门’
select last_name,nvl(to_char(department_id,'999999'),'没有部门') from employees;
select last_name,nvl(to_char(department_id),'没有部门') from employees;

--查询员工的奖金率，若为空，返回0.01,若不为空，返回实际奖金率+0.015
select last_name,commission_pct,nvl2(commission_pct,commission_pct+0.015,0.01) from employees;

⑨条件表达式
–CASE 表达式：

--查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
select employee_id,last_name,department_id,case department_id when 10 then salary * 1.1 when 20 then salary * 1.2 else salary * 1.3 end new_sal from employees where department_id in(10,20,30);

–DECODE 函数：

select employee_id,last_name,department_id,decode(department_id,10,salary*1.1,20,salary*1.2,salary)new_sal from employees where department_id in(10,20,30);