Oracle 条件表达式 CASE DECODE

Oracle 条件表达式

在 SQL 语句中使用IF-THEN-ELSE 逻辑使用两种方法:

1. CASE 表达式：

	CASE expr WHEN comparison_expr1 THEN return_expr1
	         [WHEN comparison_expr2 THEN return_expr2
	          WHEN comparison_exprn THEN return_exprn
	          ELSE else_expr]
	END

	例子： 查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 
	30 号部门打印其工资的 1.3 倍数。

	SELECT last_name, job_id, salary,
	       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
	                   WHEN 'ST_CLERK' THEN  1.15*salary
	                   WHEN 'SA_REP'   THEN  1.20*salary
	       ELSE      salary END     "REVISED_SALARY"
	FROM   employees;

2. DECODE 函数：

	DECODE(col|expression, search1, result1 ,
	      			   [, search2, result2,...,]
	      			   [, default])
      			   
	例子1：
	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;
	
	例子2：      		
	SELECT last_name, salary,
	       DECODE (TRUNC(salary/2000, 0),
	                         0, 0.00,
	                         1, 0.09,
	                         2, 0.20,
	                         3, 0.30,
	                         4, 0.40,
	                         5, 0.42,
	                         6, 0.44,
	                            0.45) TAX_RATE
	FROM   employees
	WHERE  department_id = 80;