oracle学习85-oracle之单行函数2

 

--通用函数
--求公司员工的年薪 nvl 没有值代替
select employee_id,last_name,salary*12*(1+nvl(commission_pct,0)) "annnal salary"
from employees

运行结果

--输出department_id为空时候 没有部门
select last_name,nvl(to_char(department_id,'99999'),'没有部门')
 from employees

运行结果

 

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

运行结果

 
--条件表达式
--查询部门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)