PL/SQL control structures: Best practices

Here are several best practices you should take into account when you work with IF, FOR, WHILE, and GOTO statements in PL/SQL.

This Content Component encountered an error

This is Chapter 4 from the O'Reilly book "Oracle PL/SQL Best Practices" by Steven Feuerstein. You can purchase the book here.

Oracle PL/SQL offers a range of constructs that allow you to control the flow of processing, including:

  • For conditional logic: the IF statement
  • For loop processing: FOR, WHILE, and simple loops
  • For branching logic: the GOTO statement

These constructs are relatively straightforward in syntax and usage. There remain, however, several best practices you should take into account when you work with these kinds of statements.

Conditional and Boolean Logic

Follow the best practices in this section when you are using PL/SQL's IF statements.

CTL-01: Use ELSIF with mutually exclusive clauses.

When you need to write conditional logic that has several mutually exclusive clauses (in other words, if one clause is TRUE, no other clause evaluates to TRUE), use the ELSIF construct:

IF condA THEN
   ...
ELSIF condB THEN
   ...
ELSIF condN THEN
   ...
ELSE
   ...
END IF; 

Example

At first glance, the following procedure makes sense, but on closer examination, it's a mess:

PROCEDURE process_lineitem (line_in IN INTEGER)
IS
BEGIN
   IF line_in = 1
   THEN
      process_line1;
   END IF;
   IF line_in = 2
   THEN
      process_line2;
   END IF;
   ...
   IF line_in = 2045
   THEN
      process_line2045;
   END IF;
END;

Every IF statement is executed and each condition evaluated. You should rewrite such logic as follows:

PROCEDURE process_lineitem (line_in IN INTEGER)
IS
BEGIN
   IF line_in = 1
   THEN
      process_line1;
   ELSIF line_in = 2
   THEN
      process_line2;
   ...
   ELSIF line_in = 2045
   THEN
      process_line2045;
   END IF;
END;

Benefits

This structure clearly expresses the underlying "reality" of your business logic: if one condition is TRUE, no others can be TRUE.

ELSIF offers the most efficient implementation for processing mutually exclusive clauses. When one clause evaluates to TRUE, all subsequent clauses are ignored.

CTL-02: Use IF...ELSIF only to test a single, simple condition.

The real world is very complicated; the software we write is supposed to map those complexities into applications. The result is that we often end up needing to deal with convoluted logical expressions.

You should write your IF statements in such a way as to keep them as straightforward and understandable as possible. For example, expressions are often more readable and understandable when they are stated in a positive form. Consequently, you are probably better off avoiding the NOT operator in conditional expressions.

Example

It's not at all uncommon to write or maintain code that's structured like this:

IF condA AND NOT ( condB OR condC ) 
THEN 
   proc1; 
ELSIF condA AND (condB OR condC) 
THEN 
   proc2; 
ELSIF NOT condA AND condD 
THEN
   proc3;
END IF; 

It's also fairly common to get a headache trying to make sense of all of that. You can often reduce the trauma of headache by trading off the simplicity of the IF statement itself (one level of IF and ELSIF conditions) for the simplicity of clauses within multiple levels:

IF condA 
THEN 
   IF (condB OR condC) 
   THEN 
      proc2; 
   ELSE 
      proc1;
   END IF; 
ELSIF condD 
THEN 
   proc3
END IF;

Don't forget, by the way, to take into account the possibility of your expressions evaluating to NULL. This can throw a monkey wrench into your conditional processing.

An Exception to the Rule

A notable exception to this best practice is when you need to negate a large AND expression in order to find out efficiently whether one value out of a group is different. For example, I recently needed to test the counts of 10 parallel index-by tables, to see if even one of them was different; if so, it was an error. Because AND expressions short-circuit on FALSE (whereas ORs short-circuit on TRUE), this was more efficient than using a group of ORs. Moreover, the logic read more naturally. For example:

IF NOT (arr1.count = arr2.count

AND arr1.count = arr3.count

AND arr1.count = arr4.count AND . . .

AND arr1.count = arr10.count)

THEN RAISE e_missing_value;

--Dan Clamage

Benefits

Following this best practice will make your code easier to read and maintain.

Breaking an expression into smaller pieces can aid maintainability; if and when the logic changes, you can change one IF clause without affecting the logic of others.

Challenges

Multiple levels of nested IF statements can also decrease readability. You need to strive for a workable balance.

There's a tradeoff between efficiency (fewer conditional statements) and ease of comprehension. "Many times," wrote one reviewer, "I'll code an IF or ELSE with a NULL statement, either to make the code easier to read, or as a placeholder for future logic. However, I may then find myself repeating logic (such as code that resets a variable) under multiple ELSE blocks because I've broken up the IF expression into smaller pieces."

CTL-03: Replace and simplify IF statements with Boolean expressions.

Sometimes, you will write or come across conditional statements that, while valid, are unnecessary and cumbersome. Such statements often reflect a lack of understanding about how you can and should use Boolean expressions and variables.

In general, if you see or write code like this:

DECLARE
   boolean_variable BOOLEAN;
BEGIN
   IF boolean_variable = TRUE
   THEN
      ...
   ELSIF boolean_variable = FALSE
   THEN
      ...
   END IF;

change it to simpler, more direct code:

DECLARE
   boolean_variable BOOLEAN;
BEGIN
   IF boolean_variable
   THEN
      ...
   ELSIF NOT boolean_variable
   THEN
      ...
   END IF;

Example

In some cases, you can completely remove an IF statement. Consider the following conditional statement:

IF hiredate < SYSDATE
THEN
   date_in_past := TRUE;
ELSE
   date_in_past := FALSE;
END IF;

If you've already validated that hiredate can't be or isn't NULL, you can replace the entire IF statement with this single assignment:

date_in_past := hiredate < SYSDATE;

If hiredate can be NULL, the following statement offers a comparable expression:

date_in_past := NVL (hiredate < SYSDATE, FALSE);

Benefits

Following this best practice will make your code more readable and expressive.

Loop Processing

Follow the best practices in this section when you are using PL/SQL's looping statements.

CTL-04: Never EXIT or RETURN from WHILE and FOR loops.

The WHILE and FOR loops include "boundary conditions" that determine:

  • When and if a loop should execute at all
  • When a loop should stop executing

If you use the EXIT or RETURN statements inside a WHILE or FOR loop, you cause an unstructured termination from the loop. The resulting code is hard to trace and debug.

Example

Here's the bottom half of a function that scans the contents of a collection and returns the row in which a match is found.

   l_count := titles.COUNT;
   FOR indx IN 1 .. l_rowcount 
   LOOP
      IF l_match_against = titles(indx)
      THEN
         RETURN indx;
      END IF;
   END LOOP;
 
   RAISE Exit_Function;
EXCEPTION
   WHEN Exit_Function THEN RETURN NULL;
END;

Now this is some nasty code. You manage to get all the way down to the end of the executable section, and you are punished with an exception! See MOD-07 for how this violates best practice for a "funnel-shaped" function.

Of course, you're not supposed to get to the end of the function. Instead, the function finds a match and zooms straight out of the function with a RETURN.

Now imagine a function whose body is 200 lines long with nested loops and several different RETURNs in different parts of the loop. Chaos!

Benefits

By following the maxim "one way in and one way out" for your loops, the resulting code is much easier to understand and debug. If your loop needs to execute at least once (like a Pascal REPEAT statement), you're better off using a simple LOOP construct and testing for the exit condition with EXIT WHEN.

Challenges

Your exit test in the WHILE expression can become a bit more complex, especially when you have to replace a natural FOR loop with a more mechanical WHILE loop. For example, you have a FOR loop expression that iterates over nested_table.FIRST to nested_table.LAST, but you need to terminate the loop when you find a matching entry. In order to put the exit test in the iteration scheme, you have to now use a WHILE loop, initialize and maintain a loop control variable yourself (for the current offset), and test for the exit condition in the WHILE expression.

CTL-05: Use a single EXIT in simple loops.

This best practice is another variation on "one way in, one way out." It suggests that, whenever possible, you consolidate all exit logic in your simple loop to a single EXIT (or EXIT WHEN) statement.

In general, use the EXIT WHEN statement in place of code like this:

IF <> THEN EXIT; END IF;

because it's more intuitive and requires less typing.

Example

Here's part of a program that compares two files for equality. After reading the next line from each file, it checks for the following conditions:

  • Did I reach the end of both files?
  • Are the lines different?
  • Did I reach the end of just one file?

In each case, set the "return value" for the function and also issue an EXIT statement:

LOOP
   read_line (file1, line1, file1_eof);
   read_line (file2, line2, file2_eof);
 
   IF (file1_eof AND file2_eof)
   THEN
      retval := TRUE;
      EXIT;
   ELSIF (line1 != line2)
   THEN
      retval := FALSE;
      EXIT;
   ELSIF (file1_eof OR file2_eof)
   THEN
      retval := FALSE;
      EXIT;
   END IF;
END LOOP;

Then rewrite this loop body as follows:

LOOP
   read_line (file1, line1, file1_eof);
   read_line (file2, line2, file2_eof);
 
   IF (file1_eof AND file2_eof)
   THEN
      retval := TRUE;
      exit_loop := TRUE;
   ELSIF (line1 != line2)
   THEN
      retval := FALSE;
      exit_loop := TRUE;
   ELSIF (file1_eof OR file2_eof)
   THEN
      retval := FALSE;
      exit_loop := TRUE;
   END IF;
   EXIT WHEN exit_loop;
END LOOP;

Sometimes it can be difficult to come up with just one EXIT statement. This usually occurs when you need to check a condition at the beginning and end of a loop. If you run into this situation, consider changing to a WHILE loop.

You should also be careful to initialize your return value and your loop terminator variable, to avoid unwanted NULL values that might disrupt your logic.

Benefits

A single EXIT is especially important in large, complex loop bodies; it allows you to more easily trace and debug your code.

Challenges

Depending on how badly the loop was written initially, you may need to perform substantial restructuring to improve the loop code.

CTL-06: Use a simple loop to avoid redundant code required by a WHILE loop.

Generally, you should use a simple loop if you always want the body of the loop to execute at least once. You use a WHILE loop if you want to check before executing the body the first time. Since the WHILE loop performs its check "up front," the variables in the boundary expression must be initialized. The code to initialize is often the same code needed to move to the next iteration in the WHILE loop. This redundancy creates a challenge in both debugging and maintaining the code: how do you remember to look at and update both?

If you find yourself writing and running the same code before the WHILE loop and at end of the WHILE loop body, consider switching to a simple loop.

Example

I write a procedure to calculate overdue charges for books; the maximum fine to be charged is $10, and I will stop processing when there are no overdue books for a given date. Here is my first attempt at the procedure body:

DECLARE
   l_fine PLS_INTEGER := 0;
   l_date DATE := SYSDATE;
   l_overdue_count NUMBER;
BEGIN
   l_overdue_count := 
      overdue_pkg.countem (
         borrower_id => borrower_in,
         l_date);
   
   WHILE (l_overdue_count > 0 AND l_fine < 10)
   LOOP
      update_fine_info (l_date, l_one_day_fine);
 
      l_fine := l_fine + l_one_day_fine;
      l_date := l_date + 1;
      l_overdue_count :=
         overdue_pkg.countem (
            borrower_id => borrower_in,
            l_date);
   END LOOP;

As is readily apparent, I duplicate the assignments of values to l_overdue_count. I would be far better off rewriting this code as follows:

DECLARE
   l_fine PLS_INTEGER := 0;
   l_date DATE := SYSDATE;
   l_overdue_count NUMBER;
BEGIN
   LOOP
      EXIT WHEN 
        (l_overdue_count <= 0 OR l_fine >= 10)
      
      update_fine_info (l_date, l_one_day_fine);
      
      l_fine := l_fine + l_one_day_fine;
      
      l_date := l_date + 1;
 
      l_overdue_count :=
         overdue_pkg.countem (
            borrower_id => borrower_in,
            l_date);
   END LOOP;

Benefits

You avoid redundant code, always bad news in a program, since it increases maintenance costs and the chance of introducing bugs into your code.

Challenges

If you have established a habit early on of writing WHILE loops, it can be hard to (a) notice the redundancy and (b) change your style.

CTL-07: Never declare the FOR loop index.

PL/SQL offers two kinds of FOR loops: numeric and cursor. Both have this general format:

FOR loop index IN loop range
LOOP
   loop body
END LOOP;

The loop index is either an integer or a record; in either case, it's implicitly declared by the PL/SQL runtime engine. The scope of the loop index variable is restricted to the body of the loop (between the LOOP and END LOOP statements).

You should never declare a variable for the loop. If you do declare the loop index variable, you are actually declaring a completely separate (recordtype or numeric) variable that will (best case) never be used or (worst case) used outside the loop in a way that is confusing and likely to introduce errors.

Example

The developer who worked on the library management system before Jim (a PL/SQL novice) created this procedure to delete books from the collection by title:

CREATE OR REPLACE PROCEDURE remove_titles (
   title_in IN book.title%TYPE,
)
IS
   CURSOR book_cur
   IS
     SELECT isbn, author FROM book
      WHERE title LIKE title_in;
   book_rec book_cur%ROWTYPE;
BEGIN
   FOR book_rec IN book_cur
   LOOP
      te_book.rem (book_rec.isbn);
   END LOOP;
END;

It works just fine (no bugs reported), but Jim has been asked to modify the procedure to display the last book removed. So he adds this code after the FOR loop:

   END LOOP;
   pl (book_rec.isbn || ' - ' || 
       book_rec.author);
END;

The code compiles, but Jim spends the next two hours banging his head against the wall trying to figure out why the last book information keeps coming up NULL. He doesn't question the existing code, since it worked and was written by a high-priced consultant. It must be Jim's fault.

In fact, the original code was faulty. The declaration of book_rec was unnecessary and made Jim's error possible.

Benefits

By avoiding unnecessary code, you make it less likely for programmers to introduce errors into the code at some later point.

You need not take out "programmer's insurance": "Gee, I don't know if I need to declare that or not, so I'd better declare it." Instead, you make certain you understand how PL/SQL works and write appropriate code.

CTL-08: Scan collections using FIRST, LAST, and NEXT in loops.

A collection in PL/SQL is like a single-dimensional array. A collection differs from an array, however, in that two of the three types of collections (nested tables and index-by tables) can be sparse, which means that the defined rows in the collection need not be sequentially defined. You can, in other words, assign a value to row 10 and a value to row 10,000, and now rows will exist between those two.

If you scan a collection with a FOR loop and the collection is sparse, the FOR loop tries to access an undefined row and raise a NO_DATA_FOUND exception. Instead, use the FIRST and NEXT methods to scan forward through a collection, and use LAST and PRIOR to scan backwards

Example

I have decided to help all of my co-programmers by providing a package that offers a standard collection type (list of strings) and some utility programs to manipulate collections defined on that type. Here is the package specification:

CREATE OR REPLACE PACKAGE mycollection
IS
   TYPE string_tt IS TABLE OF VARCHAR2 (2000)
      INDEX BY BINARY_INTEGER;
 
   PROCEDURE show (list_in IN string_tt);
 
   FUNCTION eq (list1_in IN string_tt, list2_in IN string_tt)
      RETURN BOOLEAN;
END mycollection;

By using this package, I can easily declare a collection, display its contents, and even compare two collections of the same type to see if they are equal. That sounds handy! The implementation of this utility package, however, will determine how widely my code is used. Here's my first attempt:

CREATE OR REPLACE PACKAGE BODY mycollection
IS
   PROCEDURE show (list_in IN string_tt)
   IS
   BEGIN
      FOR indx IN list_in.FIRST .. list_in.LAST
      LOOP
         pl (list_in (indx));
      END LOOP;
   END show;
 
   FUNCTION eq (list1_in IN string_tt, list2_in IN string_tt)
      RETURN BOOLEAN
   IS
      retval    BOOLEAN     := TRUE;
      indx      PLS_INTEGER := list1_in.FIRST;
      l_last1   PLS_INTEGER := list1_in.LAST;
   BEGIN
      WHILE retval
        AND indx <= l_last1
      LOOP
         retval := list1_in (indx) = list2_in (indx);
         indx := indx + 1;
      END LOOP;
   RETURN retval;
   END eq;
END mycollection;
/

At first glance, this seems fine. I throw together a test and am pleased with the results, as shown here:

SQL> DECLARE
  2     family   mycollection.string_tt;
  3     pets     mycollection.string_tt;
  4  BEGIN
  5     family (1) := 'Veva';
  6     family (2) := 'Eli';
  7     family (3) := 'Chris';
  8     family (4) := 'Steven';
  9     mycollection.show (family);
 10     pets (1) := 'Mercury';
 11     pets (2) := 'Moshe Jacobawitz';
 12     pets (3) := 'Sister Itsacat';
 13     bpl (mycollection.eq (family, pets));
 14  END;
 15  /
Veva
Eli
Chris
Steven
FALSE

