| Firebird Documentation Index → Firebird 1.5.6 Release Notes → SQL Language Enhancements → Stored Procedure and Trigger Language (PSQL) |
|
|
|
The following enhancements have been made to PSQL, the set of language extensions available for writing stored procedures and triggers.
EXECUTE STATEMENT "string" is a PSQL extension which takes a string that is a valid dynamic SQL statement and executes it as if it had been submitted to DSQL.
Available in triggers and stored procedures.
Syntax Patterns
The syntax may have three forms.-
Executes "string> as an SQL operation that does not return any data rows, viz. INSERT, UPDATE, DELETE, EXECUTE PROCEDURE or any DDL statement except CREATE/DROP DATABASE.
EXECUTE STATEMENT <string>;
Example
CREATE PROCEDURE DynamicSampleOne (Pname VARCHAR(100))
AS
DECLARE VARIABLE Sql VARCHAR(1024);
DECLARE VARIABLE Par INT;
BEGIN
SELECT MIN(SomeField) FROM SomeTable INTO :Par;
Sql = 'EXECUTE PROCEDURE ' || Pname || '(';
Sql = Sql || CAST(Par AS VARCHAR(20)) || ')';
EXECUTE STATEMENT Sql;
END
Executes "string" as an SQL operation, returning single data row. Only singleton SELECT operators may be executed with this form of EXECUTE STATEMENT.
EXECUTE STATEMENT <string> INTO :var1, [&, :varn] ;
CREATE PROCEDURE DynamicSampleTwo (TableName VARCHAR(100))
AS
DECLARE VARIABLE Par INT;
BEGIN
EXECUTE STATEMENT
'SELECT MAX(CheckField) FROM ' || TableName INTO :Par;
IF (Par > 100) THEN
EXCEPTION Ex_Overflow 'Overflow in ' || TableName;
END
Executes "string" as SQL operation, returning multiple data rows. Any SELECT operator may be executed with this form of EXECUTE STATEMENT.
FOR EXECUTE STATEMENT <string> INTO :var1, &, :varn
DO
<compound-statement>;
Example
CREATE PROCEDURE DynamicSampleThree (
TextField VARCHAR(100),
TableName VARCHAR(100))
RETURNS (Line VARCHAR(32000))
AS
DECLARE VARIABLE OneLine VARCHAR(100);
BEGIN
Line = '';
FOR EXECUTE STATEMENT
'SELECT ' || TextField || ' FROM ' || TableName
INTO :OneLine
DO
IF (OneLine IS NOT NULL) THEN
Line = Line || OneLine || ' ';
SUSPEND;
END
The 'EXECUTE STATEMENT' DSQL string cannot contain any parameters in any syntax variation. All variable substitution into the static part of the SQL statement should be performed before the execution of EXECUTE STATEMENT.
This feature is intended only for very cautious use and should be used with all factors taken into account. It should be a rule of thumb to use EXECUTE STATEMENT only when other methods are impossible, or perform even worse than EXECUTE STATEMENT.
EXECUTE STATEMENT is potentially unsafe in several ways:
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.
Operations will be slow because the embedded statement has to be prepared every time it is executed.
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.
If the stored procedure has special privileges on some objects, the dynamic statement submitted in the EXECUTE STATEMENT string does not inherit them. Privileges are restricted to those granted to the user who is executing the procedure.
A number of new context variables for PSQL have been implemented.
These context variables return the system identifier of the active connection or the current transaction context, respectively. Return type is INTEGER. Available in DSQL and PSQL.
Because these values are stored on the database header page, they will be reset after a database restore.
Syntax Patterns
CURRENT_CONNECTION
CURRENT_TRANSACTION
Examples
SELECT CURRENT_CONNECTION FROM RDB$DATABASE;
NEW.TXN_ID = CURRENT_TRANSACTION;
EXECUTE PROCEDURE P_LOGIN(CURRENT_CONNECTION);
Returns an integer, the number of rows affected by the last DML statement. Available in PSQL, in the context of the procedure or trigger module.
Currently returns zero from a SELECT statement.
Syntax Pattern
ROW_COUNT
Examples
UPDATE TABLE1 SET FIELD1 = 0 WHERE ID = :ID;
IF (ROW_COUNT = 0) THEN
INSERT INTO TABLE1 (ID, FIELD1) VALUES (:ID, 0);
ROW_COUNT cannot be used for checking the rows affected by an EXECUTE STATEMENT command.
Each context variable returns an integer which is the numeric error code for the active exception. Available in PSQL, within the scope of the particular exception handling block. Both will evaluate to zero outside the block.
The GDSCODE variable returns a numeric representation of the GDS (ISC) error code, e.g. '335544349L' will return 335544349.
A 'WHEN SQLCODE' or 'WHEN ANY' exception block will catch a non-zero value for the SQLCODE variable and return zero for GDSCODE. Only a 'WHEN GDSCODE' block can catch a non-zero GDSCODE variable (and will return zero in SQLCODE).
If a user-defined exception is thrown, both SQLCODE and GDSCODE variables contain zero, regardless of the exception handling block type.
Syntax Pattern
SQLCODE
GDSCODE
Example
BEGIN
...
WHEN SQLCODE -802 DO
EXCEPTION E_EXCEPTION_1;
WHEN SQLCODE -803 DO
EXCEPTION E_EXCEPTION_2;
WHEN ANY DO
EXECUTE PROCEDURE P_ANY_EXCEPTION(SQLCODE);
END
See also the EXCEPTION HANDLING ENHANCEMENTS, below, and the document README.exception_handling in the firebird2/doc/sql.extensions branch of the Firebird CVS tree.
Three pseudo-Boolean expressions that can be tested to determine the type of DML operation being executed. Available in PSQL, only in triggers. Intended for use with multi-action triggers (see the DML section, above).
Syntax Pattern
INSERTING
UPDATING
DELETING
Example
IF (INSERTING OR UPDATING) THEN
BEGIN
IF(NEW.SERIAL_NUM IS NULL) THEN
NEW.SERIAL_NUM = GEN_ID(G_GENERATOR_1, 1);
The common syntax for an EXCEPTION statement in PSQL is:
EXCEPTION [name [value]];
The enhancements in 1.5 allow you to
define a run-time message for a named exception
re-initiate (re-raise) a caught exception within the scope of the exception block
Obtain a numeric error code for a caught exception
Syntax Pattern
EXCEPTION <exception_name> <message_value>;
Examples
a)
EXCEPTION E_EXCEPTION_1 'Error!';
b)
EXCEPTION
E_EXCEPTION_2 'Wrong type for record with ID=' || new.ID;
This has no effect outside an exception block.
Syntax Pattern
EXCEPTION;
Examples
a)
BEGIN
...
WHEN SQLCODE -802 DO
EXCEPTION E_ARITH_EXCEPT;
WHEN SQLCODE -802 DO
EXCEPTION E_KEY_VIOLATION;
WHEN ANY THEN
EXCEPTION;
END
b)
WHEN ANY DO
BEGIN
INSERT INTO ERROR_LOG (...) VALUES (SQLCODE, ...);
EXCEPTION;
END
Terminates the flow in a loop, causing flow of control to move to the statement following the END statement that completes that loop. Available for WHILE, FOR SELECT and FOR EXECUTE language constructs only, otherwise a parser error will be thrown. Available in triggers as well as stored procedures.
The SQL-99 standard keyword LEAVE deprecates the existing use of BREAK.
Syntax Pattern
LEAVE;
Examples
i)
BEGIN
<statements>;
IF (<conditions>) THEN
LEAVE;
<statements>;
END
ii)
WHILE (<condition>) DO
BEGIN
<statements>;
WHEN ... DO
LEAVE;
END
The condition that branches to a LEAVE statement must be inside a block that is controlled by a looping construct (i.e., WHILE or FOR SELECT...INTO...DO).
It is emphasised that LEAVE will not terminate other types of BEGIN...END block.
LEAVE | BREAK and EXIT statements can now be used in triggers.
Until now, a trigger containing a PLAN statement would be rejected by the compiler. Now, a valid plan can be included and will be used.
Empty BEGIN..END blocks in PSQL modules are now legal. For example, you can now write "stub" modules like
CREATE TRIGGER BI_ATABLE FOR ATABLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
END ^
|
| Firebird Documentation Index → Firebird 1.5.6 Release Notes → SQL Language Enhancements → Stored Procedure and Trigger Language (PSQL) |