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.

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 }
| 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.

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.

CONTAINS SQL: Indicates that SQL statements that neither read nor modify SQL data can be executed by the stored procedure. Statements that are not supported in any stored procedure return a different error. MODIFIES SQL DATA is the default value.
NO SQL: Indicates that the stored procedure cannot execute any SQL statements
READS SQL DATA: Indicates that some SQL statements that do not modify SQL data can be included in the stored procedure. Statements that are not supported in any stored procedure return a different error.
MODIFIES SQL DATA: Indicates that the stored procedure can execute any SQL statement except statements that are not supported in stored procedures.

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'