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:[39]
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 ...
Get Java Enterprise in a Nutshell, Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.