CREATE PROCEDURE AS

Documentation

Home » Documentation » Using VoltDB

CREATE PROCEDURE AS

CREATE PROCEDURE AS — Defines a stored procedure composed of a SQL query.

Synopsis

CREATE PROCEDURE procedure-name [ALLOW role-name [,...]] AS sql-statement

CREATE PROCEDURE procedure-name [ALLOW role-name [,...]] AS ### source-code ### LANGUAGE GROOVY

Description

You must declare stored procedures as part of the schema to make them accessible at runtime. The declared procedures are evaluated and included in the application catalog when you compile the database schema.

Use CREATE PROCEDURE AS when declaring stored procedures directly within the schema definition. There are two forms of the CREATE PROCEDURE AS statement:

  • The SQL query form supports a single SQL query statement in the AS clause. The SQL statement can contain question marks (?) as placeholders that are filled in at runtime with the arguments to the procedure call.

  • The embedded program code form supports the inclusion of program code in the AS clause. The embedded program code is opened and closed by three pound signs (###) and followed by the LANGUAGE clause specifying the programming language in use. VoltDB currently supports Groovy as an embedded language.

In both cases, the procedure name must follow the naming conventions for Java class names. For example, the name is case-sensitive and cannot contain any white space.

If security is enabled at runtime, only those roles named in the ALLOW clause have permission to invoke the procedure. If security is not enabled at runtime, the ALLOW clause is ignored and all users have access to the stored procedure.

Examples

The following example defines a stored procedure, CountUsersByCountry, as a single SQL query with a placeholder for matching the country column:

CREATE PROCEDURE CountUsersByCountry AS
    SELECT COUNT(*) FROM Users WHERE country=?;

The next example restricts access to the stored procedure to only users with the admin role:

CREATE PROCEDURE ChangeAdminPassword ALLOW admin AS
    UPDATE Accounts SET (HashedPassword=?) WHERE userID='root';