3.1.1 Sections of the pl/sql Block

Each PL/SQL block has up to four different sections, only one of which is mandatory:

Header

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

Declaration section

Identifies variables, cursors, and sub-blocks that are referenced in the execution and exception sections. Optional.

Execution section

Statements the PL/SQL runtime engine will execute at runtime. Mandatory.

Exception section

Handles exceptions to normal processing (warnings and error conditions). Optional.

Figure 3-1 shows the structure of the PL/SQL block for a procedure.

Figure 3-1. The pl/sql block structure

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

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

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

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

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

Figure 3-2 shows a procedure containing all four sections. In practice, there is often quite a bit of iteration among these steps when creating your own blocks; don't expect perfection on your first pass!

Figure 3-2. A procedure containing all four sections

3.1.2 Anonymous Blocks

When someone wishes to remain anonymous, that person goes unnamed. Same with the anonymous block in PL/SQL, which is shown in Figure 3-3: it lacks a header section altogether, beginning instead with either DECLARE or BEGIN.

Figure 3-3. An anonymous block without declaration and exception sections

An anonymous block cannot be called by any other block—it doesn't have a handle for reference. Instead, anonymous blocks serve as scripts that execute PL/SQL statements, usually including calls to procedures and functions. Because an anonymous block can have its own declaration and exception sections, developers often use anonymous blocks to provide a scope for identifiers and exception handling within a larger program. I'll explore these characteristics in the following sections.

3.1.2.1 The structure of an anonymous block

The general format of an anonymous PL/SQL block is as follows:

[ DECLARE

... optional declaration statements ... ]

BEGIN

... executable statements ...

[ EXCEPTION

... optional exception handler statements ... ]

END;

The square brackets indicate an optional part of the syntax. You must have BEGIN and END statements, and you must have at least one executable statement.

3.1.2.2 Examples of anonymous blocks

The following examples show the different combinations of block sections, all of which comprise valid PL/SQL blocks:

• An anonymous block with BEGIN-END terminators, but no declaration or exception sections:

• BEGIN

• -- Show current date in default format

• DBMS_OUTPUT.PUT_LINE (SYSDATE);

END;

• An anonymous block with a declaration section, but no exception section:

• DECLARE

• l_right_now DATE := SYSDATE;

• BEGIN

• DBMS_OUTPUT.PUT_LINE (l_right_now);

END;

• An anonymous block containing declaration,execution, and exception sections:

• DECLARE

• -- Call a pre-defined function to obtain the hiredate

• -- of the employee named "FEUERSTEIN".

• l_hiredate DATE := employee_pkg.date_of_hire ('FEUERSTEIN');

• l_right_now DATE := SYSDATE;

• l_old_timer EXCEPTION;

• BEGIN

• IF l_hiredate < ADD_MONTHS (l_right_now, 6)

• THEN

• RAISE l_old_timer;

• ELSE

• l_hiredate := right_now;

• END IF;

• EXCEPTION

• WHEN l_old_timer

• THEN

• DBMS_OUTPUT.PUT_LINE

• ('You are not eligible for orientation.');

• WHEN OTHERS

• THEN

• DBMS_OUTPUT.PUT_LINE

• ('Error encountered: ' || SQLCODE);

END;

Anonymous blocks execute a series of statements and then terminate, thus acting like procedures. In fact, all anonymous blocks are "anonymous procedures."