CASE Expressions And Statements

SELECT ename empno (CASE deptno  WHEN 10 THEN 'Accounting'  WHEN 20 THEN 'Research'  WHEN 30 THEN 'Sales'  WHEN 40 THEN 'Operations'  ELSE 'Unknown' END) departmentFROM empORDER BY ename;

SET SERVEROUTPUT ONDECLARE 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;/

Searched CASE Expression

SELECT ename empno (CASE  WHEN sal < 1000 THEN 'Low'  WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'  WHEN sal > 3000 THEN 'High'  ELSE 'N/A' END) salaryFROM empORDER BY ename;

SET SERVEROUTPUT ONDECLARE sal  NUMBER : 2000; sal_desc VARCHAR2(20);BEGIN sal_desc : CASE     WHEN sal < 1000 THEN 'Low'     WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'     WHEN sal > 3000 THEN 'High'     ELSE 'N/A'    END; DBMS_OUTPUT.PUT_LINE(sal_desc);END;/

Value Match CASE Statement

SET SERVEROUTPUT ONBEGIN 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;/

Searched CASE Statement

SET SERVEROUTPUT ONBEGIN 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 < 1000 THEN   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;/