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.
[ [ DECLARE ] declarations ] BEGIN statements [ EXCEPTION WHEN exception_condition THEN statements [, ...] ] END;
- 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 (;).
BEGIN NULL; END;
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;
75 divided by 14 is 5.36
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;
An exception occurred