Executing Stored Procedures¶
A stored procedure is a module that is written in EnterpriseDB’s SPL and stored in the database. A stored procedure may define input parameters to supply data to the procedure and output parameters to return data from the procedure. Stored procedures execute within the server and consist of database access commands (SQL), control statements, and data structures that manipulate the data obtained from the database.
Stored procedures are especially useful when extensive data manipulation is required before storing data from the client. It is also efficient to use a stored procedure to manipulate data in a batch program.
Invoking Stored Procedures¶
The CallableStatement class provides a way for a Java program to call
stored procedures. A CallableStatement object can have a variable number
of parameters used for input (IN parameters), output (OUT parameters),
or both (IN OUT parameters).
The syntax for invoking a stored procedure in JDBC is shown below. Note that the square brackets indicate optional parameters; they are not part of the command syntax.
{call procedure_name([?, ?, ...])}
The syntax to invoke a procedure that returns a result parameter is:
{? = call procedure_name([?, ?, ...])}
Each question mark serves as a placeholder for a parameter. The stored
procedure determines if the placeholders represent IN, OUT, or IN OUT
parameters and the Java code must match. We will show you how to supply
values for IN (or IN OUT) parameters and how to retrieve values returned
in OUT (or IN OUT) parameters in a moment.
Executing a Simple Stored Procedure¶
Listing 1.7-a shows a stored procedure that increases the salary of each
employee by 10%. increaseSalary expects no arguments from the caller and
does not return any information:
CREATE OR REPLACE PROCEDURE increaseSalary
IS
BEGIN
UPDATE emp SET sal = sal * 1.10;
END;
Listing 1.7-b demonstrates how to invoke the increaseSalary procedure:
public void SimpleCallSample(Connection con)
{
try
{
CallableStatement stmt = con.prepareCall("{call increaseSalary()}");
stmt.execute();
System.out.println("Stored Procedure executed successfully");
}
catch(Exception err)
{
System.out.println("An error has occurred.");
System.out.println("See full details below.");
err.printStackTrace();
}
}
To invoke a stored procedure from a Java application, use a
CallableStatement object. The CallableStatement class is derived from
the Statement class and, like the Statement class, you obtain a
CallableStatement object by asking a Connection object to create one for
you. To create a CallableStatement from a Connection, use the
prepareCall() method:
CallableStatement stmt = con.prepareCall("{call increaseSalary()}");
As the name implies, the prepareCall() method prepares the statement,
but does not execute it. As you will see in the next example, an
application typically binds parameter values between the call to
prepareCall() and the call to execute(). To invoke the stored procedure
on the server, call the execute() method.
stmt.execute();
This stored procedure (increaseSalary) did not expect any IN parameters
and did not return any information to the caller (using OUT parameters)
so invoking the procedure is simply a matter of creating a
CallableStatement object and then calling that object’s execute()
method.
The next section demonstrates how to invoke a stored procedure that
requires data (IN parameters) from the caller.
Executing Stored Procedures with IN parameters¶
The code in the next example first creates and then invokes a stored
procedure named empInsert; empInsert requires IN parameters that contain
employee information: empno, ename, job, sal, comm, deptno, and mgr.
empInsert then inserts that information into the emp table.
Listing 1.8-a creates the stored procedure in the Advanced Server database:
CREATE OR REPLACE PROCEDURE empInsert(
pEname IN VARCHAR,
pJob IN VARCHAR,
pSal IN FLOAT4,
pComm IN FLOAT4,
pDeptno IN INTEGER,
pMgr IN INTEGER
)
AS
DECLARE
CURSOR getMax IS SELECT MAX(empno) FROM emp;
max_empno INTEGER := 10;
BEGIN
OPEN getMax;
FETCH getMax INTO max_empno;
INSERT INTO emp(empno, ename, job, sal, comm, deptno, mgr)
VALUES(max_empno+1, pEname, pJob, pSal, pComm, pDeptno, pMgr);
CLOSE getMax;
END;
Listing 1.8-b demonstrates how to invoke the stored procedure from Java:
public void CallExample2(Connection con)
{
try
{
Console c = System.console();
String commandText = "{call empInsert(?,?,?,?,?,?)}";
CallableStatement stmt = con.prepareCall(commandText);
stmt.setObject(1, new String(c.readLine("Employee Name :")));
stmt.setObject(2, new String(c.readLine("Job :")));
stmt.setObject(3, new Float(c.readLine("Salary :")));
stmt.setObject(4, new Float(c.readLine("Commission :")));
stmt.setObject(5, new Integer(c.readLine("Department No :")));
stmt.setObject(6, new Integer(c.readLine("Manager")));
stmt.execute();
}
catch(Exception err)
{
System.out.println("An error has occurred.");
System.out.println("See full details below.");
err.printStackTrace();
}
}
Each placeholder (?) in the command (commandText) represents a point in
the command that is later replaced with data:
String commandText = "{call EMP_INSERT(?,?,?,?,?,?)}";
CallableStatement stmt = con.prepareCall(commandText);
The setObject() method binds a value to an IN or IN OUT placeholder.
Each call to setObject() specifies a parameter number and a value to
bind to that parameter:
stmt.setObject(1, new String(c.readLine("Employee Name :")));
stmt.setObject(2, new String(c.readLine("Job :")));
stmt.setObject(3, new Float(c.readLine("Salary :")));
stmt.setObject(4, new Float(c.readLine("Commission :")));
stmt.setObject(5, new Integer(c.readLine("Department No :")));
stmt.setObject(6, new Integer(c.readLine("Manager")));
After supplying a value for each placeholder, this method executes the
statement by calling the execute() method.
Executing Stored Procedures with OUT parameters¶
The next example creates and invokes an SPL stored procedure called
deptSelect. This procedure requires one IN parameter (department number)
and returns two OUT parameters (the department name and location)
corresponding to the department number. The code in Listing 1.9-a
creates the deptSelect procedure:
CREATE OR REPLACE PROCEDURE deptSelect
(
p_deptno IN INTEGER,
p_dname OUT VARCHAR,
p_loc OUT VARCHAR
)
AS
DECLARE
CURSOR deptCursor IS SELECT dname, loc FROM dept WHERE deptno=p_deptno;
BEGIN
OPEN deptCursor;
FETCH deptCursor INTO p_dname, p_loc;
CLOSE deptCursor;
END;
Listing 1.9-b shows the Java code required to invoke the deptSelect
stored procedure:
public void GetDeptInfo(Connection con)
{
try
{
Console c = System.console();
String commandText = "{call deptSelect(?,?,?)}";
CallableStatement stmt = con.prepareCall(commandText);
stmt.setObject(1, new Integer(c.readLine("Dept No :")));
stmt.registerOutParameter(2, Types.VARCHAR);
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.execute();
System.out.println("Dept Name: " + stmt.getString(2));
System.out.println("Location : " + stmt.getString(3));
}
catch(Exception err)
{
System.out.println("An error has occurred.");
System.out.println("See full details below.");
err.printStackTrace();
}
}
Each placeholder (?) in the command (commandText) represents a point in
the command that is later replaced with data:
String commandText = "{call deptSelect(?,?,?)}";
CallableStatement stmt = con.prepareCall(commandText);
The setObject() method binds a value to an IN or ``IN OUT placeholder.
When calling setObject() you must identify a placeholder (by its ordinal
number) and provide a value to substitute in place of that placeholder:
stmt.setObject(1, new Integer(c.readLine("Dept No :")));
The JDBC type of each OUT parameter must be registered before the
CallableStatement object can be executed. Registering the JDBC type is
done with the registerOutParameter() method.
stmt.registerOutParameter(2, Types.VARCHAR);
stmt.registerOutParameter(3, Types.VARCHAR);
After executing the statement, the CallableStatement’s getter method
retrieves the OUT parameter values: to retrieve a VARCHAR value, use the
getString() getter method.
stmt.execute();
System.out.println("Dept Name: " + stmt.getString(2));
System.out.println("Location : " + stmt.getString(3));
In the current example GetDeptInfo() registers two OUT parameters and
(after executing the stored procedure) retrieves the values returned in
the OUT parameters. Since both OUT parameters are defined as VARCHAR
values, GetDeptInfo() uses the getString() method to retrieve the OUT
parameters.
Executing Stored Procedures with IN OUT parameters¶
The code in the next example creates and invokes a stored procedure
named empQuery defined with one IN parameter (p_deptno), two IN OUT
parameters (p_empno and p_ename) and three OUT parameters (p_job,
p_hiredate and p_sal). empQuery then returns information about the
employee in the two IN OUT parameters and three OUT parameters.
Listing 1.10-a creates a stored procedure named empQuery:
CREATE OR REPLACE PROCEDURE empQuery
(
p_deptno IN NUMBER,
p_empno IN OUT NUMBER,
p_ename IN OUT VARCHAR2,
p_job OUT VARCHAR2,
p_hiredate OUT DATE,
p_sal OUT NUMBER
)
IS
BEGIN
SELECT empno, ename, job, hiredate, sal
INTO p_empno, p_ename, p_job, p_hiredate, p_sal
FROM emp
WHERE deptno = p_deptno
AND (empno = p_empno
OR ename = UPPER(p_ename));
END;
Listing 1.10-b demonstrates invoking the empQuery procedure, providing
values for the IN parameters, and handling the OUT and IN OUT
parameters:
public void CallSample4(Connection con)
{
try
{
Console c = System.console();
String commandText = "{call emp_query(?,?,?,?,?,?)}";
CallableStatement stmt = con.prepareCall(commandText);
stmt.setInt(1, new Integer(c.readLine("Department No:")));
stmt.setInt(2, new Integer(c.readLine("Employee No:")));
stmt.setString(3, new String(c.readLine("Employee Name:")));
stmt.registerOutParameter(2, Types.INTEGER);
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.registerOutParameter(4, Types.VARCHAR);
stmt.registerOutParameter(5, Types.TIMESTAMP);
stmt.registerOutParameter(6, Types.NUMERIC);
stmt.execute();
System.out.println("Employee No: " + stmt.getInt(2));
System.out.println("Employee Name: " + stmt.getString(3));
System.out.println("Job : " + stmt.getString(4));
System.out.println("Hiredate : " + stmt.getTimestamp(5));
System.out.println("Salary : " + stmt.getBigDecimal(6));
}
catch(Exception err)
{
System.out.println("An error has occurred.");
System.out.println("See full details below.");
err.printStackTrace();
}
}
Each placeholder (?) in the command (commandText) represents a point in
the command that is later replaced with data:
String commandText = "{call emp_query(?,?,?,?,?,?)}";
CallableStatement stmt = con.prepareCall(commandText);
The setInt() method is a type-specific setter method that binds an
Integer value to an IN or IN OUT placeholder. The call to setInt()
specifies a parameter number and provides a value to substitute in place
of that placeholder:
stmt.setInt(1, new Integer(c.readLine("Department No:")));
stmt.setInt(2, new Integer(c.readLine("Employee No:")));
The setString() method binds a String value to an IN or IN OUT
placeholder:
stmt.setString(3, new String(c.readLine("Employee Name:")));
Before executing the CallableStatement, you must register the JDBC type
of each OUT parameter by calling the registerOutParameter() method.
stmt.registerOutParameter(2, Types.INTEGER);
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.registerOutParameter(4, Types.VARCHAR);
stmt.registerOutParameter(5, Types.TIMESTAMP);
stmt.registerOutParameter(6, Types.NUMERIC);
Remember, before calling a procedure with an IN parameter, you must
assign a value to that parameter with a setter method. Before calling a
procedure with an OUT parameter, you register the type of that
parameter; then you can retrieve the value returned by calling a getter
method. When calling a procedure that defines an IN OUT parameter, you
must perform all three actions:
Assign a value to the parameter.
Register the type of the parameter.
Retrieve the value returned with a getter method.
