Java stored procedure

From Wikipedia, the free encyclopedia

This article may require cleanup to meet Wikipedia's quality standards. Please improve this article if you can. (September 2007)

A Java stored procedure is a set of SQL statements, written in the Java programming language, grouped together as an executable unit.

A stored procedure is a program that is kept and executed within a database server. The procedure is called from a Java class using a special syntax. When you call it, the name of the procedure and the parameters you specify are sent over the JDBC connection to the DBMS, which executes the procedure and returns the results (if any) back over the connection.

Using stored procedures has a lot of the same advantages as using application servers based on EJBs or CORBA. The difference is that stored procedures come free with lots of popular DBMSs, while application servers are mostly expensive. This isn't just an issue of license cost. The time it takes to administer and write code for app servers, and the increased complexity of the client applications that rely on them, can be almost wholly replaced by a reliance on your DBMS.

Since stored procedures run in the DBMS itself, they can help to reduce latency in applications. Rather than executing four or five SQL statements in a Java application, executing one stored procedure performs all the operations on the server side. Reducing the number of network trips alone can have a dramatic effect on performance.

The following list summarizes the advantages of stored procedures:

• Faster execution. Stored procedures, after their first execution, become memory-resident and do not need to be reparsed, reoptimized, or recompiled.

• Reduced network traffic. Less SQL needs to cross busy network lines.

• Modular programming. You now have a way of breaking up things into digestible pieces.

• Restricted, function-based access to tables. You can grant permissions in such a way as to allow a user access to tables only through the stored procedure.

• Reduced operator error. Less information to pass.

• Enforced consistency. If users are accessing tables only through your stored procedures, ad-hoc modifications go away.

• Automated complex or sensitive transactions.

Procedures can be renamed by using sp_rename. To modify a stored procedure, drop the procedure and re-create it; a stored procedure must be dropped before it can be re-created with the same name by the same user.

Here’s some information about stored procedure parameters:

• Parameter names, like local variables, may be up to 29 characters in length, and they follow SQL Server naming guidelines.

• Up to 255 parameters may be defined.

• Wildcards can be contained in values passed to stored procedures if the parameter is used in a like clause.

• Parameter datatypes can be either system datatypes or user-defined datatypes.

• Rules, defaults, and column properties do not apply to parameters defined with user-defined datatypes.

• Microsoft SQL Server can use text and image datatypes as read-only stored procedure parameters.

Procedure Limitations and Notes A stored procedure may not create views, defaults, rules, triggers, or procedures or issue the use statement. (If you want a stored procedure to operate within the context of the database it is called from, create a system stored procedure.) You can create tables in stored procedures. Typically, you create temporary tables for storing intermediate results or as work tables. Temporary tables used within stored procedures are dropped at procedure termination. A table cannot be created, dropped, and re-created with the same name in a single procedure. Stored procedures are parsed in a single pass and will not resolve forward or backward references. For example, when defining a stored procedure that references a temporary table, either the stored procedure must create the temporary table prior to referencing it, or the temporary table must exist at the time the stored procedure is created. Procedures are reusable, but not reentrant. Stored procedures can be recursive. Stored procedures may reference objects in other databases and call other procedures to a nesting level of 16 deep.

Here is when to use with recompile:

• When a stored procedure can generate widely different query plans depending on the parameters passed in and there is no way of predicting the best query plan for all executions

• When statistics have been updated on a table and you want the stored procedure to generate a new query plan based on the updated statistics

• When an index has been added to a table that you want the optimizer to consider to generate a new query plan for the stored procedure