Using Functions and Stored Procedures

This topic provides an overview of writing and using functions and stored procedures in Splice Machine.

About User-Defined Functions

You can create user-defined database functions (UDFs) that can be evaluated in SQL statements; these functions can be invoked where most other built-in functions are allowed, including within SQL expressions and SELECT statements. Functions must be deterministic, and cannot be used to make changes to the database.

You can create two kinds of functions:

  • Scalar functions, which always return a single value (or NULL),
  • Table functions, which return a table.

When you invoke a function within a SELECT statement, it is applied to each retrieved row. For example:

SELECT ID, Salary, MyAdjustSalaryFcn(Salary) FROM SPLICEBBALL.Salaries;

This SELECT will execute the MyAdjustSalaryFcn to the Salary value for each player in the table.

About Stored Procedures

You can group a set of SQL commands together with variable and logic into a stored procedure, which is a subroutine that is stored in your database’s data dictionary. Unlike user-defined functions, a stored procedure is not an expression and can only be invoked using the CALL statement. Stored procedures allow you to modify the database and return Result Sets or nothing at all.

Stored procedures can be used for situations where a complex set of SQL statements are required to process something, and that process is used by various applications; creating a stored procedure increases performance efficiency. They are typically used for:

  • checking business rules and validating data before performing actions
  • performing significant processing of data with the inputs to the procedure

Comparison of Functions and Stored Procedures

Here’s a comparison of Splice Machine functions and stored procedures:

Database Function Stored Procedure

A Splice Machine database function:

  • must be written as a public static method in a Java public class
  • is executed in exactly the same manner as are public static methods in Java
  • can have multiple input parameters
  • always returns a single value (which can be null)
  • cannot modify data in the database

Splice Machine stored procedures can:

  • return result sets or return nothing at all
  • issue update, insert, and delete statements
  • perform DDL statements such as create and drop
  • consolidate and centralize code
  • reduce network traffic and increase execution speed
Can be used in SELECT statements.

Cannot be used in in SELECT statements.

Must be invoked using a CALL statement.

Create with the CREATE FUNCTION statement, which is described in our SQL Reference book.

You can find an example in the Function and Stored Procedure Examples topic in this section.

Create with the CREATE PROCEDURE statement, which is described in our SQL Reference book.

You can find an example in the Function and Stored Procedure Examples topic in this section.

Operations in Which You Can Use Functions and Stored Procedures

The following table provides a list of the differences between functions and stored procedures with regard to when and where they can be used:

Operation Functions Stored Procedures
Execute in an SQL Statement Yes No
Execute in a Trigger Yes Triggers that execute before an operation (before triggers) cannot modify SQL data.
Process OUT / INOUT Parameters No Yes
Return Resultset(s) No Yes
Execute SQL Select Yes Yes
Execute SQL Update/Insert/Delete No Yes
Execute DDL (Create/Drop) No Yes

Viewing Functions and Stored Procedures

You can use the  show functions and show procedures commands in the splice> command line interface to display the functions and stored procedures available in your database:

Command Output
splice> show functions; All functions defined in your database
splice> show functions in SYSCS_UTIL; All functions in the SYSCS_UTIL schema in your database
splice> show procedures; All stored procedures defined in your database
splice> show procedures in SYSCS_UTIL; All stored procedures in the SYSCS_UTIL schema in your database

Writing and Deploying Functions and Stored Procedures

The remainder of this section presents information about and examples of writing functions and stored procedures for use with Splice Machine, in these topics:

See Also