Home arrow Oracle arrow Oracle PL/SQL Control Structures and Loops

Oracle PL/SQL Control Structures and Loops

In this fourth part of a six part series, we look at the CASE statement and learn how to create loops in Oracle PL/SQL. This article is excerpted from chapter 36 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

TABLE OF CONTENTS:
  1. Oracle PL/SQL Control Structures and Loops
  2. Oracle Iterators: LOOP-END LOOP,
  3. The FOR and WHILE-LOOP in Oracle PL/SQL
By: Apress Publishing
Rating: starstarstarstarstar / 2
March 29, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The CASE statement is useful when you need to compare a value against an array of possibilities. While doing so is certainly possible using an IF statement, the code readability improves considerably by using the CASE statement. The CASE statement has two different forms, CASE-WHEN and the searched CASE statement. The CASE-WHEN statement identifies the variable to be compared in the first line of the CASE statement and performs the comparisons to the variable in subsequent lines. Here is the CASE-WHEN syntax:

CASE expression
   WHEN expression THEN statement_list
   [WHEN expression THEN statement_list] . . .
   [ELSE statement_list]
END CASE;

The ELSE condition executes if none of the other WHEN conditions evaluate to TRUE . Consider the following example, which sets a variable containing the appropriate sales tax rate by comparing a customerís state to a list of values:

CASE state
   WHEN 'AL' THEN tax_rate := .04;
   WHEN 'AK' THEN tax_rate := .00;
   ...
   WHEN 'WY' THEN tax_rate := .06;
END CASE;

Alternatively, the searched CASE statement gives you a bit more flexibility (at the expense of more typing). Here is the searched CASE syntax:

CASE
   WHEN condition THEN statement_list
   [WHEN condition THEN statement_list] . . .
   [ELSE statement_list]
END CASE;

Consider the following revised example, which sets a variable containing the appropriate sales tax rate by comparing a customerís state to a list of values:

CASE
   WHEN state='AL' THEN tax_rate := .04;
   WHEN state='AK' THEN tax_rate := .00;
   ...
   WHEN state='WY' THEN tax_rate := .06;
END CASE;

The form of the CASE statement is sometimes driven by your programming style. However, when you have complex conditions that cannot be represented in the CASE-WHEN syntax, you have no choice but to use the searched CASE . In either case (no pun intended), the readability of your code is dramatically improved in contrast to representing the same logic using
IF-THEN-[ELSIF][-ELSE]-END IF .



 
 
>>> More Oracle Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: