The CREATE PROCEDURE statement allows you to create Java stored procedures, which you can then call using the CALL PROCEDURE statement.
The procedure owner and the database owner automatically gain the EXECUTE privilege on the procedure, and are able to grant this privilege to other users. The EXECUTE privileges cannot be revoked from the procedure and database owners.
For details on how Derby matches procedures to Java methods, see Argument matching.
CREATE PROCEDURE procedure-Name ( [ ProcedureParameter [, ProcedureParameter] ] * ) [ ProcedureElement ] *
If schema-Name is not provided, the current schema is the default schema. If a qualified procedure name is specified, the schema name cannot begin with SYS.
The default value for a parameter is IN. ParameterName must be unique within a procedure.
The syntax of DataType is described in Data types.
{ | [ DYNAMIC ] RESULT SETS INTEGER | LANGUAGE { JAVA } | DeterministicCharacteristic | EXTERNAL NAME string | PARAMETER STYLE JAVA | { NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA } }
Indicates the estimated upper bound of returned result sets for the procedure. Default is no (zero) dynamic result sets.
JAVA- the database manager will call the procedure as a public static method in a Java class.
class_name.method_nameThe External Name cannot have any extraneous spaces.
DETERMINISTIC | NOT DETERMINISTIC
Declares that the procedure is deterministic, meaning that with the same set of input values, it always computes the same result. The default is NOT DETERMINISTIC. Derby cannot recognize whether an operation is actually deterministic, so you must take care to specify the DeterministicCharacteristic correctly.
JAVA - The procedure will use a parameter-passing convention that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters will be passed as single entry arrays to facilitate returning values. Result sets are returned through additional parameters to the Java method of type java.sql.ResultSet [] that are passed single entry arrays.
Derby does not support long column types (for example Long Varchar, BLOB, and so on). An error will occur if you try to use one of these long column types.
Indicates whether the stored procedure issues any SQL statements and, if so, what type. MODIFIES SQL DATA is the default value. A stored procedure which issues a statement which does not conform to the declared SQL statement level will cause Derby to throw an exception.
CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER, IN S_YEAR INTEGER, OUT TOTAL DECIMAL(10,2)) PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 'com.acme.sales.calculateRevenueByMonth'