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
matches procedures to Java methods, see
. For
information on how stored procedures interact with deferrable constraints, see
.
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 .
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.
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 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.
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 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'