Nested Blocks

A nested block lives inside another block. Here's an example showing a procedure containing an anonymous, nested block:

PROCEDURE calc_totals IS year_total NUMBER;BEGIN year_total := 0; /* Beginning of nested block */ DECLARE month_total NUMBER; BEGIN month_total := year_total / 12; END set_month_total;/* End of nested block */ END;

The /* and */ delimiters indicate comments (see Section 3.6 later in this chapter). You can nestanonymous blocks within anonymous blocks to more than one level, as shown in Figure 3-4.

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.

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.