SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-134
Considerations for EXECUTE
Considerations for EXECUTE
Scope of EXECUTE
A statement must be compiled by PREPARE before you EXECUTE it, but after it is
compiled, you can execute the statement multiple times without recompiling it.
The statement must have been compiled during the same MXCI session as its
execution.
The statement must have been prepared during the same compilation unit as its
execution.
MXCI Examples of EXECUTE
•
Use PREPARE to compile a statement once, and then execute the statement
multiple times with different parameter values. This example uses the SET PARAM
command to set the parameter values in the prepared statement.
PREPARE FINDEMP FROM
SELECT * FROM persnl.employee
WHERE salary > ?SALARY AND jobcode = ?JOBCODE;
--- SQL command prepared.
SET PARAM ?SALARY 40000.00;
SET PARAM ?JOBCODE 450;
EXECUTE FINDEMP;
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY
------ ---------- ---------- ------- ------- --------
232 THOMAS SPINNER 4000 450 45000.00
--- 1 row(s) selected.
SET PARAM ?SALARY 20000.00;
SET PARAM ?JOBCODE 300;
EXECUTE FINDEMP;
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY
------ ---------- ---------- ------- ------- --------
75 TIM WALKER 3000 300 32000.00
89 PETER SMITH 3300 300 37000.40
...
--- 13 row(s) selected.
•
Use EXECUTE USING for both parameter values, which are unnamed in the
prepared statement:
PREPARE FINDEMP FROM
SELECT * FROM persnl.employee
WHERE salary > ? AND jobcode = ?;
EXECUTE FINDEMP USING 40000.00,450;
EXECUTE FINDEMP USING 20000.00,300;
MXCI
C/COBOL