Regardless of whether the program is a procedure, function, subprogram, or trigger, an SPL program has the same block structure. A block consists of up to three sections: an optional declaration section, a mandatory executable section, and an optional exception section. A block must have at least 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, types, and subprograms that are used by the statements within the executable and exception sections. Declarations appears only prior to the BEGIN keyword of the executable section. Depending on the context of where the block is used, the declaration section may begin with the keyword DECLARE.
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 where the thrown exception may or may not be handled depending on the exception and the contents of the exception section.
The following content shows the general structure of a block:
[ [ DECLARE ] pragmas declarations ] BEGIN statements [ EXCEPTION WHEN exception_condition THEN statements [, ...] ] END;
pragmas are the directives (AUTONOMOUS_TRANSACTION is the currently supported pragma). declarations are one or more variable, cursor, type, or subprogram declarations that are local to the block. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations. Each declaration must be terminated by a semicolon (;). The use of the keyword DECLARE 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 indicated by the keyword END must also be terminated by a semicolon (;).
If present, the keyword EXCEPTION marks the beginning of the exception section. exception_condition is a conditional expression testing for one or more types of exceptions. If an exception matches one of the exceptions in exception_condition, the statements following the WHEN exception_condition clause are executed. There may be one or more WHEN exception_condition clauses that are followed by statements. Note: A BEGIN/END block can be considered as a statement. Therefore, blocks can be nested. The exception section may also contain nested blocks.
The following content describes the simplest possible block consisting of the NULL statement within the executable section. The NULL statement is an executable statement that does not perform any operations.
BEGIN NULL; END;
The following content describes a block that contains a declaration section as well as the 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 this example, three numeric variables are declared for the data type NUMBER. Values are assigned to two of the variables, and one number is divided by the other, storing the results in the third variable that is then displayed. If executed, the following output is generated:
75 divided by 14 is 5.36
The following content describes a block that contains a declaration, an executable, and an exception:
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 the result of the division by zero:
An exception occurred