Creating a Stored Procedure in Jdbc

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.

Note: Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities.

This simple stored procedure has no parameters. Even though most stored procedures do something more complex than this example, it serves to illustrate some basic points about them. As previously stated, the syntax for defining a stored procedure is different for each DBMS. For example, some use begin . . . end , or other keywords to indicate the beginning and ending of the procedure definition. In some DBMSs, the following SQL statement creates a stored procedure:

create procedure SHOW_SUPPLIERS
as
select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
from SUPPLIERS, COFFEES
where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
order by SUP_NAME

The following code puts the SQL statement into a string and assigns it to the variable createProcedure, which we will use later:

String createProcedure = “create procedure SHOW_SUPPLIERS ” +
“as ” +
“select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME ” +
“from SUPPLIERS, COFFEES ” +
“where SUPPLIERS.SUP_ID = COFFEES.SUP_ID ” +
“order by SUP_NAME”;

The following code fragment uses the Connection object con to create a Statement object, which is used to send the SQL statement creating the stored procedure to the database:

Statement stmt = con.createStatement();
stmt.executeUpdate(createProcedure);

The procedure SHOW_SUPPLIERS is compiled and stored in the database as a database object that can be called, similar to the way you would call a method.