SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-67
Examples of CREATE PROCEDURE
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
-----------
4
For more examples, see the SQL/MX Guide to Stored Procedures in Java.