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

The FOR and WHILE-LOOP in Oracle PL/SQL - 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

As yet another alternative to EXIT, you can place your loop termination condition at the beginning of the loop using this syntax:

WHILE condition LOOP
   statement1;
   statement2;
   . . .
END LOOP;

While this syntax may be more readable, it also has one major distinction compared to the previously discussed loop constructs: if the condition in the WHILE clause is not true the first time through the loop, the statements within the loop are not executed at all. In contrast, all previous versions of the LOOP construct execute the code within the loop at least once. Here is the previous example rewritten to use WHILE :

DECLARE
  countr       NUMBER := 1;
BEGIN
   WHILE countr < 11 LOOP
      dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
      countr := countr + 1;
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;

FOR-LOOP

If your application needs to iterate over a range of integers, you can use the FOR-LOOP construct and simplify your code even more. Here is the syntax:

FOR variable IN startvalue..endvalue LOOP
   statement1;
   statement2;
   . . .
END LOOP;

Within the loop, the variable variable  starts with a value of startvalue  and terminates the loop when the value of variable exceeds endvalue . Rewriting our well-worn example from earlier in the chapter (and while we’re at it, dropping the unnecessary variable declaration) looks like this:

BEGIN
   FOR i IN 1..10 LOOP
      dbms_output.put_line('Square root of ' || i || ' is ' || SQRT(i));
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;

Note that you do not need to include the loop variable in the declaration section. You can, however, explicitly declare your loop variables depending on your programming standards.

In our final loop example, you want to iterate your loop in reverse order and produce the square roots starting with ten and ending at one. As you might expect, all you need to add is the REVERSE keyword to your LOOP clause as follows:

BEGIN
   FOR i IN REVERSE 1..10 LOOP
      dbms_output.put_line('Square root of ' || i || ' is ' || SQRT(i));
   END LOOP;
   dbms_output.put_line('End of Calculations.');
END;

This produces the following output, as expected:

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

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

Please check back for the next part of this series.



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