Posts an event. Available in triggers and stored procedures.
Syntax POST_EVENT 'event_name' | col | variable;
Description POST_EVENT posts an event to the event manager. When an event occurs, this statement will notify the event manager, which alerts applications waiting for the named event. Example The following statement posts an event named “new_order”:
POST_EVENT 'new_order';
The next statement posts an event based on the current value of a column: POST_EVENT NEW.COMPANY;
The next statement posts an event based on a string variable previously declared: myval = ‘new_order:’ || NEW.COMPANY;
POST_EVENT myval; See also EVENT INIT, EVENT WAIT
For more information on events, see the Embedded SQL Statement.
SELECT
Retrieves a single row that satisfies the requirements of the search condition. The same as standard singleton SELECT, with some differences in syntax. Available in triggers and stored procedures.
<select_expr> = <select_clause> <from_clause>
[<where_clause>] [<group_by_clause>] [<having_clause>]
[<union_expression>] [<plan_clause>] [<ordering_clause>]
<into_clause>;
Description In a stored procedure, use the SELECT statement with an INTO clause to retrieve a single row value from the database and assign it to a host variable. The SELECT statement must return at most one row from the database, like a standard singleton SELECT. The INTO clause is required and must be the last clause in the statement.
Argument Description
‘event_name’ Name of the event being posted; must be enclosed in quotes col Name of a column whose value the posting will be based on variable Name of a string variable in the stored procedure or trigger
The INTO clause comes at the end of the SELECT statement to allow the use of UNION operators. UNION is not allowed in singleton SELECT statements in embedded SQL. Example The following statement is a standard singleton SELECT statement in an embedded
application: EXEC SQL
SELECT SUM(BUDGET), AVG(BUDGET) INTO :TOT_BUDGET, :AVG_BUDGET FROM DEPARTMENT
WHERE HEAD_DEPT = :HEAD_DEPT
To use the above SELECT statement in a procedure, move the INTO clause to the end as follows:
SELECT SUM(BUDGET), AVG(BUDGET) FROM DEPARTMENT
WHERE HEAD_DEPT = :HEAD_DEPT INTO :TOT_BUDGET, :AVG_BUDGET; See also FOR SELECT…DO
For a complete explanation of the standard SELECT syntax, see SELECT on page 2-121.
SUSPEND
Suspends execution of a select procedure until the next FETCH is issued and returns values to the calling application. Available in stored procedures only.
Syntax SUSPEND;
Description The SUSPEND statement:
• Suspends execution of a stored procedure until the application issues the next FETCH. • Returns values of output parameters, if any.
A procedure should ensure that all output parameters are assigned values before a SUSPEND.
SUSPEND should not be used in an executable procedure. Use EXIT instead to indicate to the reader explicitly that the statement terminates the procedure.
The following table summarizes the behavior of SUSPEND, EXIT, and END.
Note If a SELECT procedure has executable statements following the last
SUSPEND in the procedure, all of those statements are executed, even though no more rows are returned to the calling program. The procedure terminates with the final END statement, which sets SQLCODE to 100.
The SUSPEND statement also delimits atomic statement blocks in select procedures. If an error occurs in a select procedure—either a SQLCODE error, GDSCODE error, or
exception—the statements executed since the last SUSPEND are undone. Statements before the last SUSPEND are never undone, unless the transaction comprising the procedure is rolled back.
Example The following procedure illustrates the use of SUSPEND and EXIT: CREATE PROCEDURE P RETURNS (R INTEGER)
AS BEGIN R = 0; WHILE (R < 5) DO BEGIN R = R + 1; SUSPEND; IF (R = 3) THEN EXIT; END END;
If this procedure is used as a select procedure in isql, for example, SELECT * FROM P;
then it will return values 1, 2, and 3 to the calling application, since the SUSPEND statement returns the current value of r to the calling application until r = 3, when the procedure performs an EXIT and terminates.
If the procedure is used as an executable procedure in isql, for example, EXECUTE PROCEDURE P;
Table 3.1 SUSPEND, EXIT, and END
Procedure type SUSPEND EXIT END
Select procedure • Suspends execution of procedure until next FETCH is issued • Returns output values Jumps to final END • Returns control to application
• Sets SQLCODE to 100 (end of record stream)
Executable procedure
• Jumps to final END
• Not recommended Jumps to final END • Returns values • Returns control to application
then it will return 1, since the SUSPEND statement will terminate the procedure and return the current value of r to the calling application. Since SUSPEND should not be used in executable procedures, EXIT would be used instead, indicating that when the statement is encountered, the procedure is exited.
See also No rows or data returned, BEGIN … END
WHEN … DO
Error-handling statement that performs the statements following DO when the specified error occurs. Available in triggers and stored procedures.
Syntax WHEN {<error> [, <error> …] | ANY} DO <compound_statement>
<error>=
{EXCEPTION exception_name | SQLCODE number | GDSCODE errcode}
Important If used, WHEN must be the last statement in a BEGIN…END block. It should come after SUSPEND, if present.
Description Procedures can handle three kinds of errors with a WHEN statement:
• Exceptions raised by EXCEPTION statements in the current procedure, in a nested procedure, or in a trigger fired as a result of actions by such a procedure.
• SQL errors reported in SQLCODE. • InterBase error codes.
The WHEN ANY statement handles any of the three types. Argument Description
EXCEPTION
exception_name
The name of an exception already in the database
SQLCODE number A SQLCODE error code number
GDSCODE errcode An InterBase error code. Use Table 5.5 and strip “isc_ before mentioning the errorcode with GDSCODE usage. For example: GDSCODE lock_conflict.
ANY Keyword that handles any of the above types of errors compound_statement Statement or block executed when any of the specified errors