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.
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.
ProcedureParameter
[ { IN | OUT | INOUT } ] [ parameter-Name ] DataType
The default value for a parameter is IN. ParameterName must be
unique within a procedure.
The syntax of DataType is described
in Data types.
Note: Data-types
such as BLOB, CLOB, LONG VARCHAR, LONG VARCHAR FOR BIT DATA, and XML are not
allowed as parameters in a CREATE PROCEDURE statement.
ProcedureElement
{
| [ DYNAMIC ] RESULT SETS INTEGER
| LANGUAGE { JAVA }
| DeterministicCharacteristic
| EXTERNAL NAME string
| PARAMETER STYLE JAVA
| { NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA }
}
DYNAMIC RESULT SETS integer
Indicates the
estimated upper bound of returned result sets for the procedure. Default is
no (zero) dynamic result sets.
LANGUAGE
JAVA- the database manager will
call the procedure as a public static method in a Java class.
EXTERNAL NAME string
String describes
the Java method to be called when the procedure is executed, and takes the
following form:
class_name.method_name
The External
Name cannot have any extraneous spaces.
DeterministicCharacteristic
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.
PARAMETER STYLE
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.
NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA
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.
- NO SQL
- Indicates that the stored procedure cannot execute any SQL statements
- CONTAINS SQL
- Indicates that SQL statements that neither read nor modify SQL data can
be executed by the stored procedure.
- READS SQL DATA
- Indicates that SQL statements that do not modify SQL data (for
example, SELECT statements) can be included in the stored procedure.
- MODIFIES SQL DATA
- Indicates that the stored procedure can execute any SQL statement.
The procedure elements may appear in any order, but each type
of element can only appear once. A procedure definition must contain these
elements:
- LANGUAGE
- PARAMETER STYLE
- EXTERNAL NAME
Example
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'
