Skip to Main Content
Mastering Oracle SQL
book

Mastering Oracle SQL

by Sanjay Mishra, Alan Beaulieu
April 2002
Intermediate to advanced content levelIntermediate to advanced
336 pages
9h 58m
English
O'Reilly Media, Inc.
Content preview from Mastering Oracle SQL

The Case for CASE

The CASE expression made its SQL debut in the SQL-92 specification in 1992. Eight years later, Oracle included the CASE expression in the 8.1.6 release. Like the DECODE function, the CASE expression enables conditional logic within an SQL statement, which might explain why Oracle took so much time implementing this particular feature. If you have been using Oracle for a number of years, you might wonder why you should care about the CASE expression, since DECODE does the job nicely. Here are several reasons why you should make the switch:

  • CASE expressions can be used everywhere that DECODE functions are permitted.

  • CASE expressions are more readable than DECODE expressions.

  • CASE expressions execute faster than DECODE expressions.[14]

  • CASE expressions handle complex logic more gracefully than DECODE expressions.

  • CASE is ANSI-compliant, whereas DECODE is proprietary.

The only downside to using CASE over DECODE is that CASE expressions are not supported in Oracle8i’s PL/SQL language. If you are using Oracle9i, however, any SQL statements executed from PL/SQL may include CASE expressions.

The SQL-92 specification defines two distinct flavors of the CASE expression: searched and simple. Searched CASE expressions are the only type supported in the Oracle8i release. If you are using Oracle9i, you may also use simple CASE expressions.

Searched CASE Expressions

A searched CASE expression evaluates a number of conditions and returns a result determined by which condition ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL

Oracle SQL

Dan Hotka
Oracle SQL Developer

Oracle SQL Developer

Ajith Narayanan, Susan Harper

Publisher Resources

ISBN: 0596001290Supplemental ContentCatalog PageErrata