2.7.1 Sections of the PL/SQL Block

Each PL/SQL block has up to four different sections (some are optional under certain circumstances):

Header

Relevant for named blocks only. The header determines the way the named block or program must be called.

Declaration section

The part of the block that declares variables, cursors, and sub-blocks that are referenced in the execution and exception sections.

Execution section

The part of the PL/SQL block containing the executable statements, the code that is executed by the PL/SQL runtime engine.

Exception section

The section that handles exceptions to normal processing (warnings and error conditions).

Figure 2.1 shows the structure of the PL/SQL block for a procedure.

Figure 2.1: The PL/SQL block structure

The ordering of the sections in a block corresponds to the way you would write your programs and the way they are executed:

Step 1

Define the type of block (procedure, function, anonymous) and the way it is called (header).

Step 2

Declare any variables used in that block (declaration section).

Step 3

Use those local variables and other PL/SQL objects to perform the required actions (execution section).

Step 4

Handle any problems that arise during the execution of the block (exception section).

2.7.2 Scope of a Block

In the declaration section of the block, you may define variables, modules, and other structures. These declarations are local to that block. When the execution of the block finishes, those structures no longer exist. For example, if you open a cursor in a block, that cursor is automatically closed at the end of the block.

The block is the scope of any objects declared in that block. The block also provides the scope for exceptions that are declared and raised. In fact, one of the most important reasons to create a block is to take advantage of the exception section that comes with that block. It gives you a finer granularity of control over how you can handle errors in your programs.

2.7.3 Nested Blocks

A block may also contain nested sub-blocks of code. The following example shows a procedure with an anonymous, nested block defined within it:

PROCEDURE calc_totals
IS
   year_total NUMBER;
BEGIN
   year_total := 0;

   /* Nested anonymous block */
   DECLARE
      month_total NUMBER;
   BEGIN
      month_total := year_total / 12;
   END;

END;

Notice that I can reference the year_total variable inside the nested block. Any element declared in an outer block is global to all blocks nested within it. Any element declared within an inner block cannot, however, be referenced in an outer block.

Although I refer to the PL/SQL block structure throughout this book, it will figure most prominently in Part 4, Modular Code .