PL/SQL is a block-structured language. Each of the basic programming 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 structure is at the core of two key concepts and features of the PL/SQL language:
The PL/SQL block is the basic unit of work from which modules, such as procedures and functions, are built. The ability to modularize is central to successfully developing complex applications.
The block provides a scope or context for logically related objects. In the block, you group together declarations of variables and executable statements that belong together.
You can create anonymous blocks (blocks of code that have no name) and named blocks, which are procedures and functions. Furthermore, you can build packages in PL/SQL that group together multiple procedures and functions.
Each PL/SQL block has up to four different sections (some are optional under certain circumstances):
Relevant for named blocks only. The header determines the way the named block or program must be called.
The part of the block that declares variables, cursors, and sub-blocks that are referenced in the execution and exception sections.
The part of the PL/SQL block containing the executable statements, the code that is executed by the PL/SQL runtime engine.
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.
The ordering of the sections in a block corresponds to the way you would write your programs and the way they are executed:
Define the type of block (procedure, function, anonymous) and the way it is called (header).
Declare any variables used in that block (declaration section).
Use those local variables and other PL/SQL objects to perform the required actions (execution section).
Handle any problems that arise during the execution of the block (exception section).
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.
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 .
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.