Executing stored procedures v42.7.3.1

Suggest edits

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 OUTparameters (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 OUTparameters:

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.

On this page
Invoking stored procedures
Executing stored procedures with IN OUT parameters

← Prev

Using PreparedStatements to send SQL commands

↑ Up

Advanced JDBC Connector functionality

Using REF CURSORS with Java

Executing stored procedures v42.7.3.1

Invoking stored procedures

Executing a simple stored procedure

Executing stored procedures with IN parameters

Executing stored procedures with OUT parameters

Executing stored procedures with IN OUT parameters

← Prev

↑ Up

Next →