CREATE PROCEDURE statement

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.

Syntax

CREATE PROCEDURE procedureName ( [ procedureParameter
   [ , procedureParameter ]* [...] ] )
[ procedureElement ]*

An ellipsis (...) after the last parameter indicates that the Java method supports trailing optional arguments, called varargs. The ellipsis indicates that the method may be invoked with zero or more trailing values, all having the data type of the last argument.

procedureParameter

[ { IN | OUT | INOUT } ] [ parameterName ] dataType

The default value for a parameter is IN. A parameterName must be unique within a procedure.

The syntax of dataType is described in Data types.

Note: The data types 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 |
    { DETERMINISTIC | NOT DETERMINISTIC } |
    EXTERNAL NAME singleQuotedString |
    PARAMETER STYLE { JAVA | DERBY } |
    EXTERNAL SECURITY { DEFINER | INVOKER } |
    { NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA }
}

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
EXTERNAL NAME
PARAMETER STYLE

DYNAMIC RESULT SETS integer

Indicates the estimated upper bound of returned result sets for the procedure. Default is no (zero) dynamic result sets. If the procedure takes varargs, the value must be zero.

LANGUAGE JAVA

The database manager will call the procedure as a public static method in a Java class.

DETERMINISTIC, NOT DETERMINISTIC

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 this element correctly.

EXTERNAL NAME singleQuotedString

The singleQuotedString specified by the EXTERNAL NAME clause describes the Java method to be called when the procedure is executed.

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.
DERBY: The PARAMETER STYLE must be DERBY if and only if an ellipsis (...) appears at the end of the argument list.

EXTERNAL SECURITY

If SQL authorization mode is enabled, a procedure runs by default with the privileges specified for the user who invokes the procedure (invoker's rights). To specify that the procedure should run with the privileges specified for the user who defines the procedure (definer's rights), create the procedure with EXTERNAL SECURITY DEFINER. Those privileges include the right to set the current role to a role for which the definer has privileges. When the procedure is first invoked, no role is set; even if the invoker has set a current role, the procedure running with definer's rights has no current role set initially.

See derby.database.sqlAuthorization for details about setting SQL authorization mode.

When a procedure with definer's rights is called, the current default schema is set to the eponymously named schema of the definer. For example, if the defining user is called OWNER, the default schema will also be set to OWNER.

When a procedure with invoker's rights is called, the current default schema and current role are unchanged initially within the procedure. Similarly, if SQL authorization mode is not enabled, the current default schema is unchanged initially within the procedure.

When the call returns, any changes made inside the procedure to the default current schema (and current role, if relevant) are reset (popped).

If SQL authorization mode is not enabled, an attempt to create a procedure with EXTERNAL SECURITY will result in an error.

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.

Examples

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.example.sales.calculateRevenueByMonth'


CREATE PROCEDURE VARARGPROC
( IN a INT, IN b INT, IN c BIGINT ... )
LANGUAGE JAVA
PARAMETER STYLE DERBY
READS SQL DATA
EXTERNAL NAME 'Procs.varargProc'