SQL Statement as Loop
You actually can think of a SQL statement such as SELECT as a loop. After all, such a statement specifies an action to be taken on a set of data; the SQL engine then "loops through" the data set and applies the action. In some cases, you will have a choice between using a PL/SQL loop and a SQL statement to do the same or similar work. Let's look at an example and then draw some conclusions about how you can decide which approach to take.
I need to write a program to move the information for pets who have checked out of the pet hotel from the occupancy table to the occupancy_history table. As a seasoned PL/SQL developer, I immediately settle on a cursor FOR loop. For each record fetched (implicitly) from the cursor (representing a pet who has checked out), the body of the loop first inserts a record into the occupancy_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 WHERE 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 code does the trick. But was it necessary to do it this way? I can express precisely the same logic and get the same result with nothing more than an INSERT-SELECT FROM followed by a DELETE, as shown here:
What are the advantages to this approach? I have written less code, and my code will run more efficiently because I have reduced the number of "context switches" (moving back and forth between the PL/SQL and SQL execution engines). I execute just a single INSERT and a single DELETE.
There are, however, disadvantages to the 100% SQL approach. SQL statements are generally all-or-nothing propositions. In other words, if any one of those individual rows from occupancy_history fails, then the entire INSERT fails; no records are inserted or deleted. Also, the WHERE clause had to be coded twice. Though not a significant factor in this example, it may well be when substantially more complex queries are involved. The initial cursor FOR loop thus obviated the need to potentially maintain complex logic in multiple places.
PL/SQL offers more flexibility as well. Suppose, for example, that I want to transfer as many of the rows as possible, and simply write a message to the error log for any transfers of individual rows that fail. In this case, I really do need to rely on the cursor FOR loop, but with the added functionality of an EXCEPTION section:
BEGIN FOR checked_out_rec IN checked_out_cur LOOP BEGIN INSERT INTO occupancy_history ... DELETE FROM occupancy ... EXCEPTION WHEN OTHERS THEN log_checkout_error (checked_out_rec); END; END LOOP;END;;PL/SQL offers the ability to access and process a single row at a time, and to take action (and, perhaps, complex procedural logic based on the contents of that specific record). When that's what you need, use a blend of PL/SQL and SQL. If, on the other hand, your requirements allow you to use native SQL, you will find that you can use less code and that it will runmoreefficiently.
Chapter 6. Exception Handlers
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, and so on. It is devilishly difficult, from both a psychological standpoint and a resources perspective, to focus on the negative: for example, 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 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 somehow always find just the right sequence of keystrokes to make a form implode. The challenge is clear: either you spend the time up-front to properly debug and bulletproof your programs, or you 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. It is entirely feasible within the PL/SQL language to build an application that fully protects the user and the database from errors.
6.1 How PL/SQL Deals with Errors
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 it's 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 the current block, 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.
Figure 6-1 illustrates how control is transferred to the exception section when an exception is raised.