CREATE PROCEDURE

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

Creating stored procedures in Python is currently a Beta Release feature; it will become generally available in a future release.

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 | PYTHON }
  | { EXTERNAL NAME javaMethodName | AS ' pythonScript ' }
  | DeterministicCharacteristic
  | PARAMETER STYLE parameterStyle
  | DYNAMIC RESULT SETS integer
  | 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

Specify the language in which your procedure is written; this must be JAVA or PYTHON.

Creating stored procedures in Python is currently a Beta Release feature; it will become generally available in a future release.

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.

pythonScript

def run(scriptArgs): scriptCode

This is the Python script, enclosed in single quotes ('). Here are a few important notes about Python scripts in stored procedures, which are described more fully in the Using Functions and Stored Procedures section of our Developer’s Guide:

  • This feature is currently in Beta release.
  • The entire script must be enclosed in single quotes.
  • Use double quotes (") around strings within the script; if you must use a single quote within the script, specify it as two single quotes ('').
  • Use spaces instead of tabs within your scripts; the command line processor will convert tabs to a single space in your script, even within a string.
  • Write the script under the run function.
  • The arguments you specify for your script in the CREATE PROCEDURE statement should match the order specified in your method definition.

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.

DYNAMIC RESULT SETS integer

Specifies the number of dynamic result sets produced by the procedure.

Currently, Splice Machine only supports 0 or 1 dynamic result sets.

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.

Examples

This section contains two examples of creating procedures: one in JAVA, and another in PYTHON. For functional examples of using CREATE PROCEDURE, please see the Using Functions and Stored Procedures section of our Developer’s Guide.

Example of Creating a Stored Procedure in Java

The following example depends on a fictionalized java class.

splice> CREATE PROCEDURE SALES.TOTAL_REVENUE (
    IN S_MONTH INTEGER,
    IN S_YEAR INTEGER, OUT TOTAL DECIMAL(10,2) )
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    DYNAMIC RESULT SETS 1
    READS SQL DATA
    EXTERNAL NAME 'com.example.sales.calculateRevenueByMonth';
0 rows inserted/updated/deleted

Example of Creating a Stored Procedure in Python

The following example creates a Python stored procedure that executes an SQL statement.

Creating stored procedures in Python is currently a Beta Release feature; it will become generally available in a future release.

splice> CREATE PROCEDURE SPLICE.PYTHON_TEST (
    IN limit INT )
    PARAMETER STYLE JAVA
    LANGUAGE PYTHON
    DYNAMIC RESULT SETS 1
    READS SQL DATA
    AS 'def run(lim, res):
       c = conn.cursor()
           # select alias and javaclassname columns from sys.sysaliases tables
           # return them as a ResultSet
       stmt = "select alias, javaclassname from sys.sysaliases {limit ?}"
       c.executemany(stmt,[lim])
       d = c.description
       result = c.fetchall()
           # construct the ResultSet and fill it into the ResultSet list res
       res[0] = factory.create([d,result])
       conn.commit()
       c.close()
       conn.close()';
0 rows inserted/updated/deleted

See Also