oraappsexp

 Profile Answers by oraappsexp 

• Feb 9th, 2006

 

CASE Expressions And Statements

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:

Value Match CASE Expression

The CASE expression is a more flexible version of the DECODE function. In its simplest form it is used to return a value when a match is found:

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;

The value match CASE expression is also supported in PL/SQL:

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

A more complex version is the searched CASE expression where a comparison expression is used to find a match. In this form the comparison is not limited to a single column:

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;

The searched CASE expression is also supported in PL/SQL:

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

The CASE statement supported by PL/SQL is very similar to the CASE expression. The main difference is that the statement is finished with an END CASE statement rather than just END. The PL/SQL statements are essentially an alternative to lists of IF .. THEN .. ELSIF statements:

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

As with its expression counterpart, the searched CASE statement allows multiple comparisons using mulitple variables:

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;/

g_sidhu

 Profile 

• Feb 6th, 2008

 

The CASE statement supported by PL/SQL is very similar to the CASE expression. The main difference is that the statement is finished with an END CASE statement rather than just END. The PL/SQL statements are essentially an alternative to lists of IF .. THEN .. ELSIF statements: