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:
Example
At first glance, the following procedure makes sense, but on closer examination, it's a mess:
Every IF statement is executed and each condition evaluated. You should rewrite such logic as follows:
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 su...
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
pposed 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:
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:
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.
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:
change it to simpler, more direct code:
Example
In some cases, you can completely remove an IF statement. Consider the following conditional statement:
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:
If hiredate can be NULL, the following statement offers a comparable expression:
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.
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:
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:
Then rewrite this loop body as follows:
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:
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:
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:
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:
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:
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:
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:
At first glance, this seems fine. I throw together a test and am pleased with the results, as shown here:
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..."):
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:
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:
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:
Once I recognize this situation (usually identified through a code walkthrough), I should change it to this:
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:
http://www.quest.com/sql_navigator/: SQL Navigator.
http://www.sfi-software.com/sql-programmer.htm: SQL Programmer.
http://www.embarcadero.com/products/Develop/develop.htm: Rapid SQL.
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:
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.
