Sometimes all you want is to execute a single SQL query and return the results to the calling application. In these simple cases, writing the necessary Java code to create a stored procedure can be tedious, so VoltDB provides a shortcut. For very simple stored procedures that execute a single SQL query and return the results, you can define the entire stored procedure as part of the database schema.
Recall from Section 5.3.2, “Declaring Stored Procedures in the Schema”, that normally you use the CREATE PROCEDURE statement to specify the class name of the Java procedure you coded, for example:
CREATE PROCEDURE FROM CLASS MakeReservation; CREATE PROCEDURE FROM CLASS CancelReservation;
However, to create procedures without writing any Java, you can simply insert a SQL query in the AS clause:
CREATE PROCEDURE CountReservations AS SELECT COUNT(*) FROM RESERVATION;
VoltDB creates the procedure when you include the SQL query in the CREATE PROCEDURE AS statement. Note that you must specify a unique class name for the procedure, which is unique among all stored procedures, including both those declared in the schema and those created as Java classes. (You can use the sqlcmd command show procedures to display a list of all stored procedures.)
It is also possible to pass arguments to the SQL query in simple stored procedures. If you use the question mark
placeholder in the SQL, any additional arguments you pass in client applications through the
callProcedure() method are used to replace the placeholders, in their respective order. For example, the
following simple stored procedure expects to receive three additional parameters:
CREATE PROCEDURE MyReservationsByTrip AS SELECT R.RESERVEID, F.FLIGHTID, F.DEPARTTIME FROM RESERVATION AS R, FLIGHT AS F WHERE R.CUSTOMERID = ? AND R.FLIGHTID = F.FLIGHTID AND F.ORIGIN=? AND F.DESTINATION=?;
You can also specify whether the simple procedure is single-partitioned or not. By default, stored procedures are assumed to be multi-partitioned. But if your procedure should be single-partitioned, specify its partitioning in the PARTITION ON clause. In the following example, the stored procedure is partitioned on the FLIGHTID column of the RESERVATION table using the first parameter as the partitioning key.
CREATE PROCEDURE FetchReservations PARTITION ON TABLE Reservation COLUMN flightid AS SELECT * FROM RESERVATION WHERE FLIGHTID=?;
Finally, if you want to execute multiple SQL statements within a simple procedure, you must enclose the SQL in a BEGIN-END clause. For example, the following CREATE PROCEDURE AS statement fetches separate records from the CUSTOMER and ORDER tables:
CREATE PROCEDURE OpenOrders AS BEGIN SELECT fullname FROM CUSTOMER WHERE CUSTOMERID=?; SELECT * FROM ORDER WHERE CUSTOMERID=?; END;
Some important points to note concerning multi-statement simple procedures:
The END statement and all of the enclosed SQL statements, must be terminated with a semi-colon.
The procedure returns an array of VoltTables, one for each statement in the procedure.
Each placeholder represents one parameter to the stored procedure. Parameters cannot be reused. So in the previous example, the customer ID would need to be entered twice as separate parameters to the stored procedure, one parameter for the first statement and one parameter for the second statement.