CREATE PROCEDURE

Creates a Java stored procedure that can be invoked using the CALL statement.

Syntax

CREATE PROCEDURE procedure-name
([ procedure-parameter [, procedure-parameter] * ])
[ procedure-element ] *

Description

CREATE PROCEDURE creates a Java stored procedure that can be invoked using the CALL statement. A procedure is invoked on the current member. Because the procedure is written in Java code, it can invoke SQL commands using JDBC internally. A qualified procedure name cannot use the SYS schema, which is reserved for internal usage. The following sections describe the syntax.
Note: GemFire XD does not support executing DDL statements in the body of a procedure or function.

procedure-name

[schemaName.]SQL92Identifier

If schemaName is not provided, the current schema is used as the the default schema. If a qualified procedure name is specified, the schema name cannot begin with SYS.

procedure-parameter

[ { IN | OUT | INOUT } ] [ parameter-name ] data-type

The default value for a parameter is IN. parameter-name must be unique within a procedure.

Note: Data-types such as CLOB, LONG VARCHAR, LONG VARCHAR FOR BIT DATA, and XML are not allowed as parameters in a CREATE PROCEDURE statement.

procedure-element

  {
| [ [DYNAMIC] RESULT SETS integer]
| LANGUAGE JAVA 
| EXTERNAL NAME 'procedure_external_class.method'
| PARAMETER STYLE JAVA
| { NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA }
}

[DYNAMIC] RESULT SETS

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.

GemFire XD 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 SQL statements and, if so, what type.

Note: GemFire XD does not support executing DDL statements in the body of a procedure or function.

CONTAINS SQL

Indicates that SQL statements that neither read nor modify SQL data can be executed by the stored procedure.

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.

MODIFIES SQL DATA

Indicates that the stored procedure can execute DML SQL statements.

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.gemfirexd.funcs.Revenue.calculateRevenueByMonth';

Data-aware procedures

A Data-Aware Procedure is created using the same CREATE PROCEDURE syntax as specified above. The difference for a data-aware procedure is in the way that the procedure is invoked with CALL and the way the procedure is implemented. For more information see Using Data-Aware Stored Procedures, and for information on implementing your own data-aware procedures, see Using the Procedure Provider API.