You can specify up to 1023 parameters for passing data between an application and a procedure. You can pass values into a procedure using any SQL expression except a subquery, an aggregate function, or a LONG column, string, TID or Date/Time function. Refer to the ALLBASE/SQL Reference Manual "Expressions" chapter for more information. (Built-in variables cannot be used as parameters, since they are
only available within the procedure.) Input parameter values can be passed using host variables or literal values. For any OUTPUT parameters, you must use host variables. (In the EXECUTE PROCEDURE clause of a CREATE RULE statement, you can pass a column name as an input parameter, but column names are not permitted in procedures invoked through
the EXECUTE PROCEDURE statement.) Note that this section discusses static parameters. Dynamic parameters are discussed in the following section,
"Using Dynamic Procedure Parameters."
The following example shows a procedure definition, followed by an excerpt from an application program that executes the procedure programmatically:
CREATE PROCEDURE ManufDB.Process12
(Operator CHAR(20),
Shift CHAR(20),
FailureType CHAR(10) NOT NULL) AS
BEGIN
INSERT INTO ManufDB.TestMonitor
VALUES (:Operator, CURRENT_DATETIME,
:Shift, :FailureType);
IF ::sqlcode = 0 THEN
COMMIT WORK;
RETURN 0;
ELSE
RETURN 1;
ENDIF;
END;
|
The following example shows the execution of procedure ManufDB.Process12 using host variables to pass in the values for Operator, Shift, and FailureType:
First, declare host variables. Note that the same names are used
for host variables in the application program that were used in the parameter definitions of the CREATE PROCEDURE statement. While this is not required, it helps in seeing the relationship between the procedure and the application program.
C Declarations: COBOL Declarations:
EXEC SQL BEGIN DECLARE SECTION; EXEC SQL BEGIN DECLARE SECTION END-EXEC.
char Operator[21]; 01 OPERATOR PIC X(20).
sqlind OperatorInd; 01 OPERATORIND SQLIND.
char Shift[21]; 01 SHIFT PIC X(20).
sqlind ShiftInd; 01 SHIFTIND SQLIND.
char FailureType[11]; 01 FAILURETYPE PIC X(10).
EXEC SQL END DECLARE SECTION; EXEC SQL END DECLARE SECTION END-EXEC.
|
A routine within the application program obtains values for
Operator, Shift, and FailureType from a user who enters the data.
If either Shift or Operator is null, the corresponding indicator
variable is set to -1.
Next, call the procedure to enter the failure information into the
database:
In C: In COBOL:
EXEC SQL EXECUTE PROCEDURE EXEC SQL EXECUTE PROCEDURE
ManufDB.Process12(:Operator ManufDB.Process12 (:OPERATOR
:OperatorInd, :Shift :OPERATORIND, :SHIFT
:ShiftInd, :FailureType); :SHIFTIND, :FAILURETYPE) END-EXEC.
|
Each host variable name in the EXECUTE PROCEDURE statement maps in sequential order to a parameter definition in the CREATE PROCEDURE statement. Inside the procedure, values are referenced by parameter name, not by host variable name. Thus, parameter names and host variable names need not be the same.
As in the example, you use indicator variables in the parameter list of the
EXECUTE PROCEDURE statement to indicate null values. But inside the procedure, the parameter itself contains the null value. In order to pass a null operator name to the procedure, you set the indicator variable OperatorInd to -1 and the content of the host variable Operator is undefined. Inside the procedure, the parameter Operator is set to NULL, and this fact can be determined through a test:
IF :Operator IS NULL THEN
PRINT 'Parameter is null';
ELSE
PRINT :Operator;
ENDIF;
|