Review of PL/SQL Block Structure

Introduction to PL/SQL, provided a brief introduction to the PL/SQL block and its structure. Before we explore the construction of modules in PL/SQL, let's take a more detailed look at the PL/SQL block.

PL/SQL is a block-structured language. Each of the basic developing units you write to build your application is (or should be) a logical unit of work. The PL/SQL block allows you to reflect that logical structure in the physical design of your programs.

The block determines both the scope of identifiers (the area of code in which a reference to the identifier can be resolved) and the way in which exceptions are handled and propagated. A block may also contain nested sub-blocks of code, each with its own scope.

There is a common block structure to all the different types of modules. The block is broken up into four different sections, as follows:

• Header
• Relevant for named blocks only, the header determines the way that the named block or program must be called. The header includes the name, parameter list, and RETURN clause (for a function only).
• Declaration section
• The part of the block that declares variables, cursors, and sub-blocks that are referenced in the execution and exception sections. The declaration section is optional, but if you have one, it must come before 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 run-time engine. The execution section contains the IF-THEN-ELSEs, LOOPs, assignments, and calls to other PL/SQL blocks. Every block must have at least one executable statement in the execution section.
• Exception section
• The section that handles exceptions to normal processing (warnings and error conditions). This final section is optional. If it is included, control is transferred to this section when an error is encountered. This section then either handles the error or passes control to the block that called the current block.

Figure 15.1 shows the structure of the PL/SQL block.

Figure 15.1: The PL/SQL block structure for procedures and functions

Sequence of Section Construction

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 how 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).

The declaration and exception sections are optional in any PL/SQL block, but you must always have at least one executable statement in a block.

PL/SQL Block Structure Examples

Figures Figure 15.2 through Figure 15.4 illustrate several different ways that PL/SQL blocks can be constructed.

Figure 15.2: A procedure containing all four sections

Figure 15.3: A function without an exception section

Now that you understand the basic components of a PL/SQL block, let's explore the ways you can use this structure in your developing.