Simply Basics: CallableStatement example

We can use callableStatement object to call a stored procedure. To use the callableStatement we need to know the name of the procedure and input/output parameters. Some procedures may not take any parameter.
Procedure for executing the stored procedure which takes parameters.

Create a procedure which takes in parameters and gives an output. Below is the procedure:

PROCEDURE procx(one IN number, two in number, three out number)
as begin
    three := one * two;
end procx;

To call the above procedure Procx, create a CallableStatement as shown in the below program.
```
callStmt = conn.prepareCall("{call procx(?,?,?)}");
```
As there are 3 parameters we need to use 3 question marks (?).
Identify the input parameters and set the values of the parameters. In this example the first 2 parameters are inputs.
```
// set input parameters
  callStmt.setInt(1,10);
  callStmt.setInt(2, 20);
```
Identify the output parameter and register the data type of the output parameter. In this example the third parameter is for output.
```
//register output types
  callStmt.registerOutParameter(3, Types.INTEGER);
```
Execute the statement.

package com.ram.app;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class CallableStatmentExample {
 public static void main(String[] args) throws Exception{
  Connection conn;
  CallableStatement callStmt;
  
  Class.forName("oracle.jdbc.driver.OracleDriver");
  conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr");
  
  callStmt = conn.prepareCall("{call procx(?,?,?)}");
  // set input parameters
  callStmt.setInt(1,10);
  callStmt.setInt(2, 20);

  //register output types
  callStmt.registerOutParameter(3, Types.INTEGER);
  callStmt.execute();
  
  System.out.println("Output = "+callStmt.getInt(3));
 }
}

Execute CallableStatmentExample.java file. Output is given below.

CallableStatement example

No comments:

Post a Comment