Oracle Magazine - July/August 2011

the loop, as shown in Listing 5.

Do not use RE TURN or GOTO statements 2. within a loop—these cause the premature, unstructured termination of the loop.

Listing 6 presents an example of a FOR loop with a RETURN in it. The total_sales function returns the total sales across the specified years, but if any year has $0 in sales, the function should terminate the loop and return the current total sales.

Note that the loop terminates in one of two ways: either by iterating through all integers between the start and end years or by executing the RETURN inside the loop. In addition and related to that, this function now has two instances of RE TURN, which means that there are two ways “out” of the function. This is also not a recommended way to design your functions. You should have just a single RETURN in your executable section, the last line of the function.

I can restructure this function so that both
the loop and the function have just “one way
out,” as shown in Listing 7.

Steven Feuerstein
(steven.feuerstein@
quest.com) is Quest
Software’s PL/SQL
evangelist. He has
published 10 books on
Oracle PL/SQL (O’Reilly Media) and is an Oracle
ACE director. More information is available at
stevenfeuerstein.com.

READ PL/SQL 101, Part 1

bit.ly/eYIxRq

PL/SQL columns

bit.ly/fc0uoJ

DOWNLOAD Oracle Database 11g

oracle.com/software/products/database

DISCUSS PL/SQL

forums.oracle.com/forums/forum.jspa?forumID=75

TES T your PL/SQL knowledge

plsqlchallenge.com

Visit groupwareadvantage.com

EVERY GREAT BUSINESS HAS A HERO.

Introducing the cost-saving, record-breaking, performance-driving, HERO-BUILDING data center solutions from Oracle.

Today’s enterprise data centers are evolving faster than a speeding bullet. Give yourself the tools and confidence to keep pace with industry leading Oracle solutions backed by the proven expertise of an Oracle Platinum partner.

Count on Groupware to plan, design, support and implement a custom storage solution that can meet today’s business goals and adapt to tomorrow’s changes.

Discover how Groupware Database Specialists will help prioritize the applications and processes that will yield the greatest performance improvements and cost savings.

Visit groupwareadvantage.com

» Oracle’s Sun Storage F5100 Flash Arrays provide the best and easiest way to accelerate database performance up to 10X and reduce operating costs up to 80% compared to traditional disk drives.

» Oracle’s Sun ZFS Storage Appliance dramatically optimizes database and application performance and reduces I/O bottlenecks with no administrator intervention via automatic and intelligent data placement across the storage hierarchy.

» Oracle’s SPARC T3-1 server is the platform of choice for web infrastructure, middleware workloads, and application development, delivering the world’s first 16-core processor with unsurpassed throughput in a sleek, compact design.