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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-65
Examples of CREATE PROCEDURE
The statement verifies the existence of the SPJ method, lowerprice, in the
/usr/mydir/myclasses/Sales.class.
Because the procedure name is not qualified by a catalog and schema, NonStop
SQL/MX qualifies it according to the current settings of CATALOG and SCHEMA,
which are SAMDBCAT and SALES in this case. To call this SPJ, use this
statement:
CALL lowerprice();
•
This CREATE PROCEDURE statement registers the SPJ named TOTALPRICE,
which accepts three input parameters and returns a numeric value, the total price,
to an INOUT parameter:
CREATE PROCEDURE samdbcat.sales.totalprice(IN NUMERIC (18),
IN VARCHAR (10),
INOUT price NUMERIC (18,2))
EXTERNAL NAME 'pkg.subpkg.Sales.totalPrice'
EXTERNAL PATH '/usr/mydir/myJar.jar'
LANGUAGE JAVA
PARAMETER STYLE JAVA;
To call this SPJ in MXCI, use these statements:
SET PARAM ?p 10.00;
CALL samdbcat.sales.totalprice(23, 'standard', ?p);
PRICE
---------------------
253.96
•
This CREATE PROCEDURE statement registers the SPJ named
MONTHLYORDERS, which accepts an integer value for the month and returns the
number of orders:
CREATE PROCEDURE samdbcat.sales.monthlyorders(IN INT,
OUT number INT)
EXTERNAL NAME
'Sales.numMonthlyOrders (int, java.lang.Integer[])'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA;
Because the OUT parameter is supposed to map to the Java wrapper class,
java.lang.Integer, you must specify the Java signature in the EXTERNAL
NAME clause. To call this SPJ, use this statement:
CALL samdbcat.sales.monthlyorders(3, ?);
NUMBER
-----------