Using Variables
Go Up to The Procedure Body
There are three types of variables that can be used in the body of a procedure:
- Input parameters, used to pass values from an application to a stored procedure.
- Output parameters, used to pass values from a stored procedure back to the calling application.
- 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.
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 data_type;
where <var> is the name of the local variable, unique within the procedure, and <data_type> is the data type, which can be any SQL data type 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
Input parameters are used to pass values from an application to a procedure. They are declared in a comma-delimited list in parentheses following the procedure name. Once declared, they can be used in the procedure body anywhere an expression can appear.
Input parameters are passed by value from the calling program to a stored procedure. This means that if the procedure changes the value of an input parameter, the change has effect only within the procedure. When control returns to the calling program, the input parameter still has its original value.
The following procedure header declares two input parameters, EMP_NO and
PROJ_ID:
CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT, PROJ_ID CHAR(5))
AS
. . .
For more information on declaring input parameters in stored procedures, see Declaring Input Parameters.
Output Parameters
Output parameters are used to return values from a procedure to the calling application. Declare them in a comma-delimited list in parentheses following the RETURNS keyword in the procedure header. Once declared, they can be used in the procedure body anywhere an expression can appear. For example, the following procedure header declares five output parameters, HEAD_DEPT, DEPARTMENT, MNGR_NAME, TITLE, and EMP_CNT:
CREATE PROCEDURE ORG_CHART
RETURNS (HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25),
MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER)
If you declare output parameters in the procedure header, the procedure must assign them values to return to the calling application. Values can be derived from any valid expression in the procedure.
For more information on declaring output parameters in stored procedures, see Declaring Output Parameters.
A procedure returns output parameter values to the calling application with a SUSPEND statement. For more information about SUSPEND, see Using SUSPEND, EXIT, and END With Procedures.
In a SELECT statement that retrieves values from a procedure, the column names must match the names and data types of the procedure’s output parameters. In an EXECUTE PROCEDURE statement, the output parameters need not match the names of the procedure’s output parameters, but the data types must match.