Executing stored procedures v42.7.3.2
A stored procedure is a module that's written in EDB’s SPL and stored in the database. A stored procedure can define input parameters to supply data to the procedure and output parameters to return data from the procedure. Stored procedures execute in 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's 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. The square brackets indicate optional parameters. They aren't 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.
Executing a simple stored procedure
The following shows a stored procedure that increases the salary of each employee by 10%. increaseSalary
expects no arguments from the caller and doesn't return any information:
CREATE OR REPLACE PROCEDURE increaseSalary IS BEGIN UPDATE emp SET sal = sal * 1.10; END;
The following shows 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 doesn't execute it. As Executing stored procedures with IN parameters shows, 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
) didn't expect any IN
parameters and didn't return any information to the caller (using OUT
parameters), so invoking the procedure is a matter of creating a CallableStatement
object and then calling that object’s execute()
method.
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.
The following creates the stored procedure in the EDB Postgres 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;
The following shows 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's 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:
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;
The following 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's 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 :")));
Register the JDBC type of each OUT
parameter before executing the CallableStatement
objects. 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
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 this 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.
This code 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;
The following code shows 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 empQuery(?,?,?,?,?,?)}"; 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's later replaced with data:
String commandText = "{call empQuery(?,?,?,?,?,?)}"; 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);
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.