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 procedure-Name ( [ ProcedureParameter
   [, ProcedureParameter] ] * )
[ ProcedureElement ] *

procedure-Name

[ schemaName. ] SQL92Identifier

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
| EXTERNAL SECURITY { DEFINER | INVOKER }
| { 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.

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.

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'