AntDB-Oracle Compatibility Developer’s Manual P4–3

SPL programs

SPL is a procedural-oriented programming language structured in code blocks. There are four different types of programs we can create using SPL, namely procedures, functions, triggers and packages. Procedures and functions are discussed in more detail in later sections of this section.

SPL Block Structure

For an SPL program, whether it is a procedure or a function, it has the same block structure. However, in AntDB, direct execution of the block is not supported, and the block must be placed into a function or procedure for execution. A code block consists of three parts - an optional variable declaration part, a mandatory command execution part, and an optional exception catching part. The simplest block consists of a command execution section containing one or more SPL statements written between the keywords BEGIN and END.

Code blocks can have an optional variable declaration section to declare those variables, cursors, and types used in the execution and exception catching sections. The declaration section appears before the BEGIN keyword in the execution section. The declaration section starts with the keyword DECLARE, depending on the code block being used at the time.

Finally, there can be an optional section inside the BEGIN-END block for catching exceptions. The exception catching section starts with the keyword EXCEPTION and ends at the end of the block. If an exception is thrown by a statement in the block, the program control flow moves to the exception catching section, where the handling of the exception depends on the exception and the content of the exception handling flow.

The following is a generic structure for a code block.

[ [ DECLARE ]

declarations ]

BEGIN

statements

[ EXCEPTION

WHEN exception_condition THEN

statements [, ...] ]

END;

declarations are one or more declarations of variables, cursors, or types in a block of code. Each declaration must be terminated by a semicolon. The use of the keyword DECLARE depends on the context in which the block occurs.

statements is one or more SPL statements. Each statement must be terminated by a semicolon. The position of the end of the block identified by the keyword END must also be terminated by a semicolon.

The keyword EXCEPTION appearing in a block of code indicates the beginning of the catch exception section. exception_condition is a conditional expression used to test for one or more types of exceptions. If a generated exception matches one of the exception types in exception_condition, then the statements that follow the WHEN exception_condition clause are executed. There can be one or more WHEN exception_condition clauses, each of which is followed by the corresponding statement.

Note: A BEGIN/END block is a statement in itself, so blocks can be nested. The catch exception section can contain nested blocks.

The following is an example of the simplest block of code. The execution section in this block contains a NULL statement, which is an executable statement that does not do any work.

BEGIN
NULL;
END;

The following code block contains two parts, variable declaration and command execution.

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 above example, first three numeric variables are declared as NUMBER data type, two variables are assigned values in the execution section, and then one of them is made to be divisible by the other one. The result is stored in the third variable, and finally the third variable is used to display the result.

The following example shows a block of code that contains three parts: variable declaration, command execution, and exception catch.

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 the result of executing the relevant statement in the catch exception section when a divide by zero operation is performed in the block.

An exception occurred