条件分支—CASE表达式和DECODE函数

CASE表达式单条件（单列）来走分支

看下面的加薪语句
SQL> SELECT last_name, job_id, salary,
  2         CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
  3                     WHEN 'ST_CLERK' THEN  1.15*salary
  4                     WHEN 'SA_REP'   THEN  1.20*salary
  5         ELSE      salary END     "REVISED_SALARY"
  6  FROM   employees;

1.CASE表达式

通过使用类似IF-THEN-ELSE语句的方法实现条件执行

 

CASE表达式多条件（条件有优先级）来走分支

department_id = 10表示是核心部门
job_id in ('ST_CLERK','SA_REP')表示重要岗位
sysdate-hire_date >4745是老员工
看下面的加薪语句
SQL> ed
已写入 file afiedt.buf
  1  SELECT last_name, job_id, salary,
  2         CASE WHEN department_id = 10 THEN  1.3*salary
  3                     WHEN job_id in ('ST_CLERK','SA_REP') THEN  1.2*salary
  4                     WHEN sysdate-hire_date >4745  THEN  1.10*salary
  5         ELSE      salary END     "REVISED_SALARY"
  6* FROM   employees
SQL> /

 

2.DECODE函数

类似于SWITCH……CASE
SELECT last_name, job_id, salary,
       DECODE(job_id, 'IT_PROG',  1.10*salary,
                      'ST_CLERK', 1.15*salary,
                      'SA_REP',   1.20*salary,
              salary)
       REVISED_SALARY
FROM   employees;