CREATE PROCEDURE

The CREATE PROCEDURE statement allows you to create Java procedures, which you can then call using the CALL PROCEDURE statement.

For details on how Splice Machine matches procedures to Java methods, see Argument matching.

Syntax

CREATE PROCEDURE procedureName (
     [ procedureParameter
     [, procedureParameter] ] *
    )
     [ ProcedureElement ] *

procedureName

If schemaName is not provided, then 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 } ] [ parameterName ] DataType

parameterName is an identifier that must be unique within the procedure’s parameter names.

By default, parameters are IN parameters unless you specify otherwise.

Data-types such as BLOB, CLOB, LONG VARCHAR are not allowed as parameters in a CREATE PROCEDURE statement.

Also: At this time, Splice Machine will return only one ResultSet from a stored procedure.

procedureElement

See the description of procedure Elements in the next section.

Procedure Elements

 {
    LANGUAGE { JAVA }
  | DeterministicCharacteristic
  | EXTERNAL NAME javaMethodName
  | PARAMETER STYLE parameterStyle
  | sqlStatementType
}

The procedure elements may appear in any order, but each type of element can only appear once. These procedure elements are required:

  •  LANGUAGE
  • EXTERNAL NAME
  • PARAMETER STYLE

LANGUAGE

Only JAVA is accepted at this time. Splice Machine will call the procedure as a public static method in a Java class.

DeterministicCharacteristic

DETERMINISTIC | NOT DETERMINISTIC

The default value is NOT DETERMINISTIC.

Specifying DETERMINISTIC indicates that the procedure always returns the same result, given the same input values. This allows Splice Machine to call the procedure with greater efficiency; however, specifying this for a procedure that is actually non-deterministic will have the opposite effect – efficiency of calls to the procedure will be reduced.

javaMethodName

class_name.method_name

This is the name of the Java method to call when this procedure executes.

parameterStyle

JAVA

Stored procedures use a parameter-passing convention is used that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters are passed as single entry arrays to facilitate returning values. Result sets can be returned through additional parameters to the Java method of type java.sql.ResultSet[] that are passed single entry arrays.

Splice Machine does not support long column types such as LONG VARCHARor BLOB; an error will occur if you try to use one of these long column types.

sqlStatementType

CONTAINS SQL

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

NO SQL

Indicates that the 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 procedure. This is the default value.

MODIFIES SQL DATA

Indicates that the procedure can execute any SQL statement.

Example

The following example depends on a fictionalized java class. For functional examples of using CREATE PROCEDURE, please see the Using Functions and Stored Procedures section of our Developer’s Guide.

splice> 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';
0 rows inserted/updated/deleted

See Also