Java Enterprise in a Nutshell, Second Edition

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:^[7]

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 ...