Procedural SQL (PSQL) is a procedural extension of SQL. This language subset is used for writing stored procedures, triggers, and PSQL blocks.
PSQL provides all the basic constructs of traditional structured programming languages, and also includes DML statements (SELECT
, INSERT
, UPDATE
, DELETE
, etc.), with a slight modified syntax in some cases.
A procedural extension may contain declarations of local variables, routines and cursors, assignments, conditional statements, loops, statements for raising custom exceptions, error handling and sending messages (events) to client applications.
Triggers have access to special context variables, two arrays that store, respectively, the NEW
values for all columns during insert and update activity, and the OLD
values during update and delete work.
Statements that modify metadata (DDL) are not available in PSQL.
If DML statements (SELECT
, INSERT
, UPDATE
, DELETE
, etc.) in the body of the module (procedure, function, trigger or block) use parameters, only named parameters can be used.
If DML statements contain named parameters, then they must be previously declared as local variables using DECLARE [VARIABLE]
in the declaration section of the module, or as input or output variables in the module header.
When a DML statement with parameters is included in PSQL code, the parameter name must be prefixed by a colon (‘:
’) in most situations.
The colon is optional in statement syntax that is specific to PSQL, such as assignments and conditionals and the INTO
clause.
The colon prefix on parameters is not required when calling stored procedures from within another PSQL module or in DSQL.
Stored procedures and functions (including those defined in packages) are executed in the context of the transaction in which they are called. Triggers are executed as an intrinsic part of the operation of the DML statement: thus, their execution is within the same transaction context as the statement itself. Individual transactions are launched for database event triggers.
Statements that start and end transactions are not available in PSQL, but it is possible to run a statement or a block of statements in an autonomous transaction.
PSQL code modules consist of a header and a body.
The DDL statements for defining them are complex statements;
that is, they consist of a single statement that encloses blocks of multiple statements.
These statements begin with a verb (CREATE
, ALTER
, DROP
, RECREATE
, CREATE OR ALTER
) and end with the last END
statement of the body.
The header provides the module name and defines any input and output parameters or — for functions — the return type. Stored procedures and PSQL blocks may have input and output parameters. Functions may have input parameters and must have a scalar return type. Triggers do not have either input or output parameters.
The header of a trigger indicates the database event (insert, update or delete, or a combination) and the phase of operation (BEFORE
or AFTER
that event) that will cause it to “fire”.
The module body is either a PSQL module body, or an external module body.
<module-body> ::= <psql-module-body> | <external-module-body> <psql-module-body> ::= AS [<declarations>] BEGIN [<PSQL_statements>] END <external-module-body> ::= EXTERNAL [NAME <extname>] ENGINE engine [AS '<extbody>'] <declarations> ::= <declare-item> [<declare-item ...] <declare-item> ::= <declare-var> | <declare-cursor> | <declare-subfunc> | <declare-subproc> <extname> ::= '<module-name>!<routine-name>[!<misc-info>]'
Parameter | Description |
---|---|
declarations |
Section for declaring local variables, named cursors, and subroutines |
PSQL_statements |
Procedural SQL statements.
Some PSQL statements may not be valid in all types of PSQL.
For example, |
declare_var |
Local variable declaration |
declare_cursor |
Named cursor declaration |
declare-subfunc |
Sub-function declaration |
declare-subproc |
Sub-procedure declaration |
extname |
String identifying the external procedure |
engine |
String identifying the UDR engine |
extbody |
External procedure body. A string literal that can be used by UDRs for various purposes. |
module-name |
The name of the module that contains the procedure |
routine-name |
The internal name of the procedure inside the external module |
misc-info |
Optional string that is passed to the procedure in the external module |
The PSQL body starts with an optional section that declares variables and subroutines, followed by a block of statements that run in a logical sequence, like a program.
A block of statements — or compound statement — is enclosed by the BEGIN
and END
keywords, and is executed as a single unit of code.
The main BEGIN…END
block may contain any number of other BEGIN…END
blocks, both embedded and sequential.
Blocks can be nested to a maximum depth of 512 blocks.
All statements except BEGIN
and END
are terminated by semicolons (‘;
’).
No other character is valid for use as a terminator for PSQL statements.
Note
|
In the Firebird 2.5 Language Reference, the declaration of local variables and cursors was considered part of the module header. With the introduction of UDR (external routines) in Firebird 3.0, we now consider this declaration section part of the — PSQL — module body. |
The external module body specifies the UDR engine used to execute the external module, and optionally specifies the name of the UDR routine to call (<extname>) and/or a string (<extbody>) with UDR-specific semantics.
Configuration of external modules and UDR engines is not covered further in this Language Reference. Consult the documentation of a specific UDR engine for details.
A stored procedure is executable code stored in the database metadata for execution on the server. A stored procedure can be called by other stored procedures (including itself), functions, triggers and client applications. A procedure that calls itself is known as recursive.
Stored procedures have the following advantages:
Modularity |
applications working with the database can use the same stored procedure, thereby reducing the size of the application code and avoiding code duplication. |
Simpler Application Support |
when a stored procedure is modified, changes appear immediately to all host applications, without the need to recompile them if the parameters were unchanged. |
Enhanced Performance |
since stored procedures are executed on a server instead of at the client, network traffic is reduced, which improves performance. |
Firebird supports two types of stored procedures: executable and selectable.
Executable procedures usually modify data in a database.
They can receive input parameters and return a single set of output (RETURNS
) parameters.
They are called using the EXECUTE PROCEDURE
statement.
See an example of an executable stored procedure at the end of the CREATE PROCEDURE
section of Chapter 5.
Selectable stored procedures usually retrieve data from a database, returning an arbitrary number of rows to the caller. The caller receives the output one row at a time from a row buffer that the database engine prepares for it.
Selectable procedures can be useful for obtaining complex sets of data that are often impossible or too difficult or too slow to retrieve using regular DSQL SELECT
queries.
Typically, this style of procedure iterates through a looping process of extracting data, perhaps transforming it before filling the output variables (parameters) with fresh data at each iteration of the loop.
A SUSPEND
statement at the end of the iteration fills the buffer and waits for the caller to fetch the row.
Execution of the next iteration of the loop begins when the buffer has been cleared.
Selectable procedures may have input parameters, and the output set is specified by the RETURNS
clause in the header.
A selectable stored procedure is called with a SELECT
statement.
See an example of a selectable stored procedure at the end of the CREATE PROCEDURE
section of Chapter 5.
The syntax for creating executable stored procedures and selectable stored procedures is exactly the same. The difference comes in the logic of the program code.
For information about creating stored procedures, see CREATE PROCEDURE
in Chapter Data Definition (DDL) Statements.
For information about modifying existing stored procedures, see ALTER PROCEDURE
, CREATE OR ALTER PROCEDURE
, RECREATE PROCEDURE
, in Chapter Data Definition (DDL) Statements.
For information about deleting stored procedures, see DROP PROCEDURE
in Chapter Data Definition (DDL) Statements.
A stored function is executable code stored in the database metadata for execution on the server. A stored function can be called by other stored functions (including itself), procedures, triggers and client applications. A function that calls itself is known as recursive.
Unlike stored procedures, stored functions always return one scalar value.
To return a value from a stored function, use the RETURN
statement, which immediately terminates the function.
For information about creating stored functions, see CREATE FUNCTION
in Chapter Data Definition (DDL) Statements.
For information about modifying stored functions, see ALTER FUNCTION
, CREATE OR ALTER FUNCTION
, RECREATE FUNCTION
, in Chapter Data Definition (DDL) Statements.
For information about deleting stored procedures, see DROP FUNCTION
in Chapter Data Definition (DDL) Statements.
A self-contained, unnamed (“anonymous”) block of PSQL code can be executed dynamically in DSQL, using the EXECUTE BLOCK
syntax.
The header of an anonymous PSQL block may optionally contain input and output parameters.
The body may contain local variables, cursor declarations and local routines, followed by a block of PSQL statements.
An anonymous PSQL block is not defined and stored as an object, unlike stored procedures and triggers. It executes in run-time and cannot reference itself.
Just like stored procedures, anonymous PSQL blocks can be used to process data and to retrieve data from the database.
EXECUTE BLOCK [(<inparam> = ? [, <inparam> = ? ...])] [RETURNS (<outparam> [, <outparam> ...])] <psql-module-body> <psql-module-body> ::= !! See Syntax of Module Body !!
Argument | Description |
---|---|
inparam |
Input parameter description |
outparam |
Output parameter description |
declarations |
A section for declaring local variables and named cursors |
PSQL statements |
PSQL and DML statements |
See EXECUTE BLOCK
for details.
A package is a group of stored procedures and function defined as a single database object.
Firebird packages are made up of two parts: a header (PACKAGE
keyword) and a body (PACKAGE BODY
keywords).
This separation is very similar to Delphi modules, the header corresponds to the interface part, and the body corresponds to the implementation part.
The notion of “packaging” the code components of a database operation addresses has several advantagrs:
- Modularisation
-
Blocks of interdependent code are grouped into logical modules, as done in other programming languages.
In programming, it is well recognised that grouping code in various ways, in namespaces, units or classes, for example, is a good thing. This is not possible with standard stored procedures and functions in the database. Although they can be grouped in different script files, two problems remain:
-
The grouping is not represented in the database metadata.
-
Scripted routines all participate in a flat namespace and are callable by everyone (we are not referring to security permissions here).
-
- Easier tracking of dependencies
-
Packages make it easy to track dependencies between a collection of related routines, as well as between this collection and other routines, both packaged and unpackaged.
Whenever a packaged routine determines that it uses a certain database object, a dependency on that object is registered in Firebird’s system tables. Thereafter, to drop, or maybe alter that object, you first need to remove what depends on it. Since the dependency on other objects only exists for the package body, and not the package body, this package body can easily be removed, even if some other object depends on this package. When the body is dropped, the header remains, allowing you to recreate its body once the changes related to the removed object are done.
- Simplify permission management
-
As Firebird runs routines with the caller privileges, it is necessary also to grant resource usage to each routine when these resources would not be directly accessible to the caller. Usage of each routine needs to be granted to users and/or roles.
Packaged routines do not have individual privileges. The privileges apply to the package as a whole. Privileges granted to packages are valid for all package body routines, including private ones, but are stored for the package header. An
EXECUTE
privilege on a package granted to a user (or other object), grants that user the privilege to execute all routines defined in the package header.For exampleGRANT SELECT ON TABLE secret TO PACKAGE pk_secret; GRANT EXECUTE ON PACKAGE pk_secret TO ROLE role_secret;
- Private scopes
-
Stored procedures and functions can be privates; that is, make them available only for internal usage within the defining package.
All programming languages have the notion of routine scope, which is not possible without some form of grouping. Firebird packages also work like Delphi units in this regard. If a routine is not declared in the package header (interface) and is implemented in the body (implementation), it becomes a private routine. A private routine can only be called from inside its package.
For information on creating packages, see CREATE PACKAGE
, CREATE PACKAGE BODY
For information on modifying existing package header or bodies, see ALTER PACKAGE
, CREATE OR ALTER PACKAGE
, RECREATE PACKAGE
, ALTER PACKAGE BODY
, RECREATE PACKAGE BODY
For information on deleting a package, see DROP PACKAGE
, DROP PACKAGE BODY
A trigger is another form of executable code that is stored in the metadata of the database for execution by the server. A trigger cannot be called directly. It is called automatically (“fired”) when data-changing events involving one particular table or view occur, or on a specific database event.
A trigger applies to exactly one table or view or database event, and only one phase in an event (BEFORE
or AFTER
the event).
A single DML trigger might be written to fire only when one specific data-changing event occurs (INSERT
, UPDATE
or DELETE
), or it might be written to apply to more than one of those.
A DML trigger is executed in the context of the transaction in which the data-changing DML statement is running. For triggers that respond to database events, the rule is different: for DDL triggers and transaction triggers, the trigger runs in the same transaction that executed the DDL, for other types, a new default transaction is started.
More than one trigger can be defined for each phase-event combination.
The order in which they are executed (known as “firing order” can be specified explicitly with the optional POSITION
argument in the trigger definition.
You have 32,767 numbers to choose from.
Triggers with the lowest position numbers fire first.
If a POSITION
clause is omitted, or if several matching event-phase triggers have the same position number, then the triggers will fire in alphabetical order.
DML triggers are those that fire when a DML operation changes the state of data: updating rows in tables, inserting new rows or deleting rows. They can be defined for both tables and views.
Six base options are available for the event-phase combination for tables and views:
Before a new row is inserted |
|
After a new row is inserted |
|
Before a row is updated |
|
After a row is updated |
|
Before a row is deleted |
|
After a row is deleted |
|
These base forms are for creating single phase/single-event triggers.
Firebird also supports forms for creating triggers for one phase and multiple-events, BEFORE INSERT OR UPDATE OR DELETE
, for example, or AFTER UPDATE OR DELETE
: the combinations are your choice.
Note
|
“Multi-phase” triggers, such as |
For DML triggers, the Firebird engine provides access to sets of OLD
and NEW
context variables.
Each is an array of the values of the entire row: one for the values as they are before the data-changing event (the BEFORE
phase) and one for the values as they will be after the event (the AFTER
phase).
They are referenced in statements using the form NEW.column_name
and OLD.column_name
, respectively.
The column_name can be any column in the table’s definition, not just those that are being updated.
The NEW
and OLD
variables are subject to some rules:
-
In all triggers, the
OLD
value is read-only -
In
BEFORE UPDATE
andBEFORE INSERT
code, theNEW
value is read/write, unless it is aCOMPUTED BY
column -
In
INSERT
triggers, references to theOLD
variables are invalid and will throw an exception -
In
DELETE
triggers, references to theNEW
variables are invalid and will throw an exception -
In all
AFTER
trigger code, theNEW
variables are read-only
A trigger associated with a database or transaction event can be defined for the following events:
Connecting to a database |
|
Before the trigger is executed, a default transaction is automatically started |
Disconnecting from a database |
|
Before the trigger is executed, a default transaction is automatically started |
When a transaction is started |
|
The trigger is executed in the current transaction context |
When a transaction is committed |
|
The trigger is executed in the current transaction context |
When a transaction is cancelled |
|
The trigger is executed in the current transaction context |
DDL triggers fire on specified metadata changes events in a specified phase.
BEFORE
triggers run before changes to system tables.
AFTER
triggers run after changes in system tables.
DDL triggers are a specific type of database trigger, so most rules for and semantics of database triggers also apply for DDL triggers.
-
BEFORE
triggers are fired before changes to the system tables.AFTER
triggers are fired after system table changes.ImportantImportant RuleThe event type
[BEFORE | AFTER]
of a DDL trigger cannot be changed. -
When a DDL statement fires a trigger that raises an exception (
BEFORE
orAFTER
, intentionally or unintentionally) the statement will not be committed. That is, exceptions can be used to ensure that a DDL operation will fail if the conditions are not precisely as intended. -
DDL trigger actions are executed only when committing the transaction in which the affected DDL command runs. Never overlook the fact that what is possible to do in an
AFTER
trigger is exactly what is possible to do after a DDL command without autocommit. You cannot, for example, create a table and then use it in the trigger. -
With “
CREATE OR ALTER
” statements, a trigger is fired one time at theCREATE
event or theALTER
event, according to the previous existence of the object. WithRECREATE
statements, a trigger is fired for theDROP
event if the object exists, and for theCREATE
event. -
ALTER
andDROP
events are generally not fired when the object name does not exist. For the exception, see point 6. -
The exception to rule 5 is that
BEFORE ALTER/DROP USER
triggers fire even when the username does not exist. This is because, underneath, these commands perform DML on the security database, and the verification is not done before the command on it is run. This is likely to be different with embedded users, so do not write code that depends on this. -
If some exception is raised after the DDL command starts its execution and before
AFTER
triggers are fired,AFTER
triggers will not be fired. -
Packaged procedures and triggers do not fire individual
{CREATE | ALTER | DROP} {PROCEDURE | FUNCTION}
triggers.
When a DDL trigger is running, the DDL_TRIGGER
namespace is available for use with RDB$GET_CONTEXT
.
This namespace contains information on the currently firing trigger.
See also The DDL_TRIGGER
Namespace in RDB$GET_CONTEXT
in Chapter Built-in Scalar Functions.
For information on creating triggers, see CREATE TRIGGER
, CREATE OR ALTER TRIGGER
, RECREATE TRIGGER
in Chapter Data Definition (DDL) Statements.
For information on modifying triggers, see ALTER TRIGGER
, CREATE OR ALTER TRIGGER
, RECREATE TRIGGER
in Chapter Data Definition (DDL) Statements.
For information on deleting triggers, see DROP TRIGGER
in Chapter Data Definition (DDL) Statements.
This section takes a closer look at the procedural SQL language constructs and statements that are available for coding the body of a stored procedure, trigger or anonymous PSQL block.
:
’)The colon marker prefix (‘:
’) is used in PSQL to mark a reference to a variable in a DML statement.
The colon marker is not required before variable names in other PSQL code.
Since Firebird 3.0, the colon prefix can also be used for the NEW
and OLD
contexts, and for cursor variables.
Assigning a value to a variable
PSQL
varname = <value_expr>;
Argument | Description |
---|---|
varname |
Name of a parameter or local variable |
value_expr |
An expression, constant or variable whose value resolves to the same data type as varname |
PSQL uses the equal symbol (‘=
’) as its assignment operator.
The assignment statement assigns an SQL expression value on the right to the variable on the left of the operator.
The expression can be any valid SQL expression: it may contain literals, internal variable names, arithmetic, logical and string operations, calls to internal functions, stored functions or external functions (UDFs).
CREATE PROCEDURE MYPROC (
a INTEGER,
b INTEGER,
name VARCHAR (30)
)
RETURNS (
c INTEGER,
str VARCHAR(100))
AS
BEGIN
-- assigning a constant
c = 0;
str = '';
SUSPEND;
-- assigning expression values
c = a + b;
str = name || CAST(b AS VARCHAR(10));
SUSPEND;
-- assigning expression value
-- built by a query
c = (SELECT 1 FROM rdb$database);
-- assigning a value from a context variable
str = CURRENT_USER;
SUSPEND;
END
Declaring a local variable
PSQL
DECLARE [VARIABLE] varname <domain_or_non_array_type> [NOT NULL] [COLLATE collation] [{DEFAULT | = } <initvalue>]; <domain_or_non_array_type> ::= !! See Scalar Data Types Syntax !! <initvalue> ::= <literal> | <context_var>
DECLARE VARIABLE
Statement Parameters
Argument | Description |
---|---|
varname |
Name of the local variable |
collation |
Collation sequence |
initvalue |
Initial value for this variable |
literal |
Literal of a type compatible with the type of the local variable |
context_var |
Any context variable whose type is compatible with the type of the local variable |
The statement DECLARE [VARIABLE]
is used for declaring a local variable.
The keyword VARIABLE
can be omitted.
One DECLARE [VARIABLE]
statement is required for each local variable.
Any number of DECLARE [VARIABLE]
statements can be included and in any order.
The name of a local variable must be unique among the names of local variables and input and output parameters declared for the module.
Note
|
A special case of |
A local variable can be of any SQL type other than an array.
-
A domain name can be specified as the type; the variable will inherit all of its attributes.
-
If the
TYPE OF domain
clause is used instead, the variable will inherit only the domain’s data type, and, if applicable, its character set and collation attributes. Any default value or constraints such asNOT NULL
orCHECK
constraints are not inherited. -
If the
TYPE OF COLUMN relation.column>
option is used to “borrow” from a column in a table or view, the variable will inherit only the column’s data type, and, if applicable, its character set and collation attributes. Any other attributes are ignored.
For local variables, you can specify the NOT NULL
constraint, disallowing NULL
values for the variable.
If a domain has been specified as the data type and the domain already has the NOT NULL
constraint, the declaration is unnecessary.
For other forms, including use of a domain that is nullable, the NOT NULL
constraint can be included if needed.
Unless specified, the character set and collation sequence of a string variable will be the database defaults.
A CHARACTER SET
clause can be included, if required, to handle string data that is going to be in a different character set.
A valid collation sequence (COLLATE
clause) can also be included, with or without the character set clause.
Local variables are NULL
when execution of the module begins.
They can be initialized so that a starting or default value is available when they are first referenced.
The DEFAULT <initvalue>
form can be used, or just the assignment operator, ‘=
’: = <initvalue>
.
The value can be any type-compatible literal or context variable, including NULL
.
Tip
|
Be sure to use this clause for any variables that have a |
CREATE OR ALTER PROCEDURE SOME_PROC
AS
-- Declaring a variable of the INT type
DECLARE I INT;
-- Declaring a variable of the INT type that does not allow NULL
DECLARE VARIABLE J INT NOT NULL;
-- Declaring a variable of the INT type with the default value of 0
DECLARE VARIABLE K INT DEFAULT 0;
-- Declaring a variable of the INT type with the default value of 1
DECLARE VARIABLE L INT = 1;
-- Declaring a variable based on the COUNTRYNAME domain
DECLARE FARM_COUNTRY COUNTRYNAME;
-- Declaring a variable of the type equal to the COUNTRYNAME domain
DECLARE FROM_COUNTRY TYPE OF COUNTRYNAME;
-- Declaring a variable with the type of the CAPITAL column in the COUNTRY table
DECLARE CAPITAL TYPE OF COLUMN COUNTRY.CAPITAL;
BEGIN
/* PSQL statements */
END
Declaring a named cursor
PSQL
DECLARE [VARIABLE] cursor_name [[NO] SCROLL] CURSOR FOR (<select>);
DECLARE … CURSOR
Statement Parameters
Argument | Description |
---|---|
cursor_name |
Cursor name |
select |
|
The DECLARE … CURSOR … FOR
statement binds a named cursor to the result set obtained in the SELECT
statement specified in the FOR
clause.
In the body code, the cursor can be opened, used to iterate row-by-row through the result set, and closed.
While the cursor is open, the code can perform positioned updates and deletes using the WHERE CURRENT OF
in the UPDATE
or DELETE
statement.
Note
|
Syntactically, the |
The cursor can be forward-only (unidirectional) or scrollable.
The optional clause SCROLL
makes the cursor scrollable, the NO SCROLL
clause, forward-only.
By default, cursors are forward-only.
Forward-only cursors can — as the name implies — only move forward in the dataset.
Forward-only cursors only support the FETCH [NEXT FROM]
statement, other commands raise an error.
Scrollable cursors allow you to move not only forward in the dataset, but also back, asl well as N positions relative to the current position.
Warning
|
Scrollable cursors are materialized as a temporary dataset, as such, they consume additional memory or disk space, so use them only when you really need them. |
-
The optional
FOR UPDATE
clause can be included in theSELECT
statement, but its absence does not prevent successful execution of a positioned update or delete -
Care should be taken to ensure that the names of declared cursors do not conflict with any names used subsequently in statements for
AS CURSOR
clauses -
If the cursor is needed only to walk the result set, it is nearly always easier and less error-prone to use a
FOR SELECT
statement with theAS CURSOR
clause. Declared cursors must be explicitly opened, used to fetch data, and closed. The context variableROW_COUNT
has to be checked after each fetch and, if its value is zero, the loop has to be terminated. AFOR SELECT
statement does this automatically.Nevertheless, declared cursors provide a high level of control over sequential events and allow several cursors to be managed in parallel.
-
The
SELECT
statement may contain parameters. For instance:SELECT NAME || :SFX FROM NAMES WHERE NUMBER = :NUM
Each parameter has to have been declared beforehand as a PSQL variable, even if they originate as input and output parameters. When the cursor is opened, the parameter is assigned the current value of the variable.
Warning
|
Unstable Variables and Cursors
If the value of the PSQL variable used in the Note particularly that the behaviour may depend on the query plan, specifically on the indexes being used. Currently, there are no strict rules for this behaviour, and this may change in future versions of Firebird. |
-
Declaring a named cursor in the trigger.
CREATE OR ALTER TRIGGER TBU_STOCK BEFORE UPDATE ON STOCK AS DECLARE C_COUNTRY CURSOR FOR ( SELECT COUNTRY, CAPITAL FROM COUNTRY ); BEGIN /* PSQL statements */ END
-
Declaring a scrollable cursor
EXECUTE BLOCK RETURNS ( N INT, RNAME CHAR(31)) AS - Declaring a scrollable cursor DECLARE C SCROLL CURSOR FOR ( SELECT ROW_NUMBER() OVER (ORDER BY RDB$RELATION_NAME) AS N, RDB$RELATION_NAME FROM RDB$RELATIONS ORDER BY RDB$RELATION_NAME); BEGIN / * PSQL statements * / END
-
A collection of scripts for creating views with a PSQL block using named cursors.
EXECUTE BLOCK RETURNS ( SCRIPT BLOB SUB_TYPE TEXT) AS DECLARE VARIABLE FIELDS VARCHAR(8191); DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME; DECLARE VARIABLE RELATION RDB$RELATION_NAME; DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE; DECLARE VARIABLE CUR_R CURSOR FOR ( SELECT RDB$RELATION_NAME, RDB$VIEW_SOURCE FROM RDB$RELATIONS WHERE RDB$VIEW_SOURCE IS NOT NULL); -- Declaring a named cursor where -- a local variable is used DECLARE CUR_F CURSOR FOR ( SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE -- It is important that the variable must be declared earlier RDB$RELATION_NAME = :RELATION); BEGIN OPEN CUR_R; WHILE (1 = 1) DO BEGIN FETCH CUR_R INTO :RELATION, :SOURCE; IF (ROW_COUNT = 0) THEN LEAVE; FIELDS = NULL; -- The CUR_F cursor will use the value -- of the RELATION variable initiated above OPEN CUR_F; WHILE (1 = 1) DO BEGIN FETCH CUR_F INTO :FIELD_NAME; IF (ROW_COUNT = 0) THEN LEAVE; IF (FIELDS IS NULL) THEN FIELDS = TRIM(FIELD_NAME); ELSE FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME); END CLOSE CUR_F; SCRIPT = 'CREATE VIEW ' || RELATION; IF (FIELDS IS NOT NULL) THEN SCRIPT = SCRIPT || ' (' || FIELDS || ')'; SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13); SCRIPT = SCRIPT || SOURCE; SUSPEND; END CLOSE CUR_R; END
Declaring a sub-function
PSQL
DECLARE FUNCTION subfuncname [ ( [ <in_params> ] ) ]
RETURNS <domain_or_non_array_type> [COLLATE collation]
[DETERMINISTIC]
<psql-module-body>
<in_params> ::=
!! See CREATE FUNCTION
Syntax !!
<domain_or_non_array_type> ::=
!! See Scalar Data Types Syntax !!
<psql-module-body> ::=
!! See Syntax of Module Body !!
DECLARE FUNCTION
Statement Parameters
Argument | Description |
---|---|
subfuncname |
Sub-function name |
collation |
Collation name |
The DECLARE FUNCTION
statement declares a sub-function.
A sub-function is only visible to the PSQL module that defined the sub-function.
Sub-functions have a number of restrictions:
-
A sub-function cannot be nested in another sub-routine. Sub-routines are only supported in top-level PSQL modules (stored procedures, stored functions, triggers and anonymous PSQL blocks). This restriction is not enforced by the syntax, but attempts to create nested sub-functions will raise an error “feature is not supported” with detail message “nested sub function”.
-
Currently, the sub-function has no direct access to use variables, cursors and other routines (including itself) from its parent module. This may change in a future Firebird version.
-
As a result of this restriction, a sub-function cannot call itself recursively; attempts to call it will yield error “Function unknown: subfuncname”.
-
Note
|
Declaring a sub-function with the same name as a stored function will hide that stored function from your module. It will not be possible to call that stored function. |
Note
|
Contrary to |
CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
RETURNS INTEGER
AS
- Subfunction
DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
RETURNS INTEGER
AS
BEGIN
RETURN n1 + n2;
END
BEGIN
RETURN SUBFUNC (n1, n2);
END
Declaring a sub-procedure
PSQL
DECLARE subprocname [ ( [ <in_params> ] ) ]
[RETURNS (<out_params>)]
<psq-module-body>
<in_params> ::=
!! See CREATE PROCEDURE
Syntax !!
<domain_or_non_array_type> ::=
!! See Scalar Data Types Syntax !!
<psql-module-body> ::=
!! See Syntax of Module Body !!
DECLARE PROCEDURE
Statement Parameters
Argument | Description |
---|---|
subprocname |
Sub-procedure name |
collation |
Collation name |
The DECLARE PROCEDURE
statement declares a sub-procedure.
A sub-procedure is only visible to the PSQL module that defined the sub-procedure.
Sub-procedures have a number of restrictions:
-
A sub-procedure cannot be nested in another sub-routine. Sub-routines are only supported in top-level PSQL modules (stored procedures, stored functions, triggers and anonymous PSQL blocks). This restriction is not enforced by the syntax, but attempts to create nested sub-procedures will raise an error “feature is not supported” with detail message “nested sub procedure”.
-
Currently, the sub-procedure has no direct access to use variables, cursors and other routines (including itself) from its parent module. This may change in a future Firebird version.
-
As a result of this restriction, a sub-procedure cannot call itself recursively; attempts to call it will yield error “Function unknown: subprocname”.
-
Note
|
Declaring a sub-procedure with the same name as a stored procedure, table or view will hide that stored procedure, table or view from your module. It will not be possible to call that stored procedure, table or view. |
Note
|
Contrary to |
EXECUTE BLOCK
EXECUTE BLOCK
RETURNS (name VARCHAR(31))
AS
-- Sub-procedure returning a list of tables
DECLARE PROCEDURE get_tables
RETURNS (table_name VARCHAR(31))
AS
BEGIN
FOR SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$VIEW_BLR IS NULL
INTO table_name
DO SUSPEND;
END
-- Sub-procedure returning a list of views
DECLARE PROCEDURE get_views
RETURNS (view_name VARCHAR(31))
AS
BEGIN
FOR SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$VIEW_BLR IS NOT NULL
INTO view_name
DO SUSPEND;
END
BEGIN
FOR SELECT table_name
FROM get_tables
UNION ALL
SELECT view_name
FROM get_views
INTO name
DO SUSPEND;
END
Delimiting a block of statements
PSQL
<block> ::= BEGIN [<compound_statement> ...] END <compound_statement> ::= {<block> | <statement>}
The BEGIN … END
construct is a two-part statement that wraps a block of statements that are executed as one unit of code.
Each block starts with the half-statement BEGIN
and ends with the other half-statement END
.
Blocks can be nested a maximum depth of 512 nested blocks.
A block can be empty, allowing them to act as stubs, without the need to write dummy statements.
The BEGIN and END statements have no line terminators (semicolon).
However, when defining or altering a PSQL module in the isql utility, that application requires that the last END
statement be followed by its own terminator character, that was previously switched — using SET TERM
— to some string other than a semicolon.
That terminator is not part of the PSQL syntax.
The final, or outermost, END
statement in a trigger terminates the trigger.
What the final END
statement does in a stored procedure depends on the type of procedure:
-
In a selectable procedure, the final
END
statement returns control to the caller, returning SQLCODE 100, indicating that there are no more rows to retrieve -
In an executable procedure, the final
END
statement returns control to the caller, along with the current values of any output parameters defined.
employee.fdb
database, showing simple usage of BEGIN…END
blocks:SET TERM ^;
CREATE OR ALTER PROCEDURE DEPT_BUDGET (
DNO CHAR(3))
RETURNS (
TOT DECIMAL(12,2))
AS
DECLARE VARIABLE SUMB DECIMAL(12,2);
DECLARE VARIABLE RDNO CHAR(3);
DECLARE VARIABLE CNT INTEGER;
BEGIN
TOT = 0;
SELECT BUDGET
FROM DEPARTMENT
WHERE DEPT_NO = :DNO
INTO :TOT;
SELECT COUNT(BUDGET)
FROM DEPARTMENT
WHERE HEAD_DEPT = :DNO
INTO :CNT;
IF (CNT = 0) THEN
SUSPEND;
FOR SELECT DEPT_NO
FROM DEPARTMENT
WHERE HEAD_DEPT = :DNO
INTO :RDNO
DO
BEGIN
EXECUTE PROCEDURE DEPT_BUDGET(:RDNO)
RETURNING_VALUES :SUMB;
TOT = TOT + SUMB;
END
SUSPEND;
END^
SET TERM ;^
Conditional branching
PSQL
IF (<condition>) THEN <compound_statement> [ELSE <compound_statement>]
IF … THEN … ELSE
Parameters
Argument | Description |
---|---|
condition |
A logical condition returning TRUE, FALSE or UNKNOWN |
compound_statement |
A single statement, or two or more statements wrapped in |
The conditional branch statement IF … THEN
is used to branch the execution process in a PSQL module.
The condition is always enclosed in parentheses.
If the condition returns the value TRUE, execution branches to the statement or the block of statements after the keyword THEN
.
If an ELSE
is present, and the condition returns FALSE or UNKNOWN, execution branches to the statement or the block of statements after it.
PSQL does not provide more advanced multi-branch jumps, such as CASE
or SWITCH
.
However, it is possible to chain IF … THEN … ELSE
statements, see the example section below.
Alternatively, the CASE
statement from DSQL is available in PSQL and is able to satisfy at least some use cases in the manner of a switch:
CASE <test_expr> WHEN <expr> THEN <result> [WHEN <expr> THEN <result> ...] [ELSE <defaultresult>] END CASE WHEN <bool_expr> THEN <result> [WHEN <bool_expr> THEN <result> ...] [ELSE <defaultresult>] END
...
C = CASE
WHEN A=2 THEN 1
WHEN A=1 THEN 3
ELSE 0
END;
...
-
An example using the
IF
statement. Assume that theFIRST
,LINE2
andLAST
variables were declared earlier.... IF (FIRST IS NOT NULL) THEN LINE2 = FIRST || ' ' || LAST; ELSE LINE2 = LAST; ...
-
Given
IF … THEN … ELSE
is a statement, it is possible to chain them together. Assume that theINT_VALUE
andSTRING_VALUE
variables were declared earlier.IF (INT_VALUE = 1) THEN STRING_VALUE = 'one'; ELSE IF (INT_VALUE = 2) THEN STRING_VALUE = 'two'; ELSE IF (INT_VALUE = 3) THEN STRING_VALUE = 'three'; ELSE STRING_VALUE = 'too much';
This specific example can be replaced with a simple
CASE
or theDECODE
function.
Looping constructs
PSQL
[label:] WHILE <condition> DO <compound_statement>
WHILE … DO
Parameters
Argument | Description |
---|---|
label |
Optional label for |
condition |
A logical condition returning TRUE, FALSE or UNKNOWN |
compound_statement |
A single statement, or two or more statements wrapped in |
A WHILE
statement implements the looping construct in PSQL.
The statement or the block of statements will be executed until the condition returns TRUE.
Loops can be nested to any depth.
A procedure calculating the sum of numbers from 1 to I shows how the looping construct is used.
CREATE PROCEDURE SUM_INT (I INTEGER)
RETURNS (S INTEGER)
AS
BEGIN
s = 0;
WHILE (i > 0) DO
BEGIN
s = s + i;
i = i - 1;
END
END
Executing the procedure in isql:
EXECUTE PROCEDURE SUM_INT(4);
the result is:
S
==========
10
IF … THEN … ELSE
, BREAK
, LEAVE
, CONTINUE
, EXIT
, FOR SELECT
, FOR EXECUTE STATEMENT
Exiting a loop
PSQL
[label:] <loop_stmt> BEGIN ... BREAK; ... END <loop_stmt> ::= FOR <select_stmt> INTO <var_list> DO | FOR EXECUTE STATEMENT ... INTO <var_list> DO | WHILE (<condition>)} DO
BREAK
Statement Parameters
Argument | Description |
---|---|
label |
Label |
select_stmt |
|
condition |
A logical condition returning TRUE, FALSE or UNKNOWN |
The BREAK
statement immediately terminates the inner loop of a WHILE
or FOR
looping statement.
Code continues to be executed from the first statement after the terminated loop block.
BREAK
is similar to LEAVE
, except it doesn’t support a label.
Exiting a loop
PSQL
[label:] <loop_stmt> BEGIN ... LEAVE [label]; ... END <loop_stmt> ::= FOR <select_stmt> INTO <var_list> DO | FOR EXECUTE STATEMENT ... INTO <var_list> DO | WHILE (<condition>)} DO
LEAVE
Statement Parameters
Argument | Description |
---|---|
label |
Label |
select_stmt |
|
condition |
A logical condition returning TRUE, FALSE or UNKNOWN |
The LEAVE
statement immediately terminates the inner loop of a WHILE
or FOR
looping statement.
Using the optional label parameter, LEAVE
can also exit an outer loop, that is, the loop labelled with label.
Code continues to be executed from the first statement after the terminated loop block.
-
Leaving a loop if an error occurs on an insert into the
NUMBERS
table. The code continues to be executed from the lineC = 0
.... WHILE (B < 10) DO BEGIN INSERT INTO NUMBERS(B) VALUES (:B); B = B + 1; WHEN ANY DO BEGIN EXECUTE PROCEDURE LOG_ERROR ( CURRENT_TIMESTAMP, 'ERROR IN B LOOP'); LEAVE; END END C = 0; ...
-
An example using labels in the
LEAVE
statement.LEAVE LOOPA
terminates the outer loop andLEAVE LOOPB
terminates the inner loop. Note that the plainLEAVE
statement would be enough to terminate the inner loop.... STMT1 = 'SELECT NAME FROM FARMS'; LOOPA: FOR EXECUTE STATEMENT :STMT1 INTO :FARM DO BEGIN STMT2 = 'SELECT NAME ' || 'FROM ANIMALS WHERE FARM = '''; LOOPB: FOR EXECUTE STATEMENT :STMT2 || :FARM || '''' INTO :ANIMAL DO BEGIN IF (ANIMAL = 'FLUFFY') THEN LEAVE LOOPB; ELSE IF (ANIMAL = FARM) THEN LEAVE LOOPA; ELSE SUSPEND; END END ...
Continuing with the next iteration of a loop
PSQL
[label:] <loop_stmt> BEGIN ... CONTINUE [label]; ... END <loop_stmt> ::= FOR <select_stmt> INTO <var_list> DO | FOR EXECUTE STATEMENT ... INTO <var_list> DO | WHILE (<condition>)} DO
CONTINUE
Statement Parameters
Argument | Description |
---|---|
label |
Label |
select_stmt |
|
condition |
A logical condition returning TRUE, FALSE or UNKNOWN |
The CONTINUE
statement skips the remainer of the current block of a loop and starts the next iteration of the current WHILE
or FOR
loop.
Using the optional label parameter, CONTINUE
can also start the next iteration of an outer loop, that is, the loop labelled with label.
Terminating module execution
PSQL
EXIT;
The EXIT
statement causes execution of the current PSQL module to jump to the final END
statement from any point in the code, thus terminating the program.
Calling EXIT
in a function will result in the function returning NULL
.
Passing output to the buffer and suspending execution while waiting for caller to fetch it
PSQL
SUSPEND;
The SUSPEND
statement is used in a selectable stored procedure to pass the values of output parameters to a buffer and suspend execution.
Execution remains suspended until the calling application fetches the contents of the buffer.
Execution resumes from the statement directly after the SUSPEND
statement.
In practice, this is likely to be a new iteration of a looping process.
Important
|
Important Notes
|
SUSPEND
statement in a selectable procedureCREATE PROCEDURE GEN_100
RETURNS (I INTEGER)
AS
BEGIN
I = 1;
WHILE (1=1) DO
BEGIN
SUSPEND;
IF (I=100) THEN
EXIT;
I = I + 1;
END
END
Executing dynamically created SQL statements
PSQL
<execute_statement> ::= EXECUTE STATEMENT <argument>
[<option> ...]
[INTO <variables>];
<argument> ::= <paramless_stmt>
| (<paramless_stmt>)
| (<stmt_with_params>) (<param_values>)
<param_values> ::= <named_values> | <positional_values>
<named_values> ::= paramname := <value_expr>
[, paramname := <value_expr> ...]
<positional_values> ::= <value_expr> [, <value_expr> ...]
<option> ::=
WITH {AUTONOMOUS | COMMON} TRANSACTION
| WITH CALLER PRIVILEGES
| AS USER user
| PASSWORD password
| ROLE role
| ON EXTERNAL [DATA SOURCE] <connection_string>
<connection_string> ::=
!! See <filespec> in the CREATE DATABASE
syntax !!
<variables> ::= [:]varname [, [:]varname ...]
EXECUTE STATEMENT
Statement Parameters
Argument | Description |
---|---|
paramless_stmt |
Literal string or variable containing a non-parameterized SQL query |
stmt_with_params |
Literal string or variable containing a parameterized SQL query |
paramname |
SQL query parameter name |
value_expr |
SQL expression resolving to a value |
user |
Username.
It can be a string, |
password |
Password. It can be a string or a string variable |
role |
Role.
It can be a string, |
connection_string |
Connection string. It can be a string literal or a string variable |
varname |
Variable |
The statement EXECUTE STATEMENT
takes a string parameter and executes it as if it were a DSQL statement.
If the statement returns data, it can be passed to local variables by way of an INTO
clause.
Note
|
|
You can use parameters — either named or positional — in the DSQL statement string. Each parameter must be assigned a value.
-
Named and positional parameters cannot be mixed in one query
-
If the statement has parameters, they must be enclosed in parentheses when
EXECUTE STATEMENT
is called, regardless of whether they come directly as strings, as variable names or as expressions -
Each named parameter must be prefixed by a colon (‘
:
’) in the statement string itself, but not when the parameter is assigned a value -
Positional parameters must be assigned their values in the same order as they appear in the query text
-
The assignment operator for parameters is the special operator “
:=
”, similar to the assignment operator in Pascal -
Each named parameter can be used in the statement more than once, but its value must be assigned only once
-
With positional parameters, the number of assigned values must match the number of parameter placeholders (question marks) in the statement exactly
With named parameters:
...
DECLARE license_num VARCHAR(15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
'SELECT license
FROM cars
WHERE driver = :driver AND location = :loc';
BEGIN
...
SELECT connstr
FROM databases
WHERE cust_id = :id
INTO connect_string;
...
FOR
SELECT id
FROM drivers
INTO current_driver
DO
BEGIN
FOR
SELECT location
FROM driver_locations
WHERE driver_id = :current_driver
INTO current_location
DO
BEGIN
...
EXECUTE STATEMENT (stmt)
(driver := current_driver,
loc := current_location)
ON EXTERNAL connect_string
INTO license_num;
...
The same code with positional parameters:
DECLARE license_num VARCHAR (15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
'SELECT license
FROM cars
WHERE driver = ? AND location = ?';
BEGIN
...
SELECT connstr
FROM databases
WHERE cust_id = :id
into connect_string;
...
FOR
SELECT id
FROM drivers
INTO current_driver
DO
BEGIN
FOR
SELECT location
FROM driver_locations
WHERE driver_id = :current_driver
INTO current_location
DO
BEGIN
...
EXECUTE STATEMENT (stmt)
(current_driver, current_location)
ON EXTERNAL connect_string
INTO license_num;
...
By default, the executed SQL statement runs within the current transaction.
Using WITH AUTONOMOUS TRANSACTION
causes a separate transaction to be started, with the same parameters as the current transaction.
This separate transaction will be committed when the statement was executed without errors and rolled back otherwise.
The clause WITH COMMON TRANSACTION
uses the current transaction whenever possible;
this is the default behaviour.
If the statement must run in a separate connection, an already started transaction within that connection is used, if available.
Otherwise, a new transaction is started with the same parameters as the current transaction.
Any new transactions started under the “COMMON
” regime are committed or rolled back with the current transaction.
By default, the SQL statement is executed with the privileges of the current user.
Specifying WITH CALLER PRIVILEGES
combines the privileges of the calling procedure or trigger with those of the user, just as if the statement were executed directly by the routine.
WITH CALLER PRIVILEGES
has no effect if the ON EXTERNAL
clause is also present.
With ON EXTERNAL [DATA SOURCE]
, the SQL statement is executed in a separate connection to the same or another database, possibly even on another server.
If connection_string is NULL or “''
” (empty string), the entire ON EXTERNAL [DATA SOURCE]
clause is considered absent, and the statement is executed against the current database.
-
External connections made by statements
WITH COMMON TRANSACTION
(the default) will remain open until the current transaction ends. They can be reused by subsequent calls toEXECUTE STATEMENT
, but only if connection_string is exactly the same, including case -
External connections made by statements
WITH AUTONOMOUS TRANSACTION
are closed as soon as the statement has been executed -
Statements using
WITH AUTONOMOUS TRANSACTION
can and will re-use connections that were opened earlier by statementsWITH COMMON TRANSACTION
. If this happens, the reused connection will be left open after the statement has been executed. (It must be, because it has at least one active transaction!)
-
If
WITH COMMON TRANSACTION
is in effect, transactions will be reused as much as possible. They will be committed or rolled back together with the current transaction -
If
WITH AUTONOMOUS TRANSACTION
is specified, a fresh transaction will always be started for the statement. This transaction will be committed or rolled back immediately after the statement’s execution
When ON EXTERNAL
is used, the extra connection is always made via a so-called external provider, even if the connection is to the current database.
One of the consequences is that exceptions cannot be caught in the usual way.
Every exception caused by the statement is wrapped in either an eds_connection
or an eds_statement
error.
In order to catch them in your PSQL code, you have to use WHEN GDSCODE eds_connection
, WHEN GDSCODE eds_statement
or WHEN ANY
.
Note
|
Without |
The optional AS USER
, PASSWORD
and ROLE
clauses allow specification of which user will execute the SQL statement and with which role.
The method of user login, and whether a separate connection is opened, depends on the presence and values of the ON EXTERNAL [DATA SOURCE]
, AS USER
, PASSWORD
and ROLE
clauses:
-
If
ON EXTERNAL
is present, a new connection is always opened, and:-
If at least one of
AS USER
,PASSWORD
andROLE
is present, native authentication is attempted with the given parameter values (locally or remotely, depending on connection_string). No defaults are used for missing parameters -
If all three are absent, and connection_string contains no hostname, then the new connection is established on the local server with the same user and role as the current connection. The term 'local' means “on the same machine as the server” here. This is not necessarily the location of the client
-
If all three are absent, and connection_string contains a hostname, then trusted authentication is attempted on the remote host (again, 'remote' from the perspective of the server). If this succeeds, the remote operating system will provide the username (usually the operating system account under which the Firebird process runs)
-
-
If
ON EXTERNAL
is absent:-
If at least one of
AS USER
,PASSWORD
andROLE
is present, a new connection to the current database is opened with the supplied parameter values. No defaults are used for missing parameters -
If all three are absent, the statement is executed within the current connection
-
Note
|
If a parameter value is NULL or “ |
-
There is no way to validate the syntax of the enclosed statement
-
There are no dependency checks to discover whether tables or columns have been dropped
-
Even though the performance in loops has been significantly improved in Firebird 2.5, execution is still considerably slower than when the same statements are executed directly
-
Return values are strictly checked for data type in order to avoid unpredictable type-casting exceptions. For example, the string
'1234'
would convert to an integer, 1234, but'abc'
would give a conversion error
All in all, this feature is meant to be used very cautiously, and you should always take the caveats into account. If you can achieve the same result with PSQL and/or DSQL, it will almost always be preferable.
Looping row-by-row through a selected result set
PSQL
[label:] FOR <select_stmt> [AS CURSOR cursor_name] DO <compound_statement>
FOR SELECT
Statement Parameters
Argument | Description |
---|---|
label |
Optional label for |
select_stmt |
|
cursor_name |
Cursor name. It must be unique among cursor names in the PSQL module (stored procedure, stored function, trigger or PSQL block) |
compound_statement |
A single statement, or a block of statements wrapped in |
The FOR SELECT
statement
-
retrieves each row sequentially from the result set, and executes the statement or block of statements for each row. In each iteration of the loop, the field values of the current row are copied into pre-declared variables.
Including the
AS CURSOR
clause enables positioned deletes and updates to be performed — see notes below -
can embed other
FOR SELECT
statements -
can contain named parameters that must be previously declared in the
DECLARE VARIABLE
statement or exist as input or output parameters of the procedure -
requires an
INTO
clause at the end of theSELECT … FROM …
specification. In each iteration of the loop, the field values of the current row are copied to the list of variables specified in theINTO
clause. The loop repeats until all rows are retrieved, after which it terminates -
can be terminated before all rows are retrieved by using a
BREAK
,LEAVE
orEXIT
statement
The optional AS CURSOR
clause surfaces the set in the FOR SELECT
structure as an undeclared, named cursor that can be operated on using the WHERE CURRENT OF
clause inside the statement or block following the DO
command, in order to delete or update the current row before execution moves to the next row.
In addition, it is possible to use the cursor name as a record variable (similar to OLD
and NEW
in triggers), allowing access to the columns of the result set (i.e. cursor_name.columnname).
-
When accessing a cursor variable in a DML statement, the colon prefix can be added before the cursor name (i.e.
:cursor_name.columnname
) for disambiguation, similar to variables.The cursor variable can be referenced without colon prefix, but in that case, depending on the scope of the contexts in the statement, the name may resolve in the statement context instead of to the cursor (e.g. you select from a table with the same name as the cursor).
-
Cursor variables are read-only
-
In a
FOR SELECT
statement without anAS CURSOR
clause, you must use theINTO
clause. If anAS CURSOR
clause is specified, theINTO
clause is allowed, but optional; you can access the fields through the cursor instead. -
Reading from a cursor variable returns the current field values. This means that an
UPDATE
statement (with aWHERE CURRENT OF
clause) will update not only the table, but also the fields in the cursor variable for subsequent reads. Executing aDELETE
statement (with aWHERE CURRENT OF
clause) will set all fields in the cursor variable toNULL
for subsequent reads
Other points to take into account regarding undeclared cursors:
-
The
OPEN
,FETCH
andCLOSE
statements cannot be applied to a cursor surfaced by theAS CURSOR
clause -
The cursor_name argument associated with an
AS CURSOR
clause must not clash with any names created byDECLARE VARIABLE
orDECLARE CURSOR
statements at the top of the module body, nor with any other cursors surfaced by anAS CURSOR
clause -
The optional
FOR UPDATE
clause in theSELECT
statement is not required for a positioned update
-
A simple loop through query results:
CREATE PROCEDURE SHOWNUMS RETURNS ( AA INTEGER, BB INTEGER, SM INTEGER, DF INTEGER) AS BEGIN FOR SELECT DISTINCT A, B FROM NUMBERS ORDER BY A, B INTO AA, BB DO BEGIN SM = AA + BB; DF = AA - BB; SUSPEND; END END
-
Nested
FOR SELECT
loop:CREATE PROCEDURE RELFIELDS RETURNS ( RELATION CHAR(32), POS INTEGER, FIELD CHAR(32)) AS BEGIN FOR SELECT RDB$RELATION_NAME FROM RDB$RELATIONS ORDER BY 1 INTO :RELATION DO BEGIN FOR SELECT RDB$FIELD_POSITION + 1, RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = :RELATION ORDER BY RDB$FIELD_POSITION INTO :POS, :FIELD DO BEGIN IF (POS = 2) THEN RELATION = ' "'; SUSPEND; END END END
-
Using the
AS CURSOR
clause to surface a cursor for the positioned delete of a record:CREATE PROCEDURE DELTOWN ( TOWNTODELETE VARCHAR(24)) RETURNS ( TOWN VARCHAR(24), POP INTEGER) AS BEGIN FOR SELECT TOWN, POP FROM TOWNS INTO :TOWN, :POP AS CURSOR TCUR DO BEGIN IF (:TOWN = :TOWNTODELETE) THEN -- Positional delete DELETE FROM TOWNS WHERE CURRENT OF TCUR; ELSE SUSPEND; END END
-
Using an implicitly declared cursor as a cursor variable
EXECUTE BLOCK RETURNS (o CHAR(31)) AS BEGIN FOR SELECT rdb$relation_name AS name FROM rdb$relations AS CURSOR c DO BEGIN o = c.name; SUSPEND; END END
-
Disambiguating cursor variables within queries
EXECUTE BLOCK RETURNS (o1 CHAR(31), o2 CHAR(31)) AS BEGIN FOR SELECT rdb$relation_name FROM rdb$relations WHERE rdb$relation_name = 'RDB$RELATIONS' AS CURSOR c DO BEGIN FOR SELECT -- with a prefix resolves as a cursor :c.rdb$relation_name x1, -- no prefix as an alias for the rdb$relations table c.rdb$relation_name x2 FROM rdb$relations c WHERE rdb$relation_name = 'RDB$DATABASE' AS CURSOR d DO BEGIN o1 = d.x1; o2 = d.x2; SUSPEND; END END END
Executing dynamically created SQL statements that return a row set
PSQL
[label:] FOR <execute_statement> DO <compound_statement>
FOR EXECUTE STATEMENT
Statement Parameters
Argument | Description |
---|---|
label |
Optional label for |
execute_stmt |
An |
compound_statement |
A single statement, or a block of statements wrapped in |
The statement FOR EXECUTE STATEMENT
is used, in a manner analogous to FOR SELECT
, to loop through the result set of a dynamically executed query that returns multiple rows.
SELECT
query that returns a data setCREATE PROCEDURE DynamicSampleThree (
Q_FIELD_NAME VARCHAR(100),
Q_TABLE_NAME VARCHAR(100)
) RETURNS(
LINE VARCHAR(32000)
)
AS
DECLARE VARIABLE P_ONE_LINE VARCHAR(100);
BEGIN
LINE = '';
FOR
EXECUTE STATEMENT
'SELECT T1.' || :Q_FIELD_NAME ||
' FROM ' || :Q_TABLE_NAME || ' T1 '
INTO :P_ONE_LINE
DO
IF (:P_ONE_LINE IS NOT NULL) THEN
LINE = :LINE || :P_ONE_LINE || ' ';
SUSPEND;
END
Opening a declared cursor
PSQL
OPEN cursor_name;
OPEN
Statement Parameter
Argument | Description |
---|---|
cursor_name |
Cursor name.
A cursor with this name must be previously declared with a |
An OPEN
statement opens a previously declared cursor, executes its declared SELECT
statement, and makes the first record of the result data set ready to fetch.
OPEN
can be applied only to cursors previously declared in a DECLARE .. CURSOR
statement.
Note
|
If the |
-
Using the
OPEN
statement:SET TERM ^; CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES RETURNS ( RNAME CHAR(31) ) AS DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME FROM RDB$RELATIONS); BEGIN OPEN C; WHILE (1 = 1) DO BEGIN FETCH C INTO :RNAME; IF (ROW_COUNT = 0) THEN LEAVE; SUSPEND; END CLOSE C; END^ SET TERM ;^
-
A collection of scripts for creating views using a PSQL block with named cursors:
EXECUTE BLOCK RETURNS ( SCRIPT BLOB SUB_TYPE TEXT) AS DECLARE VARIABLE FIELDS VARCHAR(8191); DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME; DECLARE VARIABLE RELATION RDB$RELATION_NAME; DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE; -- named cursor DECLARE VARIABLE CUR_R CURSOR FOR ( SELECT RDB$RELATION_NAME, RDB$VIEW_SOURCE FROM RDB$RELATIONS WHERE RDB$VIEW_SOURCE IS NOT NULL); -- named cursor with local variable DECLARE CUR_F CURSOR FOR ( SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE -- Important! The variable has to be declared earlier RDB$RELATION_NAME = :RELATION); BEGIN OPEN CUR_R; WHILE (1 = 1) DO BEGIN FETCH CUR_R INTO :RELATION, :SOURCE; IF (ROW_COUNT = 0) THEN LEAVE; FIELDS = NULL; -- The CUR_F cursor will use -- variable value of RELATION initialized above OPEN CUR_F; WHILE (1 = 1) DO BEGIN FETCH CUR_F INTO :FIELD_NAME; IF (ROW_COUNT = 0) THEN LEAVE; IF (FIELDS IS NULL) THEN FIELDS = TRIM(FIELD_NAME); ELSE FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME); END CLOSE CUR_F; SCRIPT = 'CREATE VIEW ' || RELATION; IF (FIELDS IS NOT NULL) THEN SCRIPT = SCRIPT || ' (' || FIELDS || ')'; SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13); SCRIPT = SCRIPT || SOURCE; SUSPEND; END CLOSE CUR_R; END
Fetching successive records from a data set retrieved by a cursor
PSQL
FETCH [<fetch_scroll> FROM] cursor_name [INTO [:]varname [, [:]varname ...]]; <fetch_scroll> ::= NEXT | PRIOR | FIRST | LAST | RELATIVE n | ABSOLUTE n
FETCH
Statement Parameters
Argument | Description |
---|---|
cursor_name |
Cursor name.
A cursor with this name must be previously declared with a |
varname |
Variable name |
n |
Integer expression for the number of rows |
The FETCH
statement fetches the first and successive rows from the result set of the cursor and assigns the column values to PSQL variables.
The FETCH
statement can be used only with a cursor declared with the DECLARE .. CURSOR
statement.
Using the optional fetch_scroll part of the FETCH
statement, you can specify in which direction and how many rows to advance the cursor position.
The NEXT
clause can be used for scrollable and forward-only cursors.
Other clauses are only supported for scrollable cursors.
NEXT
-
moves the cursor one row forward; this is the default
PRIOR
-
moves the cursor one record back
FIRST
-
moves the cursor to the first record.
LAST
-
moves the cursor to the last record
RELATIVE n
-
moves the cursor n rows from the current position; positive numbers move forward, negative numbers move backwards; using zero (
0
) will not move the cursor, andROW_COUNT
will be set to zero as no new row was fetched.NoteBug: Fetching First Row UsingRELATIVE
In Firebird 3.0.7 and earlier, it is not possible to fetch the first row using
FETCH RELATIVE 1
immediately after opening the cursor. As a workaround, useFETCH
(orFETCH NEXT
) to fetch the first row.This will be fixed in Firebird 3.0.8, see CORE-6486
ABSOLUTE n
-
moves the cursor to the specified row; n is an integer expression, where
1
indicates the first row. For negative values, the absolute position is taken from the end of the result set, so-1
indicates the last row,-2
the second to last row, etc. A value of zero (0
) will position before the first row.
Note
|
Bug: Positioning Beyond the Bounds of the Cursor
In Firebird 3.0.7 and earlier, using This will be fixed in Firebird 3.0.8, see CORE-6487 |
The optional INTO
clause gets data from the current row of the cursor and loads them into PSQL variables.
If fetch moved beyond the bounds of the result set, the variables will be set to NULL
.
It is also possible to use the cursor name as a variable of a row type (similar to OLD
and NEW
in triggers), allowing access to the columns of the result set (i.e. cursor_name.columnname).
-
When accessing a cursor variable in a DML statement, the colon prefix can be added before the cursor name (i.e.
:cursor_name.columnname
) for disambiguation, similar to variables.The cursor variable can be referenced without colon prefix, but in that case, depending on the scope of the contexts in the statement, the name may resolve in the statement context instead of to the cursor (e.g. you select from a table with the same name as the cursor).
-
Cursor variables are read-only
-
In a
FOR SELECT
statement without anAS CURSOR
clause, you must use theINTO
clause. If anAS CURSOR
clause is specified, theINTO
clause is allowed, but optional; you can access the fields through the cursor instead. -
Reading from a cursor variable returns the current field values. This means that an
UPDATE
statement (with aWHERE CURRENT OF
clause) will update not only the table, but also the fields in the cursor variable for subsequent reads. Executing aDELETE
statement (with aWHERE CURRENT OF
clause) will set all fields in the cursor variable toNULL
for subsequent reads -
When the cursor is not positioned on a row — it is positioned before the first row, or after the last row — attempts to read from the cursor variable will result in error “Cursor cursor_name is not positioned in a valid record”
For checking whether all the rows of the result set have been fetched, the context variable ROW_COUNT
returns the number of rows fetched by the statement.
If a record was fetched, then ROW_COUNT
is one (1
), otherwise zero (0
).
-
Using the
FETCH
statement:CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES RETURNS (RNAME CHAR(31)) AS DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME FROM RDB$RELATIONS); BEGIN OPEN C; WHILE (1 = 1) DO BEGIN FETCH C INTO RNAME; IF (ROW_COUNT = 0) THEN LEAVE; SUSPEND; END CLOSE C; END
-
Using the
FETCH
statement with nested cursors:EXECUTE BLOCK RETURNS (SCRIPT BLOB SUB_TYPE TEXT) AS DECLARE VARIABLE FIELDS VARCHAR (8191); DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME; DECLARE VARIABLE RELATION RDB$RELATION_NAME; DECLARE VARIABLE SRC TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE; -- Named cursor declaration DECLARE VARIABLE CUR_R CURSOR FOR ( SELECT RDB$RELATION_NAME, RDB$VIEW_SOURCE FROM RDB$RELATIONS WHERE RDB$VIEW_SOURCE IS NOT NULL); -- Declaring a named cursor in which -- a local variable is used DECLARE CUR_F CURSOR FOR ( SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE -- It is important that the variable must be declared earlier RDB$RELATION_NAME =: RELATION); BEGIN OPEN CUR_R; WHILE (1 = 1) DO BEGIN FETCH CUR_R INTO RELATION, SRC; IF (ROW_COUNT = 0) THEN LEAVE; FIELDS = NULL; -- Cursor CUR_F will use the value -- the RELATION variable initialized above OPEN CUR_F; WHILE (1 = 1) DO BEGIN FETCH CUR_F INTO FIELD_NAME; IF (ROW_COUNT = 0) THEN LEAVE; IF (FIELDS IS NULL) THEN FIELDS = TRIM (FIELD_NAME); ELSE FIELDS = FIELDS || ',' || TRIM(FIELD_NAME); END CLOSE CUR_F; SCRIPT = 'CREATE VIEW' || RELATION; IF (FIELDS IS NOT NULL) THEN SCRIPT = SCRIPT || '(' || FIELDS || ')' ; SCRIPT = SCRIPT || 'AS' || ASCII_CHAR (13); SCRIPT = SCRIPT || SRC; SUSPEND; END CLOSE CUR_R; EN
-
An example of using the
FETCH
statement with a scrollable cursor
EXECUTE BLOCK RETURNS (N INT, RNAME CHAR (31)) AS DECLARE C SCROLL CURSOR FOR ( SELECT ROW_NUMBER() OVER (ORDER BY RDB$RELATION_NAME) AS N, RDB$RELATION_NAME FROM RDB$RELATIONS ORDER BY RDB$RELATION_NAME); BEGIN OPEN C; -- move to the first record (N = 1) FETCH FIRST FROM C; RNAME = C.RDB$RELATION_NAME; N = C.N; SUSPEND; -- move 1 record forward (N = 2) FETCH NEXT FROM C; RNAME = C.RDB$RELATION_NAME; N = C.N; SUSPEND; -- move to the fifth record (N = 5) FETCH ABSOLUTE 5 FROM C; RNAME = C.RDB$RELATION_NAME; N = C.N; SUSPEND; -- move 1 record backward (N = 4) FETCH PRIOR FROM C; RNAME = C.RDB$RELATION_NAME; N = C.N; SUSPEND; -- move 3 records forward (N = 7) FETCH RELATIVE 3 FROM C; RNAME = C.RDB$RELATION_NAME; N = C.N; SUSPEND; -- move back 5 records (N = 2) FETCH RELATIVE -5 FROM C; RNAME = C.RDB$RELATION_NAME; N = C.N; SUSPEND; -- move to the first record (N = 1) FETCH FIRST FROM C; RNAME = C.RDB$RELATION_NAME; N = C.N; SUSPEND; -- move to the last entry FETCH LAST FROM C; RNAME = C.RDB$RELATION_NAME; N = C.N; SUSPEND; CLOSE C; END
Closing a declared cursor
PSQL
CLOSE cursor_name;
CLOSE
Statement Parameter
Argument | Description |
---|---|
cursor_name |
Cursor name.
A cursor with this name must be previously declared with a |
A CLOSE
statement closes an open cursor.
Any cursors that are still open will be automatically closed after the module code completes execution.
Only a cursor that was declared with DECLARE .. CURSOR
can be closed with a CLOSE
statement.
Executing a statement or a block of statements in an autonomous transaction
PSQL
IN AUTONOMOUS TRANSACTION DO <compound_statement>
IN AUTONOMOUS TRANSACTION
Statement Parameter
Argument | Description |
---|---|
compound_statement |
A single statement, or a block of statements |
The IN AUTONOMOUS TRANSACTION
statement enables execution of a statement or a block of statements in an autonomous transaction.
Code running in an autonomous transaction will be committed right after its successful execution, regardless of the status of its parent transaction.
This can be used when certain operations must not be rolled back, even if an error occurs in the parent transaction.
An autonomous transaction has the same isolation level as its parent transaction. Any exception that is thrown in the block of the autonomous transaction code will result in the autonomous transaction being rolled back and all changes made will be undone. If the code executes successfully, the autonomous transaction will be committed.
Using an autonomous transaction in a trigger for the database ON CONNECT
event, in order to log all connection attempts, including those that failed:
CREATE TRIGGER TR_CONNECT ON CONNECT
AS
BEGIN
-- Logging all attempts to connect to the database
IN AUTONOMOUS TRANSACTION DO
INSERT INTO LOG(MSG)
VALUES ('USER ' || CURRENT_USER || ' CONNECTS.');
IF (EXISTS(SELECT *
FROM BLOCKED_USERS
WHERE USERNAME = CURRENT_USER)) THEN
BEGIN
-- Logging that the attempt to connect
-- to the database failed and sending
-- a message about the event
IN AUTONOMOUS TRANSACTION DO
BEGIN
INSERT INTO LOG(MSG)
VALUES ('USER ' || CURRENT_USER || ' REFUSED.');
POST_EVENT 'CONNECTION ATTEMPT BY BLOCKED USER!';
END
-- now calling an exception
EXCEPTION EX_BADUSER;
END
END
Notifying listening clients about database events in a module
PSQL
POST_EVENT event_name;
POST_EVENT
Statement Parameter
Argument | Description |
---|---|
event_name |
Event name (message) limited to 127 bytes |
The POST_EVENT
statement notifies the event manager about the event, which saves it to an event table.
When the transaction is committed, the event manager notifies applications that are signalling their interest in the event.
The event name can be some sort of code, or a short message: the choice is open as it is just a string up to 127 bytes.
The content of the string can be a string literal, a variable or any valid SQL expression that resolves to a string.
Return a value from a stored function
PSQL
RETURN value;
RETURN
Statement Parameter
Argument | Description |
---|---|
value |
Expression with the value to return; Can be any expression type-compatible with the return type of the function |
The RETURN
statement ends the execution of a function and returns the value of the expression value.
RETURN
can only be used in PSQL functions (stored and local functions).
Firebird has a useful lexicon of PSQL statements and resources for trapping errors in modules and for handling them. Firebird uses built-in exceptions that are raised for errors occurring when working DML and DDL statements.
In PSQL code, exceptions are handled by means of the WHEN
statement.
Handling an exception in the code involves either fixing the problem in situ, or stepping past it;
either solution allows execution to continue without returning an exception message to the client.
An exception results in execution being terminated in the current block.
Instead of passing the execution to the END
statement, the procedure moves outward through levels of nested blocks, starting from the block where the exception is caught, searching for the code of the handler that “knows” about this exception.
It stops searching when it finds the first WHEN
statement that can handle this exception.
An exception is a message that is generated when an error occurs.
All exceptions handled by Firebird have predefined numeric values for context variables (symbols) and text messages associated with them. Error messages are output in English by default. Localized Firebird builds are available, where error messages are translated into other languages.
Complete listings of the system exceptions can be found in Appendix B: Exception Codes and Messages:
Custom exceptions can be declared in the database as persistent objects and called in the PSQL code to signal specific errors;
for example, to enforce certain business rules.
A custom exception consists of an identifier, and a default message of 1021 bytes.
For details, see CREATE EXCEPTION
.
Throwing a user-defined exception or re-throwing an exception
PSQL
EXCEPTION [ exception_name [ custom_message | USING (<value_list>)] ] <value_list> ::= <val> [, <val> ...]
EXCEPTION
Statement Parameters
Argument | Description |
---|---|
exception_name |
Exception name |
custom_message |
Alternative message text to be returned to the caller interface when an exception is thrown. Maximum length of the text message is 1,021 bytes |
val |
Value expression that replaces parameter slots in the exception message text |
The EXCEPTION
statement with exception_name throws the user-defined exception with the specified name.
An alternative message text of up to 1,021 bytes can optionally override the exception’s default message text.
The default exception message can contain slots for parameters that can be filled when throwing an exception.
To pass parameter values to an exception, use the USING
clause.
Considering, in left-to-right order, each parameter passed in the exception-raising statement as “the Nth”, with N starting at 1:
-
If the Nth parameter is not passed, its slot is not replaced
-
If a
NULL
parameter is passed, the slot will be replaced with the string “*** null ***
” -
If more parameters are passed than are defined in the exception message, the surplus ones are ignored
-
The maximum number of parameters is 9
-
The maximum message length, including parameter values, is 1053 bytes
Note
|
The status vector is generated this code combination As a new error code ( |
Warning
|
If the message contains a parameter slot number that is greater than 9, the second and subsequent digits will be treated as literal text.
For example As an example:
This will produce the following output Statement failed, SQLSTATE = HY000 exception 1 -EX1 -something wrong in abcdefghi a0 a1 |
Exceptions can be handled in a WHEN … DO
statement.
If an exception is not handled in a module, then the effects of the actions executed inside this module are cancelled, and the caller program receives the exception (either the default text, or the custom text).
Within the exception-handling block — and only within it — the caught exception can be re-thrown by executing the EXCEPTION
statement without parameters.
If located outside the block, the re-thrown EXCEPTION
call has no effect.
Note
|
Custom exceptions are stored in the system table |
-
Throwing an exception upon a condition in the
SHIP_ORDER
stored procedure:CREATE OR ALTER PROCEDURE SHIP_ORDER ( PO_NUM CHAR(8)) AS DECLARE VARIABLE ord_stat CHAR(7); DECLARE VARIABLE hold_stat CHAR(1); DECLARE VARIABLE cust_no INTEGER; DECLARE VARIABLE any_po CHAR(8); BEGIN SELECT s.order_status, c.on_hold, c.cust_no FROM sales s, customer c WHERE po_number = :po_num AND s.cust_no = c.cust_no INTO :ord_stat, :hold_stat, :cust_no; IF (ord_stat = 'shipped') THEN EXCEPTION order_already_shipped; /* Other statements */ END
-
Throwing an exception upon a condition and replacing the original message with an alternative message:
CREATE OR ALTER PROCEDURE SHIP_ORDER ( PO_NUM CHAR(8)) AS DECLARE VARIABLE ord_stat CHAR(7); DECLARE VARIABLE hold_stat CHAR(1); DECLARE VARIABLE cust_no INTEGER; DECLARE VARIABLE any_po CHAR(8); BEGIN SELECT s.order_status, c.on_hold, c.cust_no FROM sales s, customer c WHERE po_number = :po_num AND s.cust_no = c.cust_no INTO :ord_stat, :hold_stat, :cust_no; IF (ord_stat = 'shipped') THEN EXCEPTION order_already_shipped 'Order status is "' || ord_stat || '"'; /* Other statements */ END
-
Using a parameterized exception:
CREATE EXCEPTION EX_BAD_SP_NAME 'Name of procedures must start with' '@ 1' ':' '@ 2' '' ; ... CREATE TRIGGER TRG_SP_CREATE BEFORE CREATE PROCEDURE AS DECLARE SP_NAME VARCHAR(255); BEGIN SP_NAME = RDB$GET_CONTEXT ('DDL_TRIGGER' , 'OBJECT_NAME'); IF (SP_NAME NOT STARTING 'SP_') THEN EXCEPTION EX_BAD_SP_NAME USING ('SP_', SP_NAME); END
-
Logging an error and re-throwing it in the
WHEN
block:CREATE PROCEDURE ADD_COUNTRY ( ACountryName COUNTRYNAME, ACurrency VARCHAR(10)) AS BEGIN INSERT INTO country (country, currency) VALUES (:ACountryName, :ACurrency); WHEN ANY DO BEGIN -- write an error in log IN AUTONOMOUS TRANSACTION DO INSERT INTO ERROR_LOG (PSQL_MODULE, GDS_CODE, SQL_CODE, SQL_STATE) VALUES ('ADD_COUNTRY', GDSCODE, SQLCODE, SQLSTATE); -- Re-throw exception EXCEPTION; END END
Catching an exception and handling the error
PSQL
WHEN {<error> [, <error> ...] | ANY} DO <compound_statement> <error> ::= { EXCEPTION exception_name | SQLCODE number | GDSCODE errcode | SQLSTATE sqlstate_code }
WHEN … DO
Statement Parameters
Argument | Description |
---|---|
exception_name |
Exception name |
number |
SQLCODE error code |
errcode |
Symbolic GDSCODE error name |
sqlstate_code |
String literal with the SQLSTATE error code |
compound_statement |
A single statement, or a block of statements |
The WHEN … DO
statement handles Firebird errors and user-defined exceptions.
The statement catches all errors and user-defined exceptions listed after the keyword WHEN
keyword.
If WHEN
is followed by the keyword ANY
, the statement catches any error or user-defined exception, even if they have already been handled in a WHEN
block located higher up.
The WHEN … DO
block must be located at the very end of a block of statements, before the block’s END
statement.
The keyword DO
is followed by a statement, or a block of statements inside a BEGIN … END
block, that handles the exception.
The SQLCODE
, GDSCODE
, and SQLSTATE
context variables are available in the context of this statement or block.
The EXCEPTION
statement, without parameters, can also be used in this context to re-throw the error or exception.
GDSCODE
The argument for the WHEN GDSCODE
clause is the symbolic name associated with the internally-defined exception, such as grant_obj_notfound
for GDS error 335544551.
In statement or block of statements of the DO
clause, a GDSCODE
context variable, containing the numeric code, becomes available.
That numeric code is required if you want to compare a GDSCODE
exception with a targeted error.
To compare it with a specific error, you need to use a numeric values, for example 335544551
for grant_obj_notfound
.
Similar context variables are available for SQLCODE
and SQLSTATE
.
The WHEN … DO
statement or block is only executed when one of the events targeted by its conditions occurs at run-time.
If the WHEN … DO
statement is executed, even if it actually does nothing, execution will continue as if no error occurred: the error or user-defined exception neither terminates nor rolls back the operations of the trigger or stored procedure.
However, if the WHEN … DO
statement or block does nothing to handle or resolve the error, the DML statement (SELECT
, INSERT
, UPDATE
, DELETE
, MERGE
) that caused the error will be rolled back and none of the statements below it in the same block of statements are executed.
Important
|
|
A WHEN … DO
statement catches errors and exceptions in the current block of statements.
It also catches similar exceptions in nested blocks, if those exceptions have not been handled in those nested blocks.
All changes made before the statement that caused the error are visible to a WHEN … DO
statement.
However, if you try to log them in an autonomous transaction, those changes are unavailable, because the transaction where the changes took place is not committed at the point when the autonomous transaction is started.
Example 4, below, demonstrates this behaviour.
Tip
|
When handling exceptions, it is sometimes desirable to handle the exception by writing a log message to mark the fault and having execution continue past the faulty record. Logs can be written to regular tables, but there is a problem with that: the log records will “disappear” if an unhandled error causes the module to stop executing, and a rollback is performed. Use of external tables can be useful here, as data written to them is transaction-independent. The linked external file will still be there, regardless of whether the overall process succeeds or not. |
-
Replacing the standard error with a custom one:
CREATE EXCEPTION COUNTRY_EXIST ''; SET TERM ^; CREATE PROCEDURE ADD_COUNTRY ( ACountryName COUNTRYNAME, ACurrency VARCHAR(10) ) AS BEGIN INSERT INTO country (country, currency) VALUES (:ACountryName, :ACurrency); WHEN SQLCODE -803 DO EXCEPTION COUNTRY_EXIST 'Country already exists!'; END^ SET TERM ^;
-
Logging an error and re-throwing it in the
WHEN
block:CREATE PROCEDURE ADD_COUNTRY ( ACountryName COUNTRYNAME, ACurrency VARCHAR(10) ) AS BEGIN INSERT INTO country (country, currency) VALUES (:ACountryName, :ACurrency); WHEN ANY DO BEGIN -- write an error in log IN AUTONOMOUS TRANSACTION DO INSERT INTO ERROR_LOG (PSQL_MODULE, GDS_CODE, SQL_CODE, SQL_STATE) VALUES ('ADD_COUNTRY', GDSCODE, SQLCODE, SQLSTATE); -- Re-throw exception EXCEPTION; END END
-
Handling several errors in one
WHEN
block... WHEN GDSCODE GRANT_OBJ_NOTFOUND, GDSCODE GRANT_FLD_NOTFOUND, GDSCODE GRANT_NOPRIV, GDSCODE GRANT_NOPRIV_ON_BASE DO BEGIN EXECUTE PROCEDURE LOG_GRANT_ERROR(GDSCODE); EXIT; END ...
-
Catching errors using the SQLSTATE code
EXECUTE BLOCK AS DECLARE VARIABLE I INT; BEGIN BEGIN I = 1/0; WHEN SQLSTATE '22003' DO EXCEPTION E_CUSTOM_EXCEPTION 'Numeric value out of range.'; WHEN SQLSTATE '22012' DO EXCEPTION E_CUSTOM_EXCEPTION 'Division by zero.'; WHEN SQLSTATE '23000' DO EXCEPTION E_CUSTOM_EXCEPTION 'Integrity constraint violation.'; END END