|
|
Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
This section looks at a very simple stored procedure that 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 usebegin . . . endor 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_NAMEThe 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
Connectionobjectconto create aStatementobject, 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_SUPPLIERSwill be compiled and stored in the database as a database object that can be called, similar to the way you would call a method.Calling a Stored Procedure from JDBC
JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a
CallableStatementobject. As withStatementandPreparedStatementobjects, this is done with an openConnectionobject. ACallableStatementobject contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedureSHOW_SUPPLIERSusing the connectioncon. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters"{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure namedSHOW_SUPPLIERS.CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery();The
ResultSetrswill be similar to the following:SUP_NAME COF_NAME ---------------- ----------------------- Acme, Inc. Colombian Acme, Inc. Colombian_Decaf Superior Coffee French_Roast Superior Coffee French_Roast_Decaf The High Ground EspressoNote that the method used to execute
csisexecuteQuerybecausecscalls a stored procedure that contains one query and thus produces one result set. If the procedure had contained one update or one DDL statement, the methodexecuteUpdatewould have been the one to use. It is sometimes the case, however, that a stored procedure contains more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the methodexecuteshould be used to execute theCallableStatement.The class
CallableStatementis a subclass ofPreparedStatement, so aCallableStatementobject can take input parameters just as aPreparedStatementobject can. In addition, aCallableStatementobject can take output parameters or parameters that are for both input and output. INOUT parameters and the methodexecuteare used rarely. For complete information, refer to JDBC Database Access with Java.![]()
![]()
![]()
Start of Tutorial > Start of Trail > Start of Lesson Search
Feedback FormCopyright 1995-2004 Sun Microsystems, Inc. All rights reserved.