Stored Procedures and CallableStatement

Stored Procedures and CallableStatement

In this tutorial, we are going to discuss about the Stored Procedures and CallableStatement. Stored procedures and CallableStatement are both features provided by JDBC (Java Database Connectivity) for interacting with databases, particularly for executing SQL statements and invoking database procedures. However, they serve different purposes and have distinct characteristics.

In our programming if any code repeatedly required, then we can define that code inside a method and we can call that method multiple times based on our requirement. Hence method is the best reusable component in our programming.

Similarly in the database programming, if any group of sql statements is repeatedly required then we can define those SQL statements in a single group and we can call that group repeatedly based on our requirement.

This group of SQL statements that perform a particular task is nothing but Stored Procedure. Hence stored procedure is the best reusable component at database level. Hence Stored Procedure is a group of sql statements that performs a particular task.

These procedures stored in database permanently for future purpose and hence the name stored procedure. Usually stored procedures are created by Database Admin (DBA). Every database has its own language to create Stored Procedures.

Oracle has âž” PL/SQL
MySQL has âž” Stored Procedure Language
Microsoft SQL Server has âž” Transact SQL(TSQL)

Similar to methods stored procedure has its own parameters. Stored Procedure has 3 Types of parameters.

  1. IN parameters (to provide input values)
  2. OUT parameters (to collect output values)
  3. INOUT parameters (to provide input and to collect output)

E.g 1

Z:=X+Y;

X,Y are IN parameters and Z is OUT parameter

E.g 2

X:=X+X;

X is INOUT parameter

Syntax for creating Stored Procedure (Oracle)

create or replace procedure procedure1(X IN number, Y IN number,Z OUT number) as
BEGIN<
   z:=x+y;
END;

SQL and PL/SQL are not case-sensitive languages. We can use lower case and upper case also. After writing Stored Procedure, we have to compile for this we required to use “/” (forward slash).

/ âž” For compilation

While compiling if any errors occurs,then we can check these errors by using the following command

SQL> show errors;

Once we created Stored Procedure and compiled successfully,we have to register OUT parameter to hold result of stored procedure.

SQL> variable sum number; (declaring a variable)

We can execute with execute command as follows

SQL> execute procedure1(10,20,:sum);

SQL> print sum;

E.g 2

create or replace procedure procedure1(X IN number,Y OUT number) as
BEGIN
   Y:= x*x;
END;
/
SQL> variable square number;

SQL> execute procedure1(10,:square);

SQL> print square;

SQUARE
----------
100

E.g 3

Procedure To Print Employee Salary Based On Given Employee Number.

create or replace procedure procedure2(eno1 IN number,esal1 OUT number) as
BEGIN
   select esal into esal1 from employees where eno=eno1;
END;
/
SQL>variable salary number;

SQL>execute procedure2(100,:salary);

SQL>print salary;

Java Code for calling Stored Procedures

If we want to call stored procedure from java application, then we should go for CallableStatement. CallableStatement is an interface present in java.sql package and it is the child interface of PreparedStatement.

Stored Procedures and CallableStatement

Driver software vendor is responsible to provide implementation for CallableStatement interface. We can create CallableStatement object by using following method of Connection interface.

public CallableStatement prepareCall(String procedure_call) throws SQLException

E.g

CallableStatement cst = con.prepareCall("{call addProc(?,?,?)}");

Where

call âž” JDBC Keyword
addProc ➔Procedure Name
? ➔Parameters

Whenever JVM encounters this line, JVM will send call to database.Database engine will check whether the specified procedure is already available or not. If it is available then it returns CallableStatement object representing that procedure.

Mapping Java Types to database Types by using JDBC Types

Java related data types and database related data types are not same.Some mechanism must be required to convert java types to database types and database types to java types. This mechanism is nothing but “JDBC Types”, which are also known as “Bridge Types”.

E.g

Types.INTEGER, Types.FLOAT, Types.VARCHAR

Note

JDBC data types are defined as constants in “java.sql.Types” class.

Process to call Stored Procedure from java application by using CallableStatement

1. Make sure Stored procedure available in the database

create or replace procedure addProc(num1 IN number,num2 IN number,num3 OUT number) as
BEGIN
   num3 :=num1 + num2;
END;
/

Create a CallableStatement with the procedure call.

CallableStatement cst = con.prepareCall("{call addProc(?,?,?)}");

Provide values for every IN parameter by using corresponding setter methods.

cst.setInt(1, 100);
cst.setInt(2, 200);

Here 1 and 2 are indexes. 100 and 200 are values.

4. Register every OUT parameter with JDBC Types.

If stored procedure has OUT parameter then to hold that output value we should register every OUT parameter by using the following method.

public void registerOutParameter (int index, int jdbcType)

E.g

cst.registerOutParameter(3,Types.INTEGER);

Note

Before executing procedure call, all input parameters should set with values and every OUT parameter we have to register with jdbc type.

5. execute procedure call

cst.execute();

Get the result from OUT parameter by using the corresponding getXxx() method.

E.g

int result=cst.getInt(3);

E.g

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class PreparedStatementTest { 
   public static void main(String[] args) throws Exception {
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","ashok","ashok"); 
      CallableStatement cst = con.prepareCall("{call addProc(?,?,?)}");
      cst.setInt(1,100);
      cst.setInt(2,200);
      cst.registerOutParameter(3,Types.INTEGER);
      cst.execute();     
      System.out.println("Result.."+cst.getInt(3));

      con.close();
   }
}

E.g 2

create or replace procedure getSal(id IN number, sal OUT number) as
BEGIN
   select esal into sal from emp where eno=id;
END;
/
import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class PreparedStatementTest { 
   public static void main(String[] args) throws Exception {
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","ashok","ashok"); 
      CalCallableStatement cst=con.prepareCall("{call getSal(?,?)}");
      cst.setInt(1,100);
      cst.registerOutParameter(2,Types.FLOAT);
      cst.execute();     
      System.out.println("Result.."+cst.getInt(2));

      con.close();
   }
}

That’s all about the Stored Procedures and CallableStatement. If you have any queries or feedback, please write us email at contact@waytoeasylearn.com. Enjoy learning, Enjoy Java.!!

Stored Procedures and CallableStatement
Scroll to top