An SPL program has the same block structure regardless of whether the program is a stored procedure, function, or trigger. A block consists of up to three sections: an optional declaration section, a mandatory executable section, and an optional exception section.

A simplest block has an executable section that consists of one or more SPL statements within the keywords, BEGIN and END. The optional declaration section is used to declare variables, cursors, and types that are used by the statements within the executable and exception sections.

The declaration section appears before the BEGIN keyword of the executable section. The declaration section can begin with the keyword DECLARE, depending upon the context of where the block is used.

You can include an exception section within the BEGIN - END block. The exception section begins with the keyword, EXCEPTION, and continues until the end of the block in which it appears. If an exception is thrown by a statement within the block, program control goes to the exception section. In the exception section, the thrown exception may or may not be handled, depending on the exception and the contents of the exception section.

The following is the general structure of a block:
[ [ DECLARE ]
      declarations ]
    BEGIN
      statements
  [ EXCEPTION
      WHEN exception_condition THEN
        statements [, ...] ]
    END;
Note
  • declarations are one or more variable, cursor, or type declarations that are local to the block. Each declaration must be terminated by a semicolon (;). The use of the DECLARE keyword depends on the context in which the block appears.
  • statements are one or more SPL statements. Each statement must be terminated by a semicolon (;). The end of the block denoted by the END keyword must also be terminated by a semicolon (;).
The EXCEPTION keyword marks the beginning of the exception section. exception_condition is a conditional expression that is used for the testing of one or more exception types. If an exception matches one of the exceptions in exception_condition, the statements that follow the WHEN exception_condition clause are run. One or more WHEN exception_condition clauses can exist and each clause is followed by statements.
Note Blocks can be nested because a BEGIN/END block in itself is considered a statement. The exception section can also contain nested blocks.
The following example shows the simplest possible block that consists of the NULL statement within the executable section. The NULL statement is an executable statement that does not have effect.
BEGIN
    NULL;
END;
The following block contains a declaration section and an executable section.
DECLARE
    v_numerator     NUMBER(2);
    v_denominator   NUMBER(2);
    v_result        NUMBER(5,2);
BEGIN
    v_numerator := 75;
    v_denominator := 14;
    v_result := v_numerator / v_denominator;
    DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
        ' is ' || v_result);
END;
In the preceding example, three numeric variables are declared of data type NUMBER. Values are assigned to two of the variables, and one number is divided by the other number. The result is stored in a third variable that is used to display the result. The output of this block is as follows:
75 divided by 14 is 5.36
The following block consists of a declaration section, an executable section, and an exception section:
DECLARE
    v_numerator     NUMBER(2);
    v_denominator   NUMBER(2);
    v_result        NUMBER(5,2);
BEGIN
    v_numerator := 75;
    v_denominator := 0;
    v_result := v_numerator / v_denominator;
    DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
        ' is ' || v_result);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('An exception occurred');
END;
The following output shows that the statement within the exception section is executed as a result of the division by zero.
An exception occurred