SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-67
Examples of CREATE PROCEDURE
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;
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[])'