If you create a PreparedStatement object containing a precompiled dynamic SQL statement, once the statement is compiled in the database, it effectively becomes a stored procedure that is retained in memory and attached to the data structure associated with your session. In deciding whether to maintain stored procedures in the database or to create PreparedStatement objects containing compiled SQL statements in your application, resource demands and database and application maintenance are important considerations:
Once a stored procedure is compiled, it is globally available across all connections. In contrast, a dynamic SQL statement in a PreparedStatement object needs to be compiled and deallocated in every session that uses it.
If your application accesses multiple databases, using stored procedures means that the same stored procedures need to be available on all target databases. This can create a database maintenance problem. If you use PreparedStatement objects for dynamic SQL statements, you avoid this problem.
If your application creates CallableStatement objects for invoking stored procedures, you can encapsulate SQL code and table references in the stored procedures. You can then modify the underlying database or SQL code without have to change the application.
