ALLBASE/SQL Reference Manual (36216-90216)

150 Chapter4
Constraints, Procedures, and Rules
Using Procedures
If you wish to return values to a calling application program, specify the parameter for
OUTPUT in both the CREATE PROCEDURE and EXECUTE PROCEDURE statements. If no input
value is required for a parameter, specify OUTPUT ONLY. Note that no OUTPUT option is
allowed in the EXECUTE PROCEDURE statement in ISQL nor in the EXECUTE PROCEDURE
clause of the CREATE RULE statement.
Using Local Variables in Procedures
A local variable holds a data value within a procedure. Local variable declarations must
appear at the beginning of the main body of the procedure using the DECLARE statement,
and they must specify a data type and size. Optionally, the DECLARE statement can include
nullability, language, and a default value. The following are typical examples:
DECLARE LastName CHAR(40);
DECLARE SalesPrice DECIMAL(6,2);
DECLARE LowPrice, HighPrice DECIMAL(6,2) NOT NULL;
DECLARE LocationCode INTEGER NOT NULL;
DECLARE Quantity INTEGER DEFAULT 0;
Types and sizes are the same as for column definitions, except that you cannot specify a
LONG local variable. You can declare several variables in the same DECLARE statement by
separating them with a comma provided they share the same data type, size, nullability,
native language, and default value. Within the body of the procedure, a local variable
name is prefixed with a colon (:). A local variable name cannot duplicate a parameter
name.
Local variables function in procedures much as host variables function in application
programs, but the two are not interchangeable. That is, you cannot use host variables from
the application within the body of the procedure definition nor can you use local variables
in the application. Since the application's host variables cannot be directly accessed from
within the procedure, you must use local variables or parameters in the INTO clause of
any FETCH, REFETCH, or SELECT statement within a procedure. Then, if necessary, you
transfer data to a calling application through output parameters. If multiple rows must be
returned to the calling application, a SELECT statement with no INTO clause should be
used in conjunction with a procedure cursor. Further information regarding procedure
cursors is found in the “Using Procedures in Application Programs” chapter of the
ALLBASE/SQL Advanced Application Programming Guide and in this manual under
related syntax statements (ADVANCE, CLOSE, CREATE PROCEDURE, DECLARE CURSOR,
DESCRIBE, EXECUTE PROCEDURE, FETCH, OPEN).
In contrast to host variables, local variables do not use indicator variables to handle NULL
values. A local variable itself contains the null indicator, if the variable is nullable.
Declaring a local variable to be NOT NULL makes it work like a host variable that is used
without an indicator variable.