Sunday, July 11, 2010

What are the differences in syntax between a CASE statement and a CASE expression ?

What are the differences in syntax between a CASE statement
and a CASE expression ?
===========================================================


The CASE expression was first added to SQL in Oracle8i.
Oracle9i extends its support to PL/SQL to allow CASE to
be used as an expression or statement:


1. A CASE statement is terminated with END CASE, while a CASE
expression is terminated with END.

2. The code within a WHEN clause of a CASE statement must terminate
with a semi-colon; the code within a WHEN clause of a CASE expression
must not terminate with a semi-colon.


Example:=

Case expression:
==================
[code]
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) salary
FROM emp
ORDER BY ename;
[/code]

Case statement:
==================
[code]
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 < 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;
/
[/code]