Stored Procedures (Java Enterprise in a Nutshell)

2.11. Stored Procedures

Most RDBMS systems include some sort of internal programming language (e.g., Oracle's PL/SQL). These languages allow database developers to embed procedural application code directly within the database and then call that code from other applications. The advantage of this approach is that the code can be written just once and then used in multiple different applications (even with different platforms and languages). It also allows application code to be divorced from the underlying table structure. If stored procedures handle all of the SQL, and applications just call the procedures, only the stored procedures need to be modified if the table structure is changed later on.

Here is an Oracle PL/SQL stored procedure:[4]

[4] If it looks familiar, that's because it is from George Reese's Database Programming with JDBC (O'Reilly).

CREATE OR REPLACE PROCEDURE sp_interest
(id IN INTEGER
bal IN OUT FLOAT) IS
BEGIN
SELECT balance
INTO bal
FROM accounts
WHERE account_id = id;

bal := bal + bal * 0.03;

UPDATE accounts
SET balance = bal
WHERE account_id = id;

END;

This PL/SQL procedure takes two input values, an account ID and a balance, and returns an updated balance.

The CallableStatement interface is the JDBC object that supports stored procedures. The Connection class has a prepareCall() method that is very similar to the prepareStatement() method we used to create a PreparedStatement. Because each database has its own syntax for accessing stored procedures, JDBC defines a standardized escape syntax for accessing stored procedures with CallableStatement. The syntax for a stored procedure that does not return a result set is:

{call procedure_name[(?[,?...])]}

The syntax for a stored procedure that returns a result is:

{? = call procedure_name[(?[,?...])]}

In this syntax, each question mark (?) represents a placeholder for a procedure parameter or a return value. Note that the parameters are optional. The JDBC driver is responsible for translating the escape syntax into the database's own stored procedure syntax.

Here's a code fragment that uses CallableStatement to run the sp_interest stored procedure:

CallableStatment cstmt = con.prepareCall("{call sp_interest(?,?)}");
cstmt.registerOutParameter(2, Types.FLOAT);
cstmt.setInt(1, accountID);
cstmt.setFloat(2, 2343.23);
cstmt.execute();
out.println("New Balance:" + cstmt.getFloat(2));

In this example, we first create a CallableStatement using the prepareCall() method and passing in the appropriate escape syntax for the stored procedure. Since this stored procedure has an output parameter (actually, in this case, an INOUT parameter, which means it also serves as an input parameter), we use the registerOutParameter() method to identify that parameter as an output of type FLOAT. Note that just as with prepared statements, substituted parameters are numbered from 1 to n, left to right. Any time you have an output parameter in a stored procedure, you need to register its type using registerOutParameter() before you execute the stored procedure.

Next we set the two input parameters, the account ID and the balance, using the appropriate setXXX() methods. Finally, we execute the stored procedure and then use the getFloat() method to display the new balance. The getXXX() methods of CallableStatement are similar to those of the ResultSet.

You need to use CallableStatement only with stored procedures that have output values, such as the one we just saw. You can use either of the other statement objects to execute stored procedures that take parameters but don't return anything.


2.10. Transactions		2.12. Escape Sequences