CREATE PROCEDURE AS — Defines a stored procedure composed of a SQL query.
CREATE PROCEDURE procedure-name
[PARTITION ON TABLE table-name COLUMN column-name [PARAMETER position]]
[ALLOW role-name [,...]]
You must declare stored procedures as part of the schema to make them accessible at runtime. The CREATE PROCEDURE AS statement lets you create a procedure from a single SQL statement directly within the DDL statement. The SQL statement can contain question marks (?) as placeholders that are filled in at runtime with the arguments to the procedure call.
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.
When creating single-partitioned procedures, you can either specify the partitioning in a separate PARTITION PROCEDURE statement or you can include the PARTITION ON clause in the CREATE PROCEDURE statement. Creating and partitioning stored procedures in a single statement is recommended because there are certain cases where procedures with complex queries must be partitioned and cannot be compiled without the partitioning information. For example, queries that join two partitioned tables must be run in a single-partitioned procedure and must join the tables on their partitioning columns.
Partitioning a stored procedure means that the procedure executes within a unique partition of the database. The partition in which the procedure executes is chosen at runtime based on the table and column specified by table-name and column-name. By default, VoltDB uses the first parameter to the stored procedure as the partitioning value. However, you can use the PARAMETER clause to specify a different parameter. The position value specifies the parameter position, counting from zero. (In other words, position 0 is the first parameter, position 1 is the second, and so on.) The specified table must be a partitioned table or stream.
If security is enabled at runtime, only those roles named in the ALLOW clause (or with the ALLPROC or ADMIN permissions) 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.
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 operator role. It also partitions the stored procedure on the userID column of the Accounts table. Note that the PARAMETER clause is used since the userID is the second parameter to the procedure:
CREATE PROCEDURE ChangeUserPassword PARTITION ON TABLE Accounts COLUMN userID PARAMETER 1 ALLOW operator AS UPDATE Accounts SET HashedPassword=? WHERE userID=?;