Oracle PL/SQL Language Pocket Reference- P5

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

110
lượt xem 24
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle PL/SQL Language Pocket Reference- P5: This pocket guide features quick-reference information to help you use Oracle's PL/SQL language. It includes coverage of PL/SQL features in the newest version of Oracle, Oracle8i. It is a companion to Steven Feuerstein and Bill Pribyl's bestselling Oracle PL/SQL Programming. Updated for Oracle8, that large volume (nearly 1,000 pages) fills a huge gap in the Oracle market, providing developers with a single, comprehensive guide to building applications with PL/SQL and building them the right way. ...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle PL/SQL Language Pocket Reference- P5

the Oracle7 architecture allows you to embed many of your business rules directly into your database structure, using database triggers, constraints, and stored procedures. In many cases, you will want to let the RDBMS trap and reject invalid database actions. To do this, you need a way to identify application-specific errors and return information about those error back to the client. This kind of error communication is illustrated in Figure 8.2. Figure 8.2: Error communication from server to client I have called this type of exception "unnamed" and "programmer-defined." The programmer-defined aspect should be clear: because the error is application-specific, you cannot expect PL/SQL to have already defined it for you. The reason this type of exception is also unnamed is that you cannot name or declare an exception within a server-based program or database trigger and have the client-side tool handle that named exception. This identifier simply doesn't cross the great divide between client and server. To get around this problem, Oracle provides a special procedure to allow communication of an unnamed, yet programmer-defined, server-side exception: RAISE_APPLICATION_ERROR. (The use of this procedure and exception type is discussed in Section 8.7, "Client-Server Error Communication" later in this chapter.) The specification for this procedure is as follows: PROCEDURE RAISE_APPLICATION_ERROR (error_number_in IN NUMBER, error_msg_in IN VARCHAR2); where error_number_in is the error number you have assigned to this error. The error_msg_in argument is the message that will be sent back with the error code to the client program. Previous: 8.2 The Oracle PL/SQL Next: 8.4 Determining Exception Section Programming, 2nd Edition Exception-Handling Behavior Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8.2 The Exception Section Book Index 8.4 Determining Exception- Handling Behavior 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: 8.1 Why Chapter 8 Next: 8.3 Types of Exception Handling? Exception Handlers Exceptions 8.2 The Exception Section A PL/SQL block (of which procedures, functions, and anonymous blocks are all instances) consists of up to four parts: the header, declaration section, execution section, and exception section, as shown in the following anonymous block: DECLARE ... declarations ... BEGIN ... executable statements ... [ EXCEPTION ... exception handlers ... ] END; When an exception is raised within the execution section of a PL/SQL block, control passes to the exception section. PL/SQL then scans through the exception handlers to see if that exception is handled. The syntax for an exception section follows: EXCEPTION WHEN exception_name [ OR exception_name ... ] THEN END; You can have multiple exception handlers in a single exception section. The exception handlers are structured much like a conditional CASE statement, as shown below: The Exception Section An English-like Translation Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
EXCEPTION If the NO_DATA_FOUND exception WHEN NO_DATA_FOUND was raised, then execute the first set of THEN statements. executable_statements1; WHEN payment_overdue If the payment is overdue, then execute THEN the second set of statements. executable_statements2; WHEN OTHERS If any other exception is encountered, THEN then execute the third set of statements. executable_statements3; END; An exception is handled if an exception that is named in a WHEN clause matches the exception that was raised. Notice that the WHEN clause traps errors only by exception name, not by error codes. If a match is found, then the executable statements associated with that exception are run. If the exception that has been raised is not handled or does not match any of the named exceptions, the executable statements associated with the WHEN OTHERS clause -- if present -- will be run. The WHEN OTHERS clause is optional; if it is not present, then any unhandled exception is immediately raised in the enclosing block, if any. If the exception is not handled by any PL/SQL block, then the error number and message are presented directly to the user of the application. The exception is, in other words, unhandled and it disrupts the execution of the application. Previous: 8.1 Why Oracle PL/SQL Next: 8.3 Types of Exception Handling? Programming, 2nd Edition Exceptions 8.1 Why Exception Handling? Book Index 8.3 Types of Exceptions 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.7 Tips for PL/ Chapter 8 Next: 8.2 The Exception SQL Loops 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: q An error generated by the system (such as "out of memory" or "duplicate value in index") q An error caused by a user action q 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: q 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). q 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. q 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/ Oracle PL/SQL Next: 8.2 The Exception SQL Loops Programming, 2nd Edition 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 Chapter 7 Next: 8. Exception Handlers Loop Execution Loops 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
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.
Previous: 7.6 Managing Oracle PL/SQL Next: 8. Exception Handlers Loop Execution Programming, 2nd Edition 7.6 Managing Loop Execution Book Index 8. Exception Handlers 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.5 The WHILE Chapter 7 Next: 7.7 Tips for PL/SQL Loop Loops Loops 7.6 Managing Loop Execution I've explained how to construct the different kinds of PL/SQL loops. The topics in this section address the following nuances of loop execution: Labels You can associate a label with a loop and use that label to increase your control over loop execution. Scope The loop boundary creates a scope similar to that of a PL/SQL block. Termination There is only one way to enter a loop, but a number of ways you can exit your loop. 7.6.1 Loop Labels You can associate a name with a loop by using a label. A loop label in PL/SQL has the following format: where label_name is the name of the label. (By the way, this is the same format used for GOTO labels.) In order to associate a name with a loop, however, the loop label must appear just before the LOOP statement as shown below: FOR emp_rec IN emp_cur LOOP ... END LOOP; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The label can also appear optionally after the END LOOP reserved words, as the following example demonstrates: WHILE year_number
7.6.1.2 Loop termination using labels You can affect loop execution by adding a loop label after the EXIT keyword in the EXIT statement of a loop, as follows: EXIT loop_label; EXIT loop_label WHEN condition; When you specify a loop label with the EXIT statement, PL/SQL terminates the specified loop. Consider the last example with nested year and month loops. You might encounter a condition in which both loops should be immediately terminated. The usual, unlabeled EXIT statement inside the month loop would simply halt the month processing for the current year. The year loop would, however, continue its iterations. If the EXIT statement includes the year_loop label, both loops will halt execution: WHILE year_number
variables do not exist. A similar kind of scope is created in the body of a loop. 7.6.2.1 Scope in FOR loops In both numeric and cursor FOR loops, the scope of the loop index is restricted to the body of the loop. You cannot make reference to this implicitly declared variable in code before or after the loop. If you declare a variable of the same name as the loop index, PL/SQL considers that to be a different variable. It will not be used within the loop. The loop index always takes precedence over a variable of the same name declared outside the scope of the loop. Suppose you have the following code: PROCEDURE calc_revenue (year_in IN NUMBER) IS month_number NUMBER (2) := 6; BEGIN FOR month_number IN 1 .. 12 LOOP calc_rev_for_month (month_number); END LOOP; END; The assignment of 6 to month_number in the declaration section has no impact whatsoever on the loop. Within the FOR loop, any reference to month_number is evaluated according to the current value of the loop index. If you insist on declaring a variable whose name is the same as that of a loop index, you can use dot notation to qualify your references to these variables. In the following example I have a duplicate use of the month_number identifier: PROCEDURE calc_revenue (year_in IN NUMBER) IS month_number NUMBER (2) := 6; BEGIN FOR month_number IN 1 .. 12 LOOP IF calc_revenue.month_number < 6 THEN ... END IF; calc_rev_for_month (month_number); END LOOP; END; Inside the loop, my first reference to month_number is qualified by the procedure name Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
(calc_revenue.month_number). As a result, the compiler can obtain the right value for that month_number (6), while also using the loop index value in the call to calc_rev_for_month. Of course, you can and should avoid this kind of confusion by using distinct names for your variables and loop indexes. 7.6.2.2 Scope with labels If you define a label for a loop, then this label can be used to qualify the name of identifiers (loop indexes and locally-declared variables) inside the loop. Once the loop has terminated, you cannot use the loop label to qualify identifiers. The scope of that label, in other words, is the boundary and body of the loop. In the following example, I created two nested loops, both of which use a loop index named date_number. (Warning! Do not try this at home. Although it will compile, it can be dangerous to your sanity.) FOR date_number IN 1994 .. 1999 LOOP FOR date_number IN 1 .. 12 LOOP IF year_loop.date_number = 1994 AND date_number = 1 THEN first_month_processing; END IF; END LOOP month_loop; END LOOP year_loop; The IF statement references the date_number loop index of both the outer and inner loops by prefixing the outer loop's name to the first reference to date_number, I tell the compiler which variable I want it to use. Again, you would be much better off simply changing the name of one or both of the loop indexes; date_number is much too vague a name for either of these loops. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.