Previous: 7.7 Tips for PL/
SQL Loops
Chapter 8
Next: 8.2 The Exception
Section
8. Exception Handlers
Contents:
Why Exception Handling?
The Exception Section
Types of Exceptions
Determining Exception-Handling Behavior
Raising an Exception
Handling Exceptions
Client-Server Error Communication
NO_DATA_FOUND: Multipurpose Exception
Exception Handler as IF Statement
RAISE Nothing but Exceptions
In the PL/SQL language, errors of any kind are treated as exceptions situations that should not
occur in your program. An exception can be one of the following:
● An error generated by the system (such as "out of memory" or "duplicate value in index")
● An error caused by a user action
● A warning issued by the application to the user
PL/SQL traps and responds to errors using an architecture of exception handlers. The exception-
handler mechanism allows you to cleanly separate your error processing code from your executable
statements. It also provides an event-driven model, as opposed to a linear code model, for processing
errors. In other words, no matter how a particular exception is raised, it is handled by the same
exception handler in the exception section.
When an error occurs in PL/SQL, whether a system error or an application error, an exception is
raised. The processing in the current PL/SQL block's execution section halts and control is transferred
to the separate exception section of your program, if one exists, to handle the exception. You cannot
return to that block after you finish handling the exception. Instead, control is passed to the enclosing
block, if any.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 8.1 illustrates how control is transferred to the exception section when an exception is raised.
Figure 8.1: Exception handling architecture
8.1 Why Exception Handling?
It is a sad fact of life that most programmers never take the time to properly bullet-proof their
programs. Instead, wishful thinking often reigns. Most of us find it hard enough and more than
enough work to simply write the code that implements the positive aspects of an application:
maintaining customers, generating invoices, etc. It is devilishly difficult from both a psychological
standpoint and a resources perspective to focus on the negative side of our life: what happens when
the user presses the wrong key? If the database is unavailable, what should I do?
As a result, we write applications that often assume the best of all possible worlds, hoping that our
programs are bug-free, that users will enter only the correct data in only the correct fashion, and that
all systems (hardware and software) will always be a "go."
Of course, harsh reality dictates that no matter how hard you try, there will always be one more bug
in your application. And your users will always find just the right sequence of keystrokes it takes to
make a screen implode. The situation is clear: either you spend the time up front to properly debug
and bulletproof your programs, or you will fight an unending series of rear-guard battles, taking
frantic calls from your users and putting out the fires.
You know what you should do. Fortunately, PL/SQL offers a powerful and flexible way to trap and
handle errors in your programs. It is entirely feasible within the PL/SQL language to build an
application which fully protects the user and the database from errors.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The exception handler model offers the following advantages:
● Event-driven handling of errors. As we've mentioned, PL/SQL exception handling follows an
event-driven rather than a linear code model. No matter how a particular exception is raised, it
is handled by the same exception handler in the exception section. You do not have to check
repeatedly for a condition in your code, but instead can insert an exception for that condition
once in the exception section and be certain that it will be handled throughout that block (and
all of its enclosing blocks).
● Clean separation of error-processing code. With the exception-handling model, whenever an
exception is raised, program control transfers completely out of the normal execution
sequence and into the exception section. Instead of placing error-handling logic throughout
different sections of your program, you can consolidate all of this logic into a single, separate
section. Furthermore, if you need to add new exceptions in your program (perhaps you
overlooked a possible problem, or a new kind of system error has been identified), you do not
have to figure out where in your executable code to put the error-handling logic. Simply add
another exception handler at the bottom of the block.
● Improved reliability of error handling. It is quite difficult for errors to go undetected with the
PL/SQL error-handling model. If there is a handler, then that exception will be dealt with in
the current block or in an enclosing block. Even if there is no explicit handler for that error,
normal code execution will still stop. Your program cannot simply "work through" an error
unless you explicitly organize your code to allow this.
There is no avoiding the fact that if you want to trap errors in your PL/SQL programs you will have
to write some additional code. The exception handler architecture, however, minimizes the amount of
code you will need to write, and offers the possibility of guarding against all problems that might
arise in your application. The following sections look at how you define, raise, and handle exceptions
in PL/SQL.
Previous: 7.7 Tips for PL/
SQL Loops
Oracle PL/SQL
Programming, 2nd Edition
Next: 8.2 The Exception
Section
7.7 Tips for PL/SQL Loops
Book Index
8.2 The Exception Section
The Oracle Library
Navigation
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 7.6 Managing
Loop Execution
Chapter 7
Loops
Next: 8. Exception Handlers
7.7 Tips for PL/SQL Loops
Loops are very powerful and useful constructs, but they are among the most complicated control
structures in PL/SQL. The tips in this section will help you select the most efficient and easily
maintained loops for your programs.
7.7.1 Naming Loop Indexes
How would you like to try to understand much less maintain code that looks like this?
FOR i IN start_id end_id
LOOP
FOR j IN 1 7
LOOP
FOR k IN 1 24
LOOP
build_schedule (i, j, k);
END LOOP;
END LOOP;
END LOOP;
It is hard to imagine that someone would write code based on such generic integer variable names
(right out of Algebra 101), yet it happens all the time. The habits we pick up in our earliest days of
programming have an incredible half-life. Unless you are constantly vigilant, you will find yourself
writing the most abominable code. In this case, the solution is simple: use variable names for the loop
indexes that are meaningful and therefore self-documenting:
FOR focus_account IN start_id end_id
LOOP
FOR day_in_week IN 1 7
LOOP
FOR month_in_biyear IN 1 24
LOOP
build_schedule (focus_account, day_in_week,
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
month_in_biyear);
END LOOP;
END LOOP;
END LOOP;
Well, that cleared things up! Before I substituted the meaningless loop index names, I would wager
that you were fairly sure the statement:
FOR j IN 1 7
meant that "j" stood for the seven days of the week. And I bet further that you were equally confident
that:
FOR k IN 1 24
meant that "k" represented the hours in a day.
Now that I have provided descriptive names for those index variables, however, you discover that the
innermost loop actually spanned two sets of twelve months (12 × 2 = 24). Your deduction about "k",
while reasonable, was wrong, but it would have been completely impossible to determine this without
looking at the build_schedule code. Given PL/SQL's ability to hide information within packages, this
code might not even be available.
Software programmers should not have to make Sherlock Holmes-like deductions about the meaning
of the start and end range values of the innermost FOR loops in order to understand their purpose.
Use names that self-document the purposes of variables and loops. That way other people will
understand your code and you will remember what your own code does when you review it three
months later.
7.7.2 The Proper Way to Say Goodbye
No matter what kind of loop you are using, there is always only one entry point into the loop: the first
executable statement following the LOOP keyword. Your loops should also have just one way of
leaving the loop. The method of exit, furthermore, should be compatible with the type of loop you
use. The following tips will help you write well-structured and easily maintained loops.
7.7.2.1 Premature FOR loop termination
The syntax of the FOR loop states your intent explicitly and should only be a FOR loop if you know
in advance how many times the loop needs to execute. For example, the following loop is very clear:
FOR month_count IN 1 12
LOOP
analyze_month (month_count);
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
END LOOP;
It states: "I am going to execute the analyze_month procedure 12 times, once for each month in the
year." Straightforward and easy to understand.
Now take a look at the next numeric FOR loop:
FOR year_count IN 1 years_displayed
LOOP
IF year_count > 10 AND :store.status = 'CLOSED'
THEN
EXIT;
END IF;
analyze_month (month_count);
END LOOP;
In this case, the loop boundary states: "Run the loop for the number of years displayed in the form."
Yet in the body of the loop, an IF statement allows a premature termination of the loop. If the year
count (the loop index) exceeds 10 and the current store status is CLOSED, then an EXIT statement is
issued and the loop halts.
This approach is very unstructured and contradictory. The loop boundary states one thing, but the
loop body executes something very different.
You should always let a FOR loop (whether numeric or cursor) complete its stated number of
iterations. If you do need to conditionally halt loop execution, you should choose either an infinite or
a WHILE loop. The above FOR loop could, for example, be easily recoded as follows:
FOR year_count IN 1 LEAST (years_displayed, 11)
LOOP
analyze_month (month_count);
END LOOP;
Similar guidelines apply to the infinite and WHILE loop, as I explore in the next sections.
7.7.2.2 EXIT and EXIT WHEN statements
Neither the FOR loop nor the WHILE loop should use the EXIT and EXIT WHEN statements. You
have already seen why this is so in FOR loops. Consider the following WHILE loop:
WHILE more_records
LOOP
NEXT_RECORD;
EXIT WHEN :caller.name IS NULL;
END LOOP;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
In this case, even though my loop boundary indicates that the body should execute until
more_records evaluates to FALSE, the EXIT WHEN in the loop body bypasses that condition.
Instead of using EXITs in your WHILE loop, you should always rely exclusively on your loop
condition to determine whether the looping should continue. The previous WHILE loop can be
redesigned as follows:
WHILE more_records
LOOP
NEXT_RECORD;
more_records := :caller.name IS NOT NULL;
END LOOP;
7.7.2.3 RETURN statement
The RETURN statement will cause instant termination of a function and return the specified value
back to the calling program. Never use a RETURN statement inside a loop.
Unfortunately, such things have been known to happen. In the following example of terrifically poor
programming practice (taken from an Oracle Corporation reference manual, I am sorry to say), the
FOR loop is interrupted not with an EXIT, which would be unstructured enough, but with a
RETURN statement:
BEGIN
the_rowcount := Get_Group_Row_Count( rg_id );
FOR j IN 1 the_rowcount
LOOP
col_val := Get_Group_Char_Cell( gc_id, j );
IF UPPER(col_val) = UPPER(the_value)
THEN
RETURN j;
END IF;
END LOOP;
END;
The author of this program was in a big hurry to return to the calling program!
Once again, if the loop should be conditionally terminated, do not use a FOR loop. Instead, use a
WHILE or infinite loop and then issue the RETURN after the loop is completed. The following code
replaces the unstructured IF statement shown above:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN
/* Initialize the loop boundary variables. */
row_index := 0;
the_rowcount := Get_Group_Row_Count (rg_id);
/* Use a WHILE loop. */
WHILE row_index <= the_rowcount AND
match_not_found
LOOP
row_index := row_index + 1;
col_val := Get_Group_Char_Cell (gc_id,
row_index);
match_not_found := UPPER (col_val) != UPPER
(the_value)
END LOOP;
/* Now issue the RETURN statement. */
RETURN row_index;
END;
7.7.2.4 GOTO statements inside a loop
The same reasons for avoiding a RETURN apply to the GOTO statement. If you use a GOTO to exit
from a loop, you bypass the logical structure of the loop. You end up with code that is very difficult
to trace, debug, fix, and maintain.
7.7.3 Avoiding the Phony Loop
As I have stated previously, you should not use a numeric FOR loop if you cannot specify in a range
scheme of lowest and highest bounds the number of times the loop must execute. Just because you
know the number of iterations of some code, however, doesn't mean that you should use a loop.
I have run across a number of programs which execute variations on this kind of FOR loop:
FOR i IN 1 2
LOOP
IF i = 1
THEN
give_bonus (president_id, 2000000);
ELSIF i = 2
THEN
give_bonus (ceo_id, 5000000);
END IF;
END LOOP;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This loop provides hefty bonuses to the president and CEO of a company that just went deep into
debt to acquire a competitor. I need to use the loop so the code executes twice to make sure both the
president and CEO receive their just compensation. Right? Wrong. This code should not be inside a
loop. It does not need iteration to perform its job; the LOOP syntax just confuses the issue.
The two sections within the IF-THEN-ELSE construct in the previous example both need to be
executed all the time; this is straight sequential code and should be written as follows:
give_bonus (president_id, 2000000);
give_bonus (ceo_id, 5000000);
7.7.4 PL/SQL Loops Versus SQL Processing
One of the indicators that a numeric FOR loop is being used incorrectly is that the loop index is not
used for anything but traffic control inside the loop. The actual body of executable statements
completely ignores the loop index. When that is the case, there is a good chance that you don't need
the loop at all.
When should you use standard SQL to accomplish your task and when should you rely on PL/SQL
loops? Sometimes the choice is clear: if you do not need to interact with the database, then there is
clearly no need for SQL. In addition, SQL can't always provide the necessary flexibility to get the job
done. Conversely, if you are performing a single record insert into a table then there is no need for a
loop. Often, however, the choice is less obvious. For example, a SELECT statement queries one or
more rows from the database. A cursor FOR loop also queries rows from the database based on a
SELECT statement. In fact, PL/SQL and native SQL often can both accomplish the task at hand.
Given that fact, you will need to choose your implementation according to more subtle issues like
performance and maintainability of code.
Before we look at some examples of scenarios which call for one or the other approach, let's review
the difference between the implicit looping of the SQL set-at-a-time approach and the PL/SQL loop.
SQL statements such as SELECT, UPDATE, INSERT, and DELETE work on a set of data. That set
(actually, a collection of rows from a table or tables) is determined by the WHERE clause (or lack
thereof) in the SQL statement. SQL derives much of its power and effectiveness as a database
language from this set-at-a-time processing approach. There is, however, a drawback, as I mentioned
earlier: SQL often does not give you the flexibility you might need to handle individual records and
specialized logic which must be applied differently to different records.
The PL/SQL cursor offers the ability to access a record at a time and to take action based on the
contents of that specific record. It is not always clear, however, which language component would
best fit the needs of the moment. I have seen a number of programs where developers went overboard
in their drive to PL/SQL-ize the SQL access to their data. This happens most frequently when using a
cursor FOR loop.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The PL/SQL block in the code below moves checked-out pets from the pet hotel occupancy table to
the pets_history table using a cursor FOR loop. For each record fetched (implicitly) from the cursor
(representing a pet who has hit the road), the body of the loop first inserts a record into the
pet_history table and then deletes the record from the occupancy table:
DECLARE
CURSOR checked_out_cur IS
SELECT pet_id, name, checkout_date
FROM occupancy
S checkout_date IS NOT NULL;
BEGIN
FOR checked_out_rec IN checked_out_cur
LOOP
INSERT INTO occupancy_history (pet_id, name,
checkout_date)
VALUES (checked_out_rec.pet_id, checked_out_rec.
name,
checked_out_rec.checkout_date);
DELETE FROM occupancy WHERE pet_id =
checked_out_rec.pet_id;
END LOOP;
END;
This will work just fine. But do we really need to use a cursor FOR loop to accomplish this task?
Suppose 20 animals checked out today. This block of code will then perform 20 distinct inserts and
20 distinct deletes. The same code can be written completely within the SQL language as shown
below:
INSERT INTO occupancy_history (pet_id, name,
checkout_date)
SELECT pet_id, name, checkout_date
FROM occupancy
WHERE checkout_date IS NOT NULL;
DELETE FROM occupancy WHERE checkout_date IS NOT NULL;
Here, a single insert (making use of the INSERT SELECT syntax) and a single delete (which now
checks for the checkout_date and not the employee_id) accomplish the transfer of the data to the
history table. This reliance on native SQL, without the help of PL/SQL, allows you to take full
advantage of array processing. It significantly reduces network traffic in a client-server environment
because only two SQL statements (instead of 40) are passed to the RDBMS.
The cursor FOR loop was not really needed here; the body of the loop did not perform any procedural
logic which could not be handled by SQL itself. If, on the other hand, the program needed to
selectively reject records for the transfer, or otherwise perform procedural logic not possible within
SQL, then either the cursor FOR loop or a WHILE loop would make sense.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét