FirebirdSQL logo

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.

Module Structure

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, or EXECUTE BLOCK) and end with the last END statement of the body.

The Module Header

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, but DML triggers do have the NEW and OLD “records”, and INSERTING, UPDATING and DELETING variables.

The header of a trigger indicates the DML event (insert, update or delete, or a combination) or DDL or database event and the phase of operation (BEFORE or AFTER that event) that will cause it to “fire”.