Description

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 one or more SQL statements directly within the DDL statement. The SQL statements can contain question marks (?) as placeholders that are filled in at runtime with the arguments to the procedure call.

There are two forms of the CREATE PROCEDURE AS statement:

For a single statement, the stored procedure returns the results of the query as a VoltTable. For multi-statement procedures, the results are returned as an array of VoltTable structures, one for each statement.

For all CREATE PROCEDURE AS statements, 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 specify the partitioning in the PARTITION ON clause. 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.

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 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=?;

The last example uses a BEGIN-END clause to include four SQL statements in the procedure. In this case, the procedure performs two INSERT INTO SELECT statements, a DELETE statement and then selects the total count of records after the operation. The stored procedure returns four VoltTables, one for each statement, with the last one containing the final record count since SELECT is the last statement in the procedure.

CREATE PROCEDURE MoveOrders 
  AS BEGIN 
    INSERT INTO enroute SELECT * FROM Orders 
        WHERE ship_date < NOW() AND delivery_date > NOW();
    INSERT INTO history SELECT * FROM enroute 
        WHERE delivery_date < NOW();
    DELETE FROM enroute 
        WHERE delivery_date < NOW();
    SELECT COUNT(*) FROM enroute;
  END;