Java Programming with Oracle JDBC

Chapter 13. Callable Statements

CallableStatement objects are used to call stored procedures. The stored procedures themselves can be written using PL/SQL or Java. If they are written in Java, they must be published to the RDBMS by creating a SQL call specification. You can see examples of this in Chapter 5 . Stored procedures exist to perform data-intensive operations that cannot be accomplished using just SQL, and they perform these operations inside the database where network performance is a moot issue.

For example, let’s say you need to access five different tables in order to perform a complex calculation and need to store the result in a sixth table. Let’s further assume that the calculation is complex enough that it cannot be performed using just SQL. If you perform the work on a client, then the client will have to retrieve all the data necessary to perform the calculation and send the result back to the database. If the number of rows that need to be retrieved by the client is large, this network transfer of data could consume an inordinate amount of elapsed time. However, if you perform the calculation as a stored procedure, the elapsed time of transmitting data across the network will be eliminated, and the resulting calculation will be much quicker. This example represents the type of situation in which stored procedures excel.

As with all good things, stored procedures are sometimes taken to an extreme and are sometimes used as a panacea. For example, some developers ...