Oracle PL/SQL Language Pocket Reference- P7

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

Thêm vào BST

Báo xấu

122
lượt xem 18
download

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

Oracle PL/SQL Language Pocket Reference- P7: 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- P7

as a whole. 6.3.2.2 Vulnerability to data errors If an implicit SELECT statement returns more than one row, it raises the TOO_MANY_ROWS exception. When this happens, execution in the current block terminates and control is passed to the exception section. Unless you deliberately plan to handle this scenario, use of the implicit cursor is a declaration of faith. You are saying, "I trust that query to always return a single row!" It may well be that today, with the current data, the query will only return a single row. If the nature of the data ever changes, however, you may find that the SELECT statement which formerly identified a single row now returns several. Your program will raise an exception. Perhaps this is what you will want. On the other hand, perhaps the presence of additional records is inconsequential and should be ignored. With the implicit query, you cannot easily handle these different possibilities. With an explicit query, your program will be protected against changes in data and will continue to fetch rows without raising exceptions. 6.3.2.3 Diminished programmatic control The implicit cursor version of a SELECT statement is a black box. You pass the SQL statement to the SQL layer in the database and it returns (you hope) a single row. You can't get inside the separate operations of the cursor, such as the open and close stages. You can't examine the attributes of the cursor -- to see whether a row was found, for example, or if the cursor has already been opened. You can't easily apply traditional programming control constructs, such as an IF statement, to your data access. Sometimes you don't need this level of control. Sometimes you just think you don't need this level of control. I have found that if I am going to build programs in PL/SQL, I want as much control as I can possibly get. Always Use Explicit Cursors! My rule of thumb is always to use an explicit cursor for all SELECT statements in my applications, even if an implicit cursor might run a little bit faster and even if, by coding an explicit cursor, I have to write more code (declaration, open, fetch, close). By setting and following this clear-cut rule, I give myself one less thing to think about. I do not have to determine if a particular SELECT statement will return only one row and therefore be a candidate for an implicit cursor. I do not have to wonder about the conditions under which a single-row query might suddenly return more than one row, thus requiring a TOO_MANY_ROWS exception handler. I am guaranteed to get vastly improved programmatic control over that data access and more finely- tuned exception handling for the cursor. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
6.3.3 Explicit Cursors An explicit cursor is a SELECT statement that is explicitly defined in the declaration section of your code and, in the process, assigned a name. There is no such thing as an explicit cursor for UPDATE, DELETE, and INSERT statements. With explicit cursors, you have complete control over how to access information in the database. You decide when to OPEN the cursor, when to FETCH records from the cursor (and therefore from the table or tables in the SELECT statement of the cursor) how many records to fetch, and when to CLOSE the cursor. Information about the current state of your cursor is available through examination of the cursor attributes. This granularity of control makes the explicit cursor an invaluable tool for your development effort. Let's look at an example. The following anonymous block looks up the employee type description for an employee type code: 1 DECLARE 2 /* Explicit declaration of a cursor */ 3 CURSOR emptyp_cur IS 4 SELECT emptyp.type_desc 5 FROM employees emp, employee_type emptyp 6 WHERE emp.type_code = emptyp.type_code; 7 BEGIN 8 /* Check to see if cursor is already open. If not, open it. */ 9 IF NOT emptyp_cur%ISOPEN 10 THEN 11 OPEN emptyp_cur; 12 END IF; 13 14 /* Fetch row from cursor directly into an Oracle Forms item */ 15 FETCH emptyp_cur INTO :emp.type_desc; 16 17 /* Close the cursor */ 18 CLOSE emptyp_cur; 19 END; This PL/SQL block performs the following cursor actions: Action Line(s) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Declare the cursor 3 Open the cursor (if not already open) 9, 11 Fetch one or more rows from the cursor 15 Close the cursor 18 The next few sections examine each of these steps in more detail. For the remainder of this chapter, unless noted otherwise, the word "cursor" refers to the explicit cursor. Previous: 6.2 Cursors in PL/ Oracle PL/SQL Next: 6.4 Declaring Cursors SQL Programming, 2nd Edition 6.2 Cursors in PL/SQL Book Index 6.4 Declaring Cursors 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: 6.1 Transaction Chapter 6 Next: 6.3 Implicit and Management Database Interaction and Explicit Cursors Cursors 6.2 Cursors in PL/SQL When you execute a SQL statement from PL/SQL, the Oracle RDBMS assigns a private work area for that statement. This work area contains information about the SQL statement and the set of data returned or affected by that statement. The PL/SQL cursor is a mechanism by which you can name that work area and manipulate the information within it. In its simplest form, you can think of a cursor as a pointer into a table in the database. For example, the following cursor declaration associates the entire employee table with the cursor named employee_cur: CURSOR employee_cur IS SELECT * FROM employee; Once I have declared the cursor, I can open it: OPEN employee_cur; And then I can fetch rows from it: FETCH employee_cur INTO employee_rec; and, finally, I can close the cursor: CLOSE employee_cur; In this case, each record fetched from this cursor represents an entire record in the employee table. You can, however, associate any valid SELECT statement with a cursor. In the next example I have a join of three tables in my cursor declaration: DECLARE CURSOR joke_feedback_cur IS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
SELECT J.name, R.laugh_volume, C.name FROM joke J, response R, comedian C WHERE J.joke_id = R.joke_id AND J.joker_id = C.joker_id; BEGIN ... END; Here, the cursor does not act as a pointer into any actual table in the database. Instead, the cursor is a pointer into the virtual table represented by the SELECT statement (SELECT is called a virtual table because the data it produces has the same structure as a table -- rows and columns -- but it exists only for the duration of the execution of the SQL statement). If the triple-join returns 20 rows, each row containing the three columns in the preceding example, then the cursor functions as a pointer into those 20 rows. 6.2.1 Types of Cursors You have lots of options in PL/SQL for executing SQL, and all of them occur as some type of cursor. Generally, there are two types of SQL that you can execute in PL/SQL: static and dynamic. SQL is static if the content of the SQL statement is determined at compile time. A SQL statement is dynamic if it is constructed at runtime and then executed. Dynamic SQL is made possible in PL/SQL only through the use of the DBMS_SQL built-in package (see Appendix C, Built-In Packages). All other forms of SQL executed inside a PL/SQL program represent static SQL; these forms of cursors are the focus of the remainder of this chapter. Even within the category of static SQL, we have further differentiation. With the advent of PL/SQL Release 2.3, you can choose between two distinct types of cursor objects: Static cursor objects These are the really static cursors of PL/SQL. The SQL is determined at compile time, and the cursor always refers to one SQL statement, which is known at compile time. The examples shown earlier in this chapter are static cursors. Unless otherwise noted, any reference to "static cursor" refers to this sub-category of static (as opposed to dynamic) cursors. Cursor variables You can declare a variable which references a cursor object in the database. Your variable may refer to different SQL statements at different times (but that SQL is defined at compile time, not run time). The cursor variable is one of the newest enhancements to PL/SQL and will be unfamiliar to most programmers. Cursor variables act as references to cursor objects. As a true variable, a cursor Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
variable can change its value as your program executes. The variable can refer to different cursor objects (queries) at different times. You can also pass a cursor variable as a parameter to a procedure or function. Cursor variables are discussed later in this chapter. Static PL/SQL cursors have been available since PL/SQL Version 1. The static version of cursors "hardcodes" a link between the cursor name and a SELECT statement. The static cursor itself comes in two flavors: implicit and explicit. PL/SQL declares and manages an implicit cursor every time you execute a SQL DML statement, such as an INSERT or a SELECT that returns a single row. You, the programmer, define your own explicit cursors in your code. You must use an explicit cursor when you need to retrieve more than one row of data at a time through a SELECT statement. You can then use the cursor to fetch these rows one at a time. The set of rows returned by the query associated with an explicit cursor is called the active set or result set of the cursor. The row to which the explicit cursor points is called the current row of the result set. The bulk of this chapter is devoted to the management of static, explicit cursors. All information about cursor variables is localized in Section 6.12, "Cursor Variables". Any references to PL/SQL cursors and cursor characteristics outside of that section will pertain to static cursors. 6.2.2 Cursor Operations Regardless of the type of cursor, PL/SQL performs the same operations to execute a SQL statement from within your program: PARSE The first step in processing an SQL statement is to parse it to make sure it is valid and to determine the execution plan (using either the rule-based or cost-based optimizer). BIND When you bind, you associate values from your program (host variables) with placeholders inside your SQL statement. For static SQL, the SQL engine itself performs these binds. When you use dynamic SQL, you explicitly request a binding of variable values. OPEN When you open a cursor, the bind variables are used to determine the result set for the SQL statement. The pointer to the active or current row is set to the first row. Sometimes you will not explicitly open a cursor; instead the PL/SQL engine will perform this operation for you (as with implicit cursors). EXECUTE In the execute phase, the statement is run within the SQL engine. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FETCH If you are performing a query, the FETCH command retrieves the next row from the cursor's result set. Each time you fetch, PL/SQL moves the pointer forward in the result set. When working with explicit cursors, remember that if there are no more rows to retrieve, then FETCH does nothing (it does not raise an error). CLOSE The CLOSE statement closes the cursor and releases all memory used by the cursor. Once closed, the cursor no longer has a result set. Sometimes you will not explicitly close a cursor; instead the PL/SQL engine will perform this operation for you (as with implicit cursors). Figure 6.1 shows how some of these different operations are used to fetch information from the database into your PL/SQL program. Figure 6.1: Using cursor operations to fetch database information into your program Previous: 6.1 Transaction Oracle PL/SQL Next: 6.3 Implicit and Management Programming, 2nd Edition Explicit Cursors 6.1 Transaction Management Book Index 6.3 Implicit and Explicit Cursors 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: 5.2 Sequential Chapter 6 Next: 6.2 Cursors in PL/ Control Statements SQL 6. Database Interaction and Cursors Contents: Transaction Management Cursors in PL/SQL Implicit and Explicit Cursors Declaring Cursors Opening Cursors Fetching from Cursors Column Aliases in Cursors Closing Cursors Cursor Attributes Cursor Parameters SELECT FOR UPDATE in Cursors Cursor Variables Working with Cursors PL/SQL is tightly integrated with the Oracle database via the SQL language. From within PL/SQL, you can execute any DML (data manipulation language) statements, including INSERTs, UPDATEs, DELETEs, and, of course, queries. You can also join multiple SQL statements together logically as a transaction, so that they are either saved ("committed" in SQL parlance) together or rejected in their entirety (rolled back). This chapter examines the SQL statements available inside PL/SQL to manage transactions. It then moves on to cursors, which give you a way to fetch and process database information in your PL/SQL program. 6.1 Transaction Management The Oracle RDBMS provides a very robust transaction model, as you might expect for a relational database. You (or more precisely, your application code) determine what constitutes a transaction, the logical unit of work that must be either saved together with a COMMIT statement or rolled back together with a ROLLBACK statement. A transaction begins implicitly with the first SQL statement issued since the last COMMIT or ROLLBACK (or with the start of a session). Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PL/SQL provides the following statements for transaction management: COMMIT Saves all outstanding changes since the last COMMIT or ROLLBACK and releases all locks. ROLLBACK Erases all outstanding changes since the last COMMIT or ROLLBACK and releases all locks. ROLLBACK TO SAVEPOINT Erases all changes made since the specified savepoint was established. SAVEPOINT Establishes a savepoint, which then allows you to perform partial ROLLBACKs. SET TRANSACTION Allows you to begin a read-only or read-write session, establish an isolation level, or assign the current transaction to a specified rollback segment. LOCK TABLE Allows you to lock an entire database table in the specified mode. This overrides the default row-level locking usually applied to a table. These statements are explained in more detail in the following sections. 6.1.1 The COMMIT Statement When you COMMIT, you make permanent any changes made by your session to the database in the current transaction. Once you commit, your changes will be visible to other Oracle sessions or users. The syntax for the COMMIT statement is: COMMIT [WORK] [COMMENT text]; The WORK keyword is optional and can be used to improve readability. The COMMENT keyword specifies a comment which is then associated with the current transaction. The text must be a quoted literal and can be no more than 50 characters in length. The COMMENT text is usually employed with distributed transactions. This text can be handy for examining and resolving in-doubt transactions within a two-phase commit framework. It is stored in the data dictionary along with the transaction ID. Note that COMMIT releases any row and table locks issued in your session, such as with a SELECT FOR UPDATE statement. It also erases any savepoints issued since the last COMMIT or Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ROLLBACK. Once you COMMIT your changes, you cannot roll them back with a ROLLBACK statement. The following statements are all valid uses of the COMMIT: COMMIT; COMMIT WORK; COMMIT COMMENT 'maintaining account balance'. 6.1.2 The ROLLBACK Statement When you ROLLBACK, you undo some or all changes made by your session to the database in the current transaction. Why would you want to erase changes? From an ad hoc SQL standpoint, the ROLLBACK gives you a way to erase mistakes you might have made, as in: DELETE FROM orders; "No, no! I meant to delete only the orders before May 1995!" No problem, just issue ROLLBACK. From an application coding standpoint, ROLLBACK is important because it allows you to clean up or restart from a "clean state" when a problem occurs. The syntax for the ROLLBACK statement is: ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name]; There are two basic ways to use ROLLBACK: without parameters or with the TO clause to indicate a savepoint at which the ROLLBACK should stop. The parameterless ROLLBACK undoes all outstanding changes in your transaction. The ROLLBACK TO version allows you to undo all changes and release all acquired locks which were issued since the savepoint identified by savepoint_name was marked (see the next section on the SAVEPOINT statement for more information on how to mark a savepoint in your application). The savepoint_name is an undeclared Oracle identifier. It cannot be a literal (enclosed in quotes) or variable name. All of the following uses of ROLLBACK are valid: ROLLBACK; ROLLBACK WORK; ROLLBACK TO begin_cleanup; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
All of the following uses of ROLLBACK are invalid: ROLLBACK SAVEPOINT; -- ORA-02181: invalid option to ROLLBACK WORK -- Must use TO keyword before SAVEPOINT. ROLLBACK WORK TO; -- ORA-02182: save point name expected -- Must specify savepoint name. ROLLBACK TO SAVEPOINT 'favorite_movies'; -- ORA-03001: Unimplemented feature -- Savepoint cannot be in quotes. When you roll back to a specific savepoint, all savepoints issued after the specified savepoint_name are erased. The savepoint to which you roll back is not, however, erased. This means that you can restart your transaction from that point and, if necessary, roll back to that same savepoint if another error occurs. Immediately before you execute an INSERT, UPDATE, or DELETE, PL/SQL implicitly generates a savepoint. If your DML statement then fails, a rollback is automatically performed to that implicit savepoint. In this way, only that last DML statement is undone. 6.1.3 The SAVEPOINT Statement SAVEPOINT gives a name to and marks a point in the processing of your transaction. This marker allows you to ROLLBACK TO that point, erasing any changes and releasing any locks issued after that savepoint, but preserving any changes and locks which occurred before you marked the savepoint. The syntax for the SAVEPOINT statement is: SAVEPOINT savepoint_name; where savepoint_name is an undeclared identifier. This means that it must conform to the rules for an Oracle identifier (up to 30 characters in length, starting with a letter, containing letters, numbers and #, $, or _ ), but that you do not need to (nor can you) declare that identifier. Savepoints are not scoped to PL/SQL blocks. If you reuse a savepoint name within the current transaction, that savepoint is "moved" from its original position to the current point in the transaction, regardless of the procedure, function, or anonymous block in which the SAVEPOINT statements are executed. As a corollary, if you issue a SAVEPOINT inside a recursive program, a new SAVEPOINT is executed at each level of recursion, but you can only roll back to the most recently marked savepoint. 6.1.4 The SET TRANSACTION Statement Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The SET TRANSACTION statement allows you to begin a read-only or read-write session, establish an isolation level, or assign the current transaction to a specified rollback segment. This statement must be the first SQL statement processed in a transaction and it can appear only once. This statement comes in the following four flavors: SET TRANSACTION READ ONLY; This version defines the current transaction as read-only. In a read-only transaction, all subsequent queries only see those changes which were committed before the transaction began (providing a read- consistent view across tables and queries). This statement is useful when you are executing long- running, multiple query reports and you want to make sure that the data used in the report is consistent: SET TRANSACTION READ WRITE; This version defines the current transaction as read-write: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|READ COMMITTED; This version defines how transactions that modify the database should be handled. You can specify a serializable or read-committed isolation level. When you specify SERIALIZABLE, a data manipulation statement (update, insert, delete) which attempts to modify a table already modified in an uncommitted transaction will fail. To execute this command, you must set the database initialization parameter COMPATIBLE to 7.3.0 or higher. If you specify READ COMMITTED, a DML which requires row-level locks held by another transaction will wait until those row locks are released: SET TRANSACTION USE ROLLBACK SEGMENT rollback_segname; This version assigns the current transaction to the specified rollback segment and establishes the transaction as read-write. This statement cannot be used in conjunction with SET TRANSACTION READ ONLY. 6.1.5 The LOCK TABLE Statement This statement allows you to lock an entire database table with the specified lock mode. By doing this, you can share or deny access to that table while you perform operations against it. The syntax for this statement is: LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT]; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
where table_reference_list is a list of one or more table references (identifying either a local table/ view or a remote entity through a database link), and lock_mode is the mode of the lock, which can be one of the following: ROW SHARE ROW EXCLUSIVE SHARE UPDATE SHARE SHARE ROW EXCLUSIVE EXCLUSIVE If you specify the NOWAIT keyword, Oracle will not wait for the lock if the table has already been locked by another user. If you leave out the NOWAIT keyword, Oracle waits until the table is available (and there is no set limit on how long Oracle will wait). Locking a table never stops other users from querying or reading the table. The following LOCK TABLE statements show valid variations: LOCK TABLE emp IN ROW EXCLUSIVE MODE; LOCK TABLE emp, dept IN SHARE MODE NOWAIT; LOCK TABLE scott.emp@new_york IN SHARE UPDATE MODE; Now that you know the "macro" commands for managing transactions from within a PL/SQL application, let's move on to cursors; you will use cursors (in one form or another) to create transactions (i.e., specify the SQL statements which make up the transaction). Previous: 5.2 Sequential Oracle PL/SQL Next: 6.2 Cursors in PL/ Control Statements Programming, 2nd Edition SQL 5.2 Sequential Control Book Index 6.2 Cursors in PL/SQL Statements 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: 5.1 Conditional Chapter 5 Next: 6. Database Control Statements Conditional and Sequential Interaction and Cursors Control 5.2 Sequential Control Statements Certain PL/SQL control structures offer structured methods for processing executable statements in your program. You use an IF statement to test a condition to determine which parts of the code to execute. You use one of the LOOP variations (described in Chapter 7, Loops) to execute a section of code more than once. In addition to these well-structured approaches to program control, PL/SQL offers two other statements to handle out of the ordinary requirements for sequential processing: GOTO and NULL. The GOTO statement allows you to perform unconditional branching to another executable statement in the same execution section of a PL/SQL block. The NULL statement gives you a way to tell the compiler to do...absolutely nothing. The following sections explain the implementation of the GOTO and NULL statements in PL/SQL. As with other constructs in the language, use them with care and use them appropriately, and your programs will be stronger for it. 5.2.1 The GOTO Statement The GOTO statement performs unconditional branching to a named label. The general format for a GOTO statement is: GOTO label_name; where label_name is the name of a label. This GOTO label is defined in the program as follows: You must surround the label name with double enclosing angle brackets (>). In this case, the label names a loop. This label can then be appended to the END LOOP statement, making the termination of the loop more visible. You can also issue an EXIT statement for a particular labeled loop from within another (enclosed) loop. Finally, you can GOTO that loop label, even though it was Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
"designed" for a loop. (Chapter 7 describes the details of loop processing.) When PL/SQL encounters a GOTO statement, it immediately shifts control to the first executable statement following the label. Contrary to popular opinion (including mine), the GOTO statement can come in handy. There are cases where a GOTO statement can simplify the logic in your program. On the other hand, PL/SQL provides so many different control constructs and modularization techniques that you can almost always find a better way to do something than with a GOTO. There are several restrictions regarding the GOTO statement, described in the sections below. 5.2.1.1 At least one executable statement must follow a label A label itself is not an executable statement (notice that it does not have a semicolon (;) after the label brackets), so it cannot take the place of one. All of the uses of the loop label in the following blocks are illegal because the labels are not followed by an executable statement: IF status_inout = 'COMPLETED' THEN /* Illegal! */ ELSE schedule_activity; END IF; DECLARE CURSOR company_cur IS ...; BEGIN FOR company_rec IN company_cur LOOP apply_bonuses (company_rec.company_id); /* Illegal! */ END LOOP; END; FUNCTION new_formula (molecule_in IN NUMBER) RETURN VARCHAR2 IS BEGIN ... construct formula for molecule ... RETURN formula_string; /* Illegal! */ END; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
5.2.1.2 Target labels and scope of GOTO The target label must be in the same scope as the GOTO statement. In the context of the GOTO statement, each of the following constructs maintains its own scope: functions, procedures, anonymous blocks, IF statements, LOOP statements, and exception handlers. All of the code examples below generate the same PL/SQL error: PLS-00375: illegal GOTO statement; this GOTO cannot branch to label q IF conditions. The only way to enter an IF statement is through an evaluation to TRUE of an IF condition. Therefore, this code produces an error: GOTO label_inside_IF; IF status = 'NEW' THEN /* Out of scope! */ show_new_one; END IF; q BEGIN statements. The only way to enter a block-within-a-block is through the sub-block's BEGIN statement. PL/SQL insists on orderly entrances and exits. This code produces an error because it doesn't comply with this structure: GOTO label_inside_subblock; BEGIN /* Crosses block boundary! */ NULL; END; q Scope of IF statements. Each IF clause of the IF statement is its own scope. A GOTO may not transfer from one clause to another. This code produces an error: IF status = 'NEW' THEN GOTO old_status; /* Crosses IF clause boundary! */ ELSIF status = 'OLD' THEN GOTO new_status; /* Crosses IF clause boundary! */ END IF; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
q Don't jump into the middle of a loop. You cannot jump into the middle of a loop with a GOTO. This code produces an error: FOR month_num IN 1 .. 12 LOOP schedule_activity (month_num); END LOOP; GOTO do_a_month; /* Can't go back into loop. */ q Don't GOTO a local module. You also cannot issue a GOTO inside of a module to a label in the main body. This code produces an error: DECLARE FUNCTION local_null IS BEGIN NULL; END; BEGIN GOTO descrip_case_statement; /* Label not visible here. */ END; 5.2.1.3 Target labels and PL/SQL blocks The target label must be in the same part of the PL/SQL block as the GOTO statement. A GOTO in the executable section may not go to a label in the exception section. Similarly, a GOTO in the exception section may not go to a label in the executable section. A GOTO in an exception handler may reference a label in the same handler. The code example below generates the same PL/SQL error shown in the previous section (PLS-00375): BEGIN /* || The label and GOTO must be in the same section! */ GOTO out_of_here; EXCEPTION WHEN OTHERS THEN /* Out of scope! */ NULL; END; 5.2.2 The NULL Statement Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Usually when you write a statement in a program, you want it to do something. There are cases, however, when you want to tell PL/SQL to do absolutely nothing, and that is where the NULL statement comes in handy. The NULL statement has the following format: NULL; Well, you wouldn't want a do-nothing statement to be complicated, would you? The NULL statement is simply the reserved word followed by a semicolon (;) to indicate that this is a statement and not the NULL value reserved word. The NULL statement does nothing except pass control to the next executable statement. Why would you want to use the NULL statement? There are several reasons, described in the following sections. 5.2.2.1 Improving the readability of your program There are many situations in your program where you logically do not want to take any action. In most of these cases, PL/SQL will let you write nothing and the program will execute as you wish. The only drawback is the ambiguity surrounding this solution: it is not clear to a person examining the program that you knowingly did not take any action. Consider the IF statement. When you write an IF statement you do not have to include an ELSE clause. To produce a report based on a selection, you can code: IF :report.selection = 'DETAIL' THEN exec_detail_report; END IF; What should the program have been doing if the report selection is not `DETAIL'? One would assume that the program was supposed to do nothing. But because this is not explicitly stated in the code, one is left to wonder if perhaps there was an oversight. If, on the other hand, you include an explicit ELSE clause that does nothing, you state very clearly, "Don't worry, I thought about this possibility and I really want nothing to happen." IF :report.selection = 'DETAIL' THEN exec_detail_report; ELSE NULL; END IF; 5.2.2.2 Nullifying the effect of a raised exception Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The optional exception section of a program contains one or more exception handlers. These handlers trap and handle errors that have been raised in your program. The structure and flow of the exception section is similar in structure and flow to a conditional case statement, as follows: EXCEPTION WHEN THEN executable_statements; WHEN THEN executable_statements; WHEN OTHERS THEN executable_statements; END; If is raised, then execute its statements; if is raised, then execute its statements; and so on. The WHEN OTHERS clause handles any exceptions not handled in the previous WHEN clauses (it is just like the ELSE clause of the IF statement). You can use the NULL statement to make sure that a raised exception halts execution of the current PL/SQL block, but does not propagate any exceptions to enclosing blocks: PROCEDURE calc_avg_sales BEGIN :sales.avg := :sales.month1 / :sales.total; EXCEPTION WHEN ZERO_DIVIDE THEN :sales.avg := 0; RAISE FORM_TRIGGER_FAILURE; WHEN OTHERS THEN NULL; END; If total sales are zero, then an exception is raised, the average is set to zero, and the trigger processing in Oracle Forms is halted. If any other exceptions occur (such as VALUE_ERROR, which would be raised if the number generated by the calculation is larger than the sales.avg item allows), then the procedure does nothing and processing continues. Even though the WHEN OTHERS clause of the exception section is like the ELSE clause of an IF statement, the impact of a NULL statement is very different in each of these statements. The addition of an ELSE NULL clause to the IF statement has an impact only on the readability of the code. The Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
IF statement executes in precisely the same way it would have without the ELSE NULL clause. When you include an exception handler with the NULL executable statement, you are saying, in effect: "End processing in the current PL/SQL block and move to the enclosing block, but otherwise take no action." This is very different from leaving out the exception handler altogether. If there is no exception handler, then PL/SQL raises the exception in the enclosing block again, and continues to do so until the last block, at which point it becomes an unhandled exception and halts the program. A "null" exception handler passes control back to the enclosing block, but does not cause the exception to be raised again. See Chapter 8, Exception Handlers, for more detailed information about exceptions. 5.2.2.3 Supporting top-down design of modules With top-down design (also known as top-down decomposition), you move from a general description of your system through step-by-step refinements of that idea to the modules which implement that system, and finally to the code that implements the modules. By moving through levels of abstraction, your mind concentrates on a relatively small number of issues at a time and can better process the details. From a programming perspective, you implement top-down design by creating "stubs," or dummy programs. A stub will have the name and parameter list you need, but not have anything under the covers. Using stubs you define the API (application programmatic interface), which indicates the way that the different modules connect to each other. In order for a PL/SQL program to compile, it must have at least one executable statement. The smallest, most nonintrusive program you can build will therefore be composed of a single NULL statement. Here are sample stubs for both a procedure and a function: PROCEDURE revise_timetable (year_in IN NUMBER) IS BEGIN NULL; END; FUNCTION company_name (company_id_in IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN NULL; END; The NULL statement gives you a way to quickly cobble together the programmatic interface you need to formulate the functional hierarchy of your application. I'd like to say that this is half the battle, but I am not really sure that is so. You still have to figure out how to fill in all of those stubs. 5.2.2.4 Using NULL with GOTO to avoid additional statement execution Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.