JAVA DATABASE PROGRAMMING - Introduction to Java Programming: Comprehensive Version - page 1200

Note
The syntax of stored procedures is vendor specific. We use both Oracle and MySQL for
demonstrations of stored procedures in this topic.
A CallableStatement object can be created using the prepareCall(String
call) method in the Connection interface. For example, the following code cre-
ates a CallableStatement cstmt on Connection connection for the procedure
sampleProcedure .
CallableStatement callableStatement = connection.prepareCall(
"{call sampleProcedure(?, ?, ?)}" );
{call sampleProcedure(?, ?, ...)} is referred to as the SQL escape syntax , which
signals the driver that the code within it should be handled differently. The driver parses
the escape syntax and translates it into code that the database understands. In this example,
sampleProcedure is an Oracle procedure. The call is translated to the string begin
sampleProcedure(?, ?, ?); end and passed to an Oracle database for execution.
You can call procedures as well as functions. The syntax to create an SQL callable state-
ment for a function is:
{? = call functionName(?, ?, ...)}
CallableStatement inherits PreparedStatement . Additionally, the
CallableStatement interface provides methods for registering the OUT parameters and
for getting values from the OUT parameters.
Before calling an SQL procedure, you need to use appropriate setter methods to pass values
to IN and IN OUT parameters, and use registerOutParameter to register OUT and IN OUT
parameters. For example, before calling procedure sampleProcedure , the following state-
ments pass values to parameters p1 ( IN ) and p3 ( IN OUT ) and register parameters p2 ( OUT )
and p3 ( IN OUT ):
callableStatement.setString( 1 , "Dallas" ); // Set Dallas to p1
callableStatement.setLong( 3 , 1 ); // Set 1 to p3
// Register OUT parameters
callableStatement.registerOutParameter( 2 , java.sql.Types.DOUBLE);
callableStatement.registerOutParameter( 3 , java.sql.Types.INTEGER);
You can use execute() or executeUpdate() to execute the procedure depending on the
type of SQL statement, then use getter methods to retrieve values from the OUT parameters.
For example, the next statements retrieve the values from parameters p2 and p3 .
double d = callableStatement.getDouble( 2 );
int i = callableStatement.getInt( 3 );
Let us define a MySQL function that returns the number of the records in the table that match
the specified firstName and lastName in the Student table.
/* For the callable statement example. Use MySQL version 5 */
drop function if exists studentFound;
delimiter //
create function studentFound(first varchar ( 20 ), last varchar ( 20 ))
returns int
begin
declare result int ;
select count(*) into result