A Brief Introduction To Stored Procedure Execution Using Callable Statement

Stored procedures are favorable when we are overseeing diverse tables with complex circumstance and instead of sending different queries to the database, we can send anticipated that data would the stored CallableStatement and have the logic executed in the database server itself.

Allows first make a stored procedure in ORACLE database for our CallableStatement case programs with beneath SQL query.

 CREATE OR REPLACE PROCEDURE IAI.GET_EMPLYEE_ACIVE
(
         P_RECOREDSET OUT SYS_REFCUSOR
)
AS
BEGIN
   OPEN P_RECOREDSET FOR
    SELECT EMP_ID,EMP_NAME FROM EMPLOYEE WHERE ACTIVE_STATUS=1;
END;

CallableStatement Example : 
How about we compose a straightforward stored procedure to get active employee information into Employee table.

import java.sql.*;
import oracle.jdbc.*;
class Employee
{
  public static void main(String a[])
  {
    try
   {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","SYSTEM","SYSTEM");
        CallableStatement stmt=con.prepareCall("BEGIN GET_EMPLYEE_ACIVE(?); END;");
        stmt.registerOutParameter(1,oracleTypes.CURSOR);
        stmt.execute();
        ResultSet rs=((OracleCallableStatement)stmt).getCursor(1);
        while(rs.next())
         {
             System.out.println("Employee Id : "rs.getString("empId")+" Employee Name : "+rs.getString("empName"));
         }
    }
    catch(Exception e)
   {
     e.printStackTrace();
   }
}

}
 

We are perusing client contribution to be stored in Employee table. The main thing unique in relation to PreparedStatement is the making of CallableStatement through "{call insertEmployee(?,?,?,?,?,?)}" and setting OUT parameter with CallableStatement registerOutParameter() technique.

         We need to register the OUT parameter before executing the stored procedure. Once the stored methodology is executed, we can utilize CallableStatement getXXX() strategy to get the OUT object data. Notice that while registering the OUT parameter, we have to indicate the kind of OUT parameter through java.sql.Types.

Advantages of Callable Statement :

At the point when utilized accurately, stored procedures can deliver a major change in execution of Oracle databases. 

They are perfect for batch programs and complex business approval logic that need to interface with the database. 

The greatest advantages originate from the utilization of bundles which assemble together related capacities, procedures and information structures. 

Oracle stored procedures are additionally perfect for rearranging complex SQL proclamations, however be careful that there is an overhead in changing setting from SQL to PL/SQL, yet not with standing considering, they can in any case help execution. You would clearly need to test this however, to ensure the setting exchanging overheads won't exceed the execution upgrades picked up from utilizing PL/SQL