CHAPTERl28
Introductionlto
FirebirdlProgramming
NEOFTHEGREATBENEFITSOFAFULL-BLOODED
O SQL relational database implementation
is its capability to compile and execute internal code modules (stored procedures and
triggers) supplied as source code by the developer. The language that provides this
capability on a Firebird server is PSQL, a simple but powerful set of SQL extensions
that combines with regular data manipulation language (DML) statements to become
compilable source modules.
OverviewloflServerlCodelModules
The high-level language for Firebird server-side programming is SQL. Source code is
presented to the engine in the form of SQL programming language extensions°(TM)PSQ
statements and constructs°(TM)and DML statements. These statements are, themselves
wrapped inside single DDL statements of the form
{CREATEk|kRECREATEk|kALTER}k{TRIGGERk|kPROCEDURE}k<name>k...
...
AS
...
BEGIN
<onekorkmorekblockskofkstatements>k
ENDk
The syntax for writing PSQL modules is explored in depth in the next chapters.
The objective of each of these °∞DDL super-statements°± is to create and store o
executable code module (stored procedure or trigger) or to redefine (RECREATE or
ALTER) an existing object. A DDL statement is also used to destroy (DROP) executable
code objects.
PSQL supports the three data manipulation statements, INSERT, UPDATE, and
DELETE, and the ability to SELECT single-row or multiple-row sets of data items into
local variables. The PSQL extensions provide the following language and logic support:
?Local variables and assignment statements.
?Conditional control-flow statements.
?Special context variables (for triggers only) for accessing the old and new values
of each column of all DML input sets.
?Posting of user-defined database events to a listening client.
?Exceptions, including user-defined ones declared as database objects, and
(inv.1.5) context-specific exception messages, and supporting structure and
syntax for error handling.
?Input and output arguments (for stored procedures only).
?Encapsulation of cursor behavior in FOR SELECT ...INTO ...DOlooping
syntax.
?The SUSPEND statement (for stored procedures only), providing the capability
to write stored procedures that output a virtual table in direct response to a
SELECT statement°selectable stored procedures.
?Embedding of stored procedure calls in both stored procedures and triggers.
?Capability to define multiple triggers for the BEFORE and AFTER phases of
triggers for each DML event and to position them in a predefined execution
order. v.1.5 introduced the ability to write conditional BEFORE and AFTER
triggers encompassing any or all possible DML events.
Except for the specific elements mentioned, the entire PSQL language set is
available to both stored procedures and triggers.