EnterpriseDB: Block Structure
Oracle Tips by
SPL is a
block-structured language. You begin a block with the keyword BEGIN
and you end your block with the keyword END. From BEGIN to END is
considered a single executable statement. An executable statement
ends with a semi-colon (;).
BEGIN and END, are executable commands (i.e. your program).
Executable commands also end with a semi-colon. This section, between
the BEGIN and END, is called the "executable section".
The most basic
executable command that exists is the NULL command. This command does
nothing; it's a no-op. Its primary use is in making my life easier
when I show a simple example of an SPL block. Below is such a block:
This is the
simplest SPL program you will ever see. It will run but it does
absolutely nothing. I take that back. It does make a great
A program is
not much use without variables. A variable is where you will store
values from one step in your program to another. Variables are also
what you use to return values to calling programs.
declared in the DECLARE section of a SPL block. The DECLARE comes
before the executable section but is part of the overall executable
example, I did not declare any variables but I did include the
declaration section. This is a valid piece of code. If you launch
the SQL Terminal and run it, you will see that it executes and when it
finishes, SQL Terminal displays the text: SPL Procedure Successfully
complete. It displays that text even if the code executing is a
function or a packaged procedure.
SPL blocks can
be nested. That means that a block can exist within another block.
This is very powerful and is something you will come to expect. After
a while, you won't even think about it.
Below is a
block of code with nested blocks three levels deep.
In this piece
of code, notice that the first nested block does not contain a DECLARE
but the second one does. Also, notice that a block is considered an
executable piece of code. Only the inner most block needs the NULL
command and that is because every block MUST contain at least one
optional, part of the block structure is the exception section. The
exception section is where you will catch errors. I know you will
always write perfect code but sometimes you might want to throw an
error and catch it just to prove you can.
of an EXCEPTION is the EXCEPTION keyword followed by a set of WHEN
clauses. The EXCEPTION section is optional but if you have an
EXCEPTION section, you must have at least one WHEN clause. The WHEN
clause is where you will catch specific errors.
WHEN OTHERS THEN
keyword comes last in a block, just before the END. In addition to
the WHEN clause, the exception section, like the executable section,
requires at least one executable command. In the case above, I used
You can use
exceptions in nested blocks and in fact, you will do that regularly.
I will cover exception handling in detail below but if you want to
execute a block of code but still ensure that your program continues
running, you would put that code in a nested block.
WHEN NO_DATA_FOUND THEN
In the case
above, I want to execute a select statement and if I find no rows, I
want to continue executing my program. Without the nested block, my
program would end when the NO_DATA_FOUND
exception was raised by SPL.
* The required
keywords of a block are BEGIN and END
* The area
between the BEGIN and END is called the executable section
* The optional
DECLARE keyword identifies the declaration section of a block and is
where you would define any variables to be used within that block
* The optional
EXCEPTION keyword identifies the exception section of a block
* If the
EXCEPTION keyword is used, it must contain at least one WHEN clause
and each WHEN clause must contain at least one executable command
* Blocks can
be nested within other blocks
* To continue
processing after an exception, blocks should be nested and nested
blocks should contain their own exception handlers.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.