Oracle-函数

【1.nvl/nvl2】

--select employee_id,last_name,salary*12*(1+nvl(commission_pct,0)) from employees
//如果commission_pct存在，则为其自身，否则赋值为0；

--select employee_id,commission_pct,nvl2(commission_pct,commission_pct+0.15,0.01) from employees
//如果commission_pct存在，则返回值为commission_pct+0.15，否则为0.01

【2. case…when…then…when…then…else…end】

select employee_id,department_id,case department_id when 10 then salary*1.1 when 20 then salary*1.2 else salary*1.3 end as "new_sal" //赋别名 from employees where department_id in (10,20,30)

【3.decode】

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

//根据department_id进行判断，如果为10，则其salary*1.1；如果为20，则其salary*1.2；其他salary不变，并赋别名--new_sal

【4.to_char】

select to_char(sysdate,'yyyy"年"mm"月"dd"日" HH:mi:ss') from dual

select to_char(1234567.89,'999,999,999.99') from dual

【5.to_number】

select to_number('1,234,567.89',999.99')+100 from dual
//注意前后两个数的取值范围，后者一定要比前者大

【6.|| 拼接符】

select last_name || ' earns' ||to_char(salary,'$99999')||' monthlym,but wants to earn'||
to_char(3*salary,'$99999') "Dream Salary" from employees

//控制台将会输出一句话

【7.group by…having】

//按照部门ID进行分组，并添加过滤条件，且使用了子查询

select department_id,min(salary) from employees group by department_id having min(salary) > (select min(salary) from employees where department_id ='50' )

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!