Figure 3-4. Anonymous blocks nested three levels deep

Other terms you may hear for nested block are enclosed block, child block, or sub-block; the outer PL/SQL block may be called the enclosing block or the parent block.

3.1.5 Scope

The general advantage of nesting a block is that you create a scope for all the declared objects and executable statements in that block. You can use this feature not only to improve your control over runtime behavior, but also to reduce the likelihood of a programmer's accidentally modifying the wrong variable.

Variables, exceptions, modules, and a few other structures are local to the block that declares them. When the block stops executing, you can no longer reference any of these structures. For example, in the calc_totals procedure above, I can reference elements from the outer block, like the year_total variable, anywhere in the program; however, elements declared within an inner block are not available to the outer block.

One of the most common reasons to create a nested block is to take advantage of the exception section that comes with that block. By trapping the exception inside the program, you can allow your program to continue processing rather than ending with an error. For example, if I'm unsure that a particular record exists in the database, I might want to do something like this:

BEGIN

BEGIN /* nested block */

INSERT INTO book_copies (isbn, qty)

VALUES (:newisbn, :copies);

EXCEPTION

WHEN DUP_VAL_ON_INDEX

THEN

UPDATE book_copies SET qty = :copies WHERE isbn = :newisbn;

END; /* nested block */

books.distribute_inventory_report;

END;

Using the nested block provides a convenient way for me to ensure that the table has the desired record before running the books.distribute_inventory_report procedure. It also prevents the program from aborting due to a DUP_VAL_ON_INDEX exception. Of course, for other errors, such as ORA-00942: Table or view does not exist, you might actually want the program to terminate. The important point to keep in mind is that you are in control and get to choose what happens. See Chapter 6 for more information about error handling.

3.1.6 Visibility

Once a variable is in scope, another important property is its visibility—that is, whether you can refer to it using only its name, or whether you need to attach a prefix in front of it.

3.1.6.1 "Visible" identifiers

First, I'd like to make an observation about the simple case:

DECLARE

first_day DATE;

last_day DATE;

BEGIN

first_day := SYSDATE;

last_day := ADD_MONTHS (first_day, 6);

END;

Because both the first_day and last_day variables are declared in the same block where they are used, I can conveniently refer to them using only their identifiers, which are visible. A visible identifier might actually reference any of the following:

• A loop index variable (but it's visible and in-scope only inside the loop)

• An identifier declared in the current block

• An identifier declared in a block that encloses the current block

• A standalone database object (table, view, sequence, etc.) or PL/SQL object (procedure, function, type) that you own

• A standalone database object or PL/SQL object on which you have appropriate privilege and that is the target of an Oracle synonym that you can see

PL/SQL also allows the possibility of referring to in-scope items that are not directly visible, as the next section describes.