Those two collections certainly aren't identical. Well, what a handy little package! I enthusiastically tell all my programming friends that I have a present for them and invite them to use mycollection. Not an hour goes by before Sriniva asks me to visit her cubicle. "What's this all about?" she asks me (with a subtext of "Gee, I guess your code is not to be trusted..."):

SQL> DECLARE
  2     authors   mycollection.string_tt;
  3     pets      mycollection.string_tt;
  4  BEGIN
  5     FOR rec IN (SELECT * FROM author)
  6     LOOP
  7        authors (rec.author_id) := rec.last_name;
  8     END LOOP;
  9  
 10     mycollection.show (authors);
 11  END;
 12  /
FEUERSTEIN
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.MYCOLLECTION", line 8

I scratch my head for a while, then ask to see the data in the authors table. "Why should that matter?" is the response. It's a good response. Embarrassment soon propels me to the heart of the difficulty: her author_id values are probably not sequential--but my loops assume a densely filled collection!

Check out the myCollection.pkg file for a rewrite of the package body that fixes this problem.

Benefits

Your scan is less likely to raise an exception.

This is the most efficient way to scan a collection. You can, as is shown in the files listed under "Resources," build protection within the FOR loop to avoid raising NO_DATA_FOUND, but then you might well do excessive looping. What if, for example, the second_row_in were two million?

Resources

plsqlloops.pro : Script to compare the performance of several alternatives to scanning a collection.

myCollection.pkg: Implementation of a utility package that displays the contents of a collection and compares the contents of two collections.

CTL-09: Move static expressions outside of loops and SQL statements.

Whenever you set out to tune your PL/SQL programs, you should first take a look at your loops. Any inefficiency inside a loop's body will be magnified by the multiple executions of that code.

A common mistake is to put code that is static or unchanging for each iteration of the loop inside the body. When you can identify such situations, extract the static code, assign the outcomes of that code to one or more variables, and then reference those variables inside the loop.

Example

This procedure summarizes book reviews. It's run every morning at 8 A.M. and takes about 15 minutes to complete:

CREATE OR REPLACE PROCEDURE summarize_reviews (
   summary_title_in IN VARCHAR2,
   isbn_in IN book.isbn%TYPE)
IS
   CURSOR review_cur IS
      SELECT text, 
             TO_CHAR (SYSDATE, 'MM/DD/YYYY') today
        FROM book_review
       WHERE isbn = isbn_in;
BEGIN
   FOR review_rec IN review_cur
   LOOP
      IF LENGTH (review_rec.text) > 100
      THEN
         review_rec.text := 
            SUBSTR (review_rec.text, 1, 100);
      END IF;
      
      review_pkg.summarize (
         UPPER (summary_title_in),
         today,
         UPPER (review_rec.text)
         );
   END LOOP;
END;
/

There are a number of problems with this code:

  • Since my job starts and finishes on the same day, I don't need to select SYSDATE with each row of my query. And unless I really want "today" to be a string expression, or I am ready to absorb the overhead of multiple implicit conversions, I should use TRUNC to get rid of the time element.
  • I write over the text field of the review_rec record. While this is allowed by PL/SQL, you are generally better off not modifying the index variable loop. Treat it as a constant.
  • Since my summary_title_in argument never changes, I shouldn't UPPER case in each iteration of the loop.
  • Rather than check the length of the text for each row and then SUBSTR (and UPPER case), why not just SUBSTR inside SQL?

Here is a rewrite of the summarize_reviews procedure:

CREATE OR REPLACE PROCEDURE summarize_reviews (
   summary_title_in IN VARCHAR2,
   isbn_in IN book.isbn%TYPE)
IS
   l_summary book_types.summary_t
      := UPPER (summary_title_in);
   
   l_today CONSTANT DATE := TRUNC (SYSDATE);
      
   CURSOR review_cur IS
      SELECT UPPER (SUBSTR (text, 1, 100)) text
        FROM book_review
       WHERE isbn = isbn_in;
BEGIN
   FOR review_rec IN review_cur
   LOOP
      review_pkg.summarize (
         l_summary, l_today, review_rec.text
         );
   END LOOP;
END;
/

TIP:   You can, in general, expect the performance of built-in functions such as SUBSTR to work more efficiently in SQL than in PL/SQL, so move the processing to the SQL layer whenever possible.

Benefits

Your code doesn't do any unnecessary work and so executes more efficiently.

Challenges

Don't be obsessed with this sort of optimization as you write your code. It's theoretically true that calling UPPER just once before the loop is more efficient compared to calling it 100 times inside the loop. It's also very likely to be the case that the cycles saved on this optimization are never noticed by the user. You are always better off saving the bulk of your optimization efforts until you have identified the bottlenecks in your application as a whole.

Resources

insql.sql: A script to compare the performance of functions in SQL versus PL/SQL.

Miscellaneous

The best practices in this section are grouped together simply because they don't fall into either of the other categories.

CTL-10: Use anonymous blocks within IF statements to conserve resources.

One of the nice things about PL/SQL is that you, the developer, can define any set of executable statements as a distinct block, with its own declaration, executable, and exception sections.

If you notice that certain operations and data structures aren't needed unless a certain condition is satisfied, move all the execution of those operations and the declaration of those data structures inside the conditional statement. The result is that you won't incur the overhead (CPU or memory) unless it's absolutely needed.

Example

In the following block, I declare a set of local variables and even initialize l_name with a function that usually takes 10 seconds to execute (min_balance_account). But when I write my block, it turns out that in many situations, those structures are ignored:

DECLARE
   TYPE account_tabtype IS TABLE 
      OF account%ROWTYPE INDEX BY BINARY_INTEGER;
   l_accounts account_tabtype;
 
   l_name VARCHAR2(2000) := 
      min_balance_account (SYSDATE);
BEGIN 
   IF balance_too_low (1056)
   THEN
      use_collection (l_accounts);
      use_name (l_name);
   ELSE
      -- No use of l_accounts or l_name
      ...
   END IF;
END;

Once I recognize this situation (usually identified through a code walkthrough), I should change it to this:

BEGIN
   IF balance_too_low (1056)
   THEN  
      DECLARE  
         TYPE account_tabtype IS TABLE 
            OF account%ROWTYPE 
            INDEX BY BINARY_INTEGER;
         l_accounts account_tabtype;
 
         l_name VARCHAR2(2000) := 
            min_balance_account (SYSDATE);
      BEGIN
         use_collection (l_accounts);
         use_name (l_name);
      END;
   ELSE
      -- No use of l_accounts or l_name
      ...
   END IF;
END;

Benefits

Your programs won't execute unnecessary code, improving performance and reducing memory requirements for the program.

Challenges

It can be hard to realize as you first write your program that this kind of situation exists. Use code walkthroughs to uncover these optimization opportunities. You can also use Oracle8i 's code profiler (the DBMS_PROFILER built-in package) to identify unused or little-used code. A number of PL/SQL IDEs offer a GUI interface to this profiler.

Resources

The following products currently offer GUIs to DBMS_PROFILER:

CTL-11: Label and highlight GOTOs if using this normally unnecessary construct.

I suppose that it was thorough of Oracle to include a GOTO statement in the PL/SQL language. This statement, however, should generally be avoided, as it leads to unstructured code design that is hard to analyze and debug.

There are scenarios in which a GOTO can be justified; these mostly relate to going into existing spaghetti code to fix a bug or enhance the code. For an extensive review of GOTO-related issues, see Chapter 16 in Steve McConnell's book, Code Complete.

Example

Here is a use of GOTO that calls attention to itself:

CREATE OR REPLACE PROCEDURE someone_elses_mess
/*
|| Author: Longgone Consultant
|| Maintained by: Sad Employee
||
|| Modification History
|| When    Who    What
|| --------------------------------------------
|| 11/2000 Sad E. Fixed bug in overdue logic.
||                Used GOTO to bypass Gordian 
||                Knot of code left by L.C.
*/
IS
BEGIN
   IF ... THEN
      IF ... THEN
         FOR rec IN cur LOOP
            -- 11/2000 Bypass with GOTO
            GOTO <<quick_exit>>
         END LOOP;
         ... lots more code
      END IF;
      -- 11/2000 GOTO Target
      <<quick_exit>>
   END IF;

Benefits

Even if you can, at times, justify the use of a GOTO, you can almost always achieve the same effect with a more structured and more easily understood use of conditional and loop logic.

Resources

Code Complete, by Steve McConnell: See Chapter 16, Unusual Control Structures, for an in-depth discussion of the GOTO statement and recommendations for when it can justifiably be used.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in June 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close