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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-64
Considerations for CREATE PROCEDURE
CONTAINS SQL | MODIFIES SQL DATA | READS SQL DATA
specifies that the SPJ can perform SQL operations. Currently, all the options allow
an SPJ to read and modify SQL data. If you do not specify an SQL access mode,
the default is CONTAINS SQL.
DYNAMIC RESULT SETS integer
specifies the maximum number of result sets that the SPJ can return. NonStop
SQL/MX does not support result sets. If you specify this clause, you must set the
value to 0 (zero).
NOT DETERMINISTIC | DETERMINISTIC
specifies whether the SPJ always returns the same values for OUT and INOUT
parameters for a given set of argument values (DETERMINISTIC) or does not
return the same values (NOT DETERMINISTIC, the default option). For a
deterministic SPJ, the database server reserves the right to cache the results of a
CALL statement and reuse them during subsequent calls, optimizing the CALL
statement. NonStop SQL/MX allows both options but always treats the SPJ as
nondeterministic.
ISOLATE | NO ISOLATE
specifies that the SPJ executes either in the environment of the database server
(NO ISOLATE) or in an isolated environment (ISOLATE, the default option).
NonStop SQL/MX allows both options but always executes the SPJ in the SQL/MX
UDR server process (ISOLATE).
Considerations for CREATE PROCEDURE
Required Privileges
To issue a CREATE PROCEDURE statement, you must be the owner of the schema,
or be the super ID, and have read access to the Java class file or JAR file that contains
the SPJ method.
Examples of CREATE PROCEDURE
•
This CREATE PROCEDURE statement registers the SPJ named LOWERPRICE,
which does not accept any arguments:
SET CATALOG samdbcat;
SET SCHEMA sales;
CREATE PROCEDURE lowerprice()
EXTERNAL NAME 'Sales.lowerPrice()'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA;