Java stored procedure

A Java stored procedure is a procedure that is written in Java instead of 3GL languages like PL/SQL and stored in the Oracle database. They are executed by the JVM. For this, the database memory space is used. It is sometimes wrongly abbreviated as JSP.

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 the procedure is called, its name and any relevant parameters are sent over the JDBC connection to the DBMS, which executes the procedure and returns the results (if applicable) via the connection.

Stored procedures share many advantages with application servers based on EJBs or CORBA. The principal difference is that stored procedures are bundled free with many popular DBMSs, while application servers are frequently expensive, take time to administer and code for, and require more complex client software.

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. A simple reduction in network requests can dramatically improve 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 network lines.
• Modular programming. Code can be broken up into more 'digestible' pieces.
• Restricted, function-based access to tables. User access to tables can be restricted so that it is only possible via the stored procedure.
• Reduced operator error. Less information to pass.
• Enforced consistency. As tables may only be accessed through stored procedures, ad-hoc modifications are not permitted.
• Automated complex or sensitive transactions.

The main disadvantage to using stored procedures is that it can place important code outside of the reach of your source control system.

Stored procedure parameter properties

• Parameter names, like local variables, may be up to 29 characters in length, and 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 SQLl 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 (a "system stored procedure" is necessary if a stored procedure is to operate within the context of the database it is called from). Tables may be created in stored procedures: typically, temporary tables are used for storing intermediate results or as work tables; these temporary tables 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.

Stored procedures are reusable, but not reentrant. They can be recursive.

Stored procedures may reference objects in other databases and call other procedures to a nesting depth of 16.