The Firebird 5.0 Language Reference | Procedural SQL (PSQL) Statements

Data Manipulation (DML) StatementsBuilt-in Scalar Functions

Procedural SQL (PSQL) is a procedural extension of SQL.This language subset is used for writing PSQL modules: stored procedures, stored functions, 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 slightly modified syntax in some cases.

Elements of PSQL

A PSQL module may contain declarations of local variables, subroutines and cursors, assignments, conditional statements, loops, statements for raising custom exceptions, error handling and sending messages (events) to client applications.DML triggers have access to special context variables, two “records” that store, respectively, the NEW values for all columns during insert and update activity, and the OLD values during update and delete work, and three Boolean variables — INSERTING, UPDATING and DELETING — to determine the event that fired the trigger.

Statements that modify metadata (DDL) are not available in PSQL.

DML Statements with Parameters

If DML statements (SELECT, INSERT, UPDATE, DELETE, etc.) in the body of a 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.

Transactions

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 fired on connect or disconnect.

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.