类型转换和其他函数

SQL> select to_char(salary, '$999,999.00') from employees;

SQL> select last_name, to_char(hire_date, 'dd-Mon-RR') from employees;

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

SQL> select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss AM') from dual;

 

Fm 去0

 

SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');

 

SQL> select to_number('$123,456.78', '$999,999.00') from dual;

 

 

 

 

 

练习：

查询2006年入职员工：

select last_name

from employees

where hire_date between to_date('2006-01-01', 'yyyy-mm-dd')

and  to_date('2006-12-31', 'yyyy-mm-dd');

 

select last_name

from employees

where to_char(hire_date, 'yyyy')='2006';

 

select last_name

from employees

where extract(year from hire_date)=2006;

 

--不推荐

select last_name

from employees

where hire_date like '2006%';

 

查询历年9月份入职的员工：

select last_name

from employees

where to_char(hire_date, 'mm')='09';

 

select last_name

from employees

where extract(month from hire_date)=9;

 

其他函数：

nvl：

nvl(val1, val2)

if val1 is not null

then

    return val1;

else

    return val2;

SQL> select last_name, salary*12*(1+nvl(commission_pct, 0)) total_salary from employees;

 

练习：

显示所有员工部门编号，没有部门的显示“未分配部门”

 

case和decode：

IT_PROG +1000

SA_REP +1500

ST_CLERK +2000

其他人工资不变

 

select salary+1000 from employees where job_id='IT_PROG';

 

select last_name, job_id, salary,

case job_id

  when 'IT_PROG' then salary+1000

  when 'SA_REP' then salary+1500

  when 'ST_CLERK' then salary+2000

  else salary

end new_salary

from employees;

 

select last_name, job_id, salary,

decode( job_id,

  'IT_PROG', salary+1000,

  'SA_REP',  salary+1500,

  'ST_CLERK', salary+2000,

  salary) new_salary

from employees;

练习：

按照员工工资，对员工分级显示：

A 20001-25000

B 15001-20000

C 10001-15000

D 5001-10000

E 0-5000