A Java stored procedure is a procedure that is written in Java

Java (programming language)

Java is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...

 instead of 3GL

Third-generation programming language

A third-generation programming language is a refinement of a second-generation programming language. The second generation of programming languages brought logical structure to software. The third generation brought refinements to make the languages more programmer-friendly...

 languages like PL/SQL

PL/SQL

PL/SQL is Oracle Corporation's procedural extension language for SQL and the Oracle relational database...

 and stored in the Oracle database. They are executed by the JVM

Java Virtual Machine

A Java virtual machine is a virtual machine capable of executing Java bytecode. It is the code execution component of the Java software platform. Sun Microsystems stated that there are over 4.5 billion JVM-enabled devices.-Overview:...

. For this, the database memory space is used. It is sometimes wrongly abbreviated as JSP.

A stored procedure

Stored procedure

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...

 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 parameter

Parameter

Parameter from Ancient Greek παρά also “para” meaning “beside, subsidiary” and μέτρον also “metron” meaning “measure”, can be interpreted in mathematics, logic, linguistics, environmental science and other disciplines....

s are sent over the JDBC

Java Database Connectivity

Java DataBase Connectivity, commonly referred to as JDBC, is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases...

 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

Çorba

Chorba , ciorbă , shurpa , shorpo , or sorpa is one of various kinds of soup or stew found in national cuisines across Middle East...

. 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.