Procedure and Trigger Language

Go Up to Creating Procedures

The InterBase procedure and trigger language is a complete programming language for stored procedures and triggers. It includes:

• SQL data manipulation statements: INSERT, UPDATE, DELETE, and singleton SELECT. Cursors are allowed.
• SQL operators and expressions, including UDFs linked with the database server and generators.
• Powerful extensions to SQL, including assignment statements, control-flow statements, context variables, event-posting statements, exceptions, and error-handling statements.

Although stored procedures and triggers are used in different ways and for different purposes, they both use the procedure and trigger language. Both triggers and stored procedures can use any statements in the procedure and trigger language, with some exceptions:

• Context variables are unique to triggers.
• Input and output parameters, and the SUSPEND and EXIT statements, which return values and are unique to stored procedures.

The stored procedure and trigger language does not include many of the statement types available in DSQL or gpre. The following statement types are not supported in triggers or stored procedures:

• Data definition language statements: CREATE, ALTER, DROP, DECLARE EXTERNAL FUNCTION, and DECLARE FILTER
• Transaction control statements: SET TRANSACTION, COMMIT, ROLLBACK
• Dynamic SQL statements: PREPARE, DESCRIBE, EXECUTE
• CONNECT/DISCONNECT, and sending SQL statements to another database
• GRANT/REVOKE
• SET GENERATOR
• EVENT INIT/WAIT
• BEGIN/END DECLARE SECTION
• BASED ON
• WHENEVER
• DECLARE CURSOR
• OPEN
• FETCH

The following table summarizes the language extensions for stored procedures.

Procedure and trigger language extensions

Statement	Description
BEGIN … END	Defines a block of statements that executes as one; the BEGIN keyword starts the block, the END keyword terminates it. Neither should be followed by a semicolon.
<variable> = <expression>	Assignment statement which assigns the value of <expression> to <variable>, a local variable, input parameter, or output parameter.
/* comment_text */	Programmer’s comment, where <comment_text> can be any number of lines of text.
EXCEPTION <exception_name>	Raises the named exception. Exception: A user-defined error that can be handled with WHEN.
EXECUTE PROCEDURE <proc_name>     [<var> [, <var> …]]        [RETURNING_VALUES var           [, <var> …]]	Executes stored procedure, <proc_name>, with the input arguments listed following the procedure name, returning values in the output arguments listed following RETURNING_VALUES. Enables nested procedures and recursion. Input and output parameters must be variables defined within the procedure.
EXIT	Jumps to the final END statement in the procedure.
FOR <select_statement>   ­DO <compound_statement>.	Repeats the statement or block following DO for every qualifying row retrieved by <select_statement>. <select_statement>: a normal SELECT statement, except that the INTO clause is required and must come last.
<compound_statement>	Either a single statement in procedure and trigger language or a block of statements bracketed by BEGIN and END.
IF (<condition>)   THEN <compound_statement>   [ELSE <compound_statement>].	Tests <condition> and if it is TRUE, performs the statement or block following THEN. Otherwise, ­performs the statement or block following ELSE, if present. <condition>: a Boolean expression (TRUE, FALSE, or UNKNOWN), generally two expressions as operands of a comparison operator.
POST_EVENT <event_name>	Posts the event, <event_name>.
SUSPEND	In a SELECT procedure: Suspends execution of procedure until next FETCH is issued by the calling application. Returns output values, if any, to the calling ­application. Not recommended for executable procedures.
WHILE (<condition>)   DO <compound_statement>	While <condition> is TRUE, keep performing <compound_statement>. First <condition> is tested, and if it is TRUE, then <compound_statement> is performed. This sequence is repeated until <condition> is no longer TRUE.
WHEN   {<error> [, <error> …] | ANY}   DO <compound_statement>	Error-handling statement. When one of the specified errors occurs, performs <compound_statement>. WHEN statements, if present, must come at the end of a block, just before END. <error>: EXCEPTION <exception_name>, SQLCODE <errcode> or GDSCODE <number>. ANY: Handles any errors.

Topics

• Syntax Errors in Stored Procedures