Oracle之CASE表达式和CASE语句

CASE表达式在Oracle8i被加入到SQL中，Oracle9i对其进行了扩展，使CASE表达式和CASE语句可以用在PL/SQL中。值匹配CASE表达式，较D

CASE表达式在Oracle8i被加入到SQL中，Oracle9i对其进行了扩展，使CASE表达式和CASE语句可以用在PL/SQL中。值匹配CASE表达式
较DECODE函数而言，CASE表达式更灵活。其最简单的应用方式时当匹配成功时返回特定值。SELECT ename, empno,
(CASE deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END) department
FROM emp
ORDER BY ename;CASE表达式也可以用在PL/SQL中。
SET SERVEROUTPUT ON
DECLARE
deptno NUMBER := 20;
dept_desc VARCHAR2(20);
BEGIN
dept_desc := CASE deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END;
DBMS_OUTPUT.PUT_LINE(dept_desc);
END;
/搜索CASE表达式
在搜索CASE表达式中，比较表达式被用来作为匹配条件。此方式下，比较条件不再限于一列。
SELECT ename, empno,
(CASE
WHEN sal
WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
WHEN sal > 3000 THEN 'High'
ELSE 'N/A'
END) salary
FROM emp
ORDER BY ename;搜索CASE表达式也可以应用于PL/SQL中。
SET SERVEROUTPUT ON
DECLARE
sal NUMBER := 2000;
sal_desc VARCHAR2(20);
BEGIN
sal_desc := CASE
WHEN sal
WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
WHEN sal > 3000 THEN 'High'
ELSE 'N/A'
END;
DBMS_OUTPUT.PUT_LINE(sal_desc);
END;
/值匹配CASE语句
PL/SQL中还支持值匹配CASE语句，用法和CASE表达式非常相似，主要的差别是CASE语句以END CASE结尾，，而不是像CASE表达式那样以END结尾。CASE语句可以作为IF..THEN..ELSEIF的替代品。
SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN (SELECT ename, empno, deptno FROM emp ORDER BY ename) LOOP
DBMS_OUTPUT.PUT(cur_rec.ename || ' : ' || cur_rec.empno || ' : ');
CASE cur_rec.deptno
WHEN 10 THEN
DBMS_OUTPUT.PUT_LINE('Accounting');
WHEN 20 THEN
DBMS_OUTPUT.PUT_LINE('Research');
WHEN 30 THEN
DBMS_OUTPUT.PUT_LINE('Sales');
WHEN 40 THEN
DBMS_OUTPUT.PUT_LINE('Operations');
ELSE
DBMS_OUTPUT.PUT_LINE('Unknown');
END CASE;
END LOOP;
END;
/搜索CASE语句
就像搜索CASE表达式一样，该语句可以进行多个变量的比较。
SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN (SELECT ename, empno, sal FROM emp ORDER BY ename) LOOP
DBMS_OUTPUT.PUT(cur_rec.ename || ' : ' || cur_rec.empno || ' : ');
CASE
WHEN cur_rec.sal
DBMS_OUTPUT.PUT_LINE('Low');
WHEN cur_rec.sal BETWEEN 1000 AND 3000 THEN
DBMS_OUTPUT.PUT_LINE('Medium');
WHEN cur_rec.sal > 3000 THEN
DBMS_OUTPUT.PUT_LINE('High');
ELSE
DBMS_OUTPUT.PUT_LINE('Unknown');
END CASE;
END LOOP;
END;
/