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.
SyntaxCREATE 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
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_nameThe 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.
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'