Home arrow Oracle arrow Page 2 - Oracle PL/SQL Control Structures and Loops

Oracle Iterators: LOOP-END LOOP, EXIT, and EXIT-WHEN - Oracle

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

Some tasks, such as inserting a number of new rows into a table, require the ability to repeatedly execute over a set of statements. This section introduces the various methods available for iterating and exiting loops.

LOOP

The most basic way to loop through a series of statements is with the LOOP-END LOOP construct using this syntax:

LOOP
   statement1;
   statement2;
   . . .
END LOOP;

The first question that may come to mind is, how useful is this plain LOOP construct if you canít exit the loop? The DBA will not be too happy if your subprogram runs indefinitely. The EXIT constructs will address this issue in the next section.

EXIT and EXIT-WHEN

The EXIT statement forces a loop to complete unconditionally. As you might expect, you execute the EXIT statement based on a condition in an IF statement. Consider the following example where you display the square roots of the numbers one through ten:

DECLARE
  countr    NUMBER := 1;
BEGIN
   LOOP
      dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
      countr := countr + 1;
      IF countr > 10 THEN
         EXIT;
      END IF;
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;

The counter is initialized in the DECLARE section; within the loop, the counter is incremented. Once the counter reaches the threshold value in the IF statement, the loop terminates and continues execution after the END LOOP statement. The output looks like this:

--------------------------------------------
Square root of 1 is 1
Square root of 2 is 1.41421356237309504880168872420969807857 Square root of 3 is 1.73205080756887729352744634150587236694 Square root of 4 is 2
Square root of 5 is 2.23606797749978969640917366873127623544 Square root of 6 is 2.44948974278317809819728407470589139197 Square root of 7 is 2.64575131106459059050161575363926042571 Square root of 8 is 2.82842712474619009760337744841939615714 Square root of 9 is 3
Square root of 10 is 3.16227766016837933199889354443271853372
End of Calculations.

Statement processed.
--------------------------------------------

You can alternatively use the EXIT-WHEN construct to improve the readability of your code if you only use the IF statement to check for a termination condition. You can rewrite the previous code example as follows:

DECLARE
  countr     NUMBER := 1;
BEGIN
   LOOP
      dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
      countr := countr + 1;
      EXIT WHEN countr > 10;
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;



 
 
>>> 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: