CREATE PROCEDURE AS

Documentation

VoltDB Home » Documentation » Using VoltDB
< previoustable of contentsnext >

CREATE PROCEDURE AS

CREATE PROCEDURE AS — Defines a stored procedure composed of one or more SQL statements.

Synopsis

CREATE PROCEDURE procedure-name
[PARTITION ON TABLE table-name COLUMN column-name [PARAMETER position]]
[ALLOW role-name [,...]]
AS {sql-statement; | multi-statement-procedure}

CREATE PROCEDURE procedure-name
DIRECTED
[ALLOW role-name [,...]]
AS {sql-statement; | multi-statement-procedure}

multi-statement-procedure:
BEGIN
  sql-statement; [,...]
END;

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 ways to define a procedure as part of the CREATE PROCEDURE AS statement:

  • A single statement procedure where the CREATE PROCEDURE AS statement is followed by one SQL statement terminated by a semi-colon.

  • A multi-statement procedure where the CREATE PROCEDURE AS statement is followed by multiple SQL statements enclosed in a BEGIN-END clause.

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.

You can create three types of stored procedures:

  • Multi-Partition Procedures — By default, the CREATE PROCEDURE statement declares a multi-partition procedure. A multi-partition procedure runs as a single transaction and has access to data from the entire database. However, it also means that the procedure will access all of the partitions at once, blocking the transaction queues until the procedure is done.

  • Single-Partition Procedures — If you include the PARTITION ON clause, the procedure is partitioned and runs on only one partition of the database. The partition it runs on is determined by the value of one of the parameters you pass to the procedure at runtime, as described below.

  • Directed Procedures — if you include the DIRECTED clause, the procedure is a directed procedure and will run separate transactions on each of the partitions. However, the individual transactions are not coordinated. Directed procedures must be invoked as a scheduled task or using the callAllPartitionProcedure method. See Section 7.5, “Directed Procedures: Distributing Transactions to Every Partition” and the description of the CREATE TASK statement for more information on directed procedures.

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;
< previoustable of contentsnext >