g Optionally, the RETURNS keyword followed by a list of output parameters and their
datatypes. The procedure returns the values of the output parameters to the calling
program.
4 Declaring input parameters
Use input parameters to pass values from an application to a procedure. Any input
parameters are given in a comma-delimited list enclosed in parentheses immediately after
the procedure name, as follows:
CREATE PROCEDURE name
(var datatype [, var datatype …])
. . .
Each input parameter declaration has two parts: a name and a datatype. The name of the
parameter must be unique within the procedure, and the datatype can be any standard
SQL datatype except BLOB and arrays of datatypes. The name of an input parameter need
not match the name of any host parameter in the calling program.
Note No more than 1,400 input parameters can be passed to a stored procedure.
4 Declaring output parameters
Use output parameters to return values from a procedure to an application. The RETURNS
clause in the procedure header specifies a list of output parameters. The syntax of the
RETURNS clause is:
. . .
[RETURNS (var datatype [, var datatype …])]
140
INTERBASE 5
CREATING PROCEDURES
AS
. . .
Each output parameter declaration has two parts: a name and a datatype. The name of
the parameter must be unique within the procedure, and the datatype can be any
standard SQL datatype except BLOB and arrays.
The procedure body
Everything following the AS keyword in the CREATE PROCEDURE statement forms the
procedure body. The body consists of an optional list of local variable declarations
followed by a block of statements.
A block is composed of statements in the InterBase procedure and trigger language,
bracketed by BEGIN and END. A block can itself include other blocks, so that there can be
many levels of nesting.
InterBase procedure and trigger language includes all standard InterBase SQL statements
except data definition and transaction statements, plus statements unique to procedure
and trigger language.
Features of InterBase procedure and trigger language include:
g Assignment statements, to set values of local variables and input/output parameters.
g SELECT statements, to retrieve column values. SELECT statements must have an INTO clause
as the last clause.
g Control-flow statements, such as FOR SELECT … DO, IF … THEN, and WHILE … DO, to
perform conditional or looping tasks.
g EXECUTE PROCEDURE statements, to invoke other procedures. Recursion is allowed.
g Comments to annotate procedure code.
g Exception statements, to return error messages to applications, and WHEN statements to
handle specific error conditions.
g SUSPEND and EXIT statements, that return control—and return values of output
parameters—to the calling application.
4 BEGIN … END statements
Each block of statements in the procedure body starts with a BEGIN statement and ends
with an END statement. BEGIN and END are not followed by a semicolon. In isql, the final
END in the procedure body is followed by the terminator that you specified in the SET
TERM statement.
DATA DEFINITION GUIDE
141
CHAPTER 9 WORKING WITH STORED PROCEDURES
4 Using variables
There are three types of variables that can be used in the body of a procedure:
g Input parameters, used to pass values from an application to a stored procedure.
g Output parameters, used to pass values from a stored procedure back to the calling
application.
g Local variables, used to hold values used only within a procedure.
Any of these types of variables can be used in the body of a stored procedure where an
expression can appear. They can be assigned a literal value, or assigned a value derived
from queries or expression evaluations.
Note In SQL statements, precede variables with a colon (:) to signify that they are
variables rather than column names. In procedure and trigger language extension
statements, you need not precede variables with a colon.
LOCAL VARIABLES
Local variables are declared and used within a stored procedure. They have no effect
outside the procedure.
Local variables must be declared at the beginning of a procedure body before they can
be used. Declare a local variable as follows:
DECLARE VARIABLE var datatype;
where var is the name of the local variable, unique within the procedure, and datatype is the datatype, which can be any SQL datatype except BLOB or an array. Each local
variable requires a separate DECLARE VARIABLE statement, followed by a semicolon (;).
The following header declares the local variable, any_sales:
CREATE PROCEDURE DELETE_EMPLOYEE (EMP_NUM INTEGER)
AS
DECLARE VARIABLE ANY_SALES INTEGER;
BEGIN
. . .
INPUT PARAMETERS