by Dinesh Thakur

CallableStatement of JDBC is derived from interface of PreparedStatement. In JDBC, CallableStatement is best statement because by using this statement we can execute SQL operation on database and we can also call stored procedure of database. The only difference between CallableStatement and PreparedStatement is that the latter is not useful to call a stored procedure but it is possible through the former.

CallableStatement in JDBC can be used for both executing the command on a database and also calling the stored procedure of database. So, we have two syntax to obtain CallableStatement.

 

Syntax-1

CallableStatement cs=cn.prepareCall("sql command");

 

Syntax-2

CallableStatement cs=con.prepareCall( {call procedurename(parameter) });

 

What is the difference between Procedure and Function in frontend programming?

• Procedure does not return any value.

Function returns only one value.

What is the difference between Procedure and Function in backend programming?

• Procedure can return zero or more value.

Function can return only one value.

Syntax for creating procedure in oracle database:

create or replace procedure Name(parameter)

is

declare variables;

begin

statement;

end;

• A procedure can contain any of three types of parameters

• IN parameter

• OUT parameter

• INOUT parameter

• By default the parameter is type IN.

IN: IN parameter is used to accept input values to a procedure.

OUT: Parameter is used to return output value from procedure.

INOUT: Parameter is used to take input value and also return output values.

• The following procedure is used for calculating experience of an employee.

Take Input as employee number and return output as employee name and is experienced.

The following is a JDBC program for calling the above procedure created in Oracle database server using Callable Statement of JDBC.

Step 1:Login to Oracle server

Step 2:SQL>ed demo

Step 3:Create procedure emp_experience

Syntax:

Create or replace procedure emp_experience(eno in number, empname out varchar2, experience out number)

is

doj date;

today date;

begin

Select sysdate into today from dual;

Select hiredate into doj from employee where empno=eno;

experience:=(today-doj)/365;

Select ename into empname from employee where empno=eno;

end;

Step 4: Save and exit

//compilation of procedure

Step 5:SQL>@demo

Step 6:Write Java code

import java.sql.*;

import java.util.*;

public class CallableTest1

{

       public static void main (String args [ ]) throws Exception

       {

               //load type-4 driver

               Class.forName("oracle.Jdbc.driver.OracleDriver");

               //get connecticin

               Connection cn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","manger");

               //create Callable Statement object

               CallableStatement cs=cn.callableStatement("insert into employee_info values(?,?,?) ");

               Scanner sc=new Scanner(System.in);

               System.out.println("Enter the employee no");

               int eid=sc.nextInt();

               //set id and name

               cs.setInt(1,eid);

               //registeter OUT parameter

               cs.registerOutParameter(2,Types.VARCHAR);

               cs.registerOutParameter(3, Types.INTEGER);

               //call the procedure

               cs.execute() ;

               //read empname

               String str=cs.getString(2);

               int k=cs.getInt(3);

               System.out.println("empno"+eid) ;

               System.out.println("emp narne"+str);

               System.out.println("empexperience"+k+"year") ;

               cs.close() ;

               cn.close() ;

      }

}

Output:

Enter employee number

7900

emp no 7900

emp name Dinesh

emp experience 31 years

In above example, while calling a procedure using CallableStatement:

• We register_out Parameter procedure call. The reason why OUTparameter is registered is that if you do not register then by default CallableStatement assumes all parameters as IN parameter.

• If the parameter is IN modeor IN type then Input values must be set before calling the procedure.

• If parameter is registered as OUT then CallableStatement object store the output values as returned by the OUT parameter.

• CallableStatement cannot understand Java Datatype or Database Datatype. So, we need to specify JDBC Type which acts as a mediator between java and database.

• Types.VARCHAR is mediator type between java String type and database varchar2.

• In JDBC all mediator data types are given in java.sql package.

                                                DATA TYPE

Java Type

JDBC Type

Database Type

Int

Types.INTEGER                     

Number

Double

Types.DOUBLE                      

Number

String

Types.VARCHAR                   

varchar2

Char

Types.CHAR                           

char(1)

InputStream  

Types.BLOB                            

Blob

In the following procedure empno is IN parameter and return Bonus is OUT parameter

SQL>ed demo2

Create or replace procedure emp_bonus(eno number,bonus out number)

is

temp number(9);

begin

select sal into temp from  emp where empno=eno;

if temp between  1 and 2000 then

bonus:=temp*0.15;

else if temp between 2001 and 5000 then

bonus:=temp*0.20;

else

bonus=temp*0.30;

end if;

end;

SQL>@demo2

procedure created

The following JDBC program is calling the above procedure using CallableStatement.

 

Example

 

import java.sql.*;

import java.util.*;

public class CallableTest2

{

              public static void main (String args [ ])throws Exception

              {

                      //load the type-4 driver

                      Class.forName("oracle.jdbc.driver.OracleDriver");

                      //get connection.

                      Connection cn=DriverManager.getConnection ("jdbc:orac1e:thin:@localhost:1521:XE","system","manager");

                      //create callableStatement object

                      CallableStatement cs=cn.prepareCall ("{call emp_bonus(?,?) }");

                      Scanner sc=new Scanner(System.in);

                      System.out.println("Enter the employee no");

                      int eno=sc.nextInt();

                      //set IN parameter

                      cs.setInt (1, eno);

                      //register out parameter

                      cs.registerOutParameter(2,Types.DOUBLE);

                      //call the procedure

                      cs.execute();

                      //read out parameter

                      double d=cs.getDouble (2);

                      System.out.println ("emp no"+eno);

                      System.out.println("empbonus"+d);

                      cs.close();

                      cn.close();

               }

}

 

Output;

Enter employee no

7900

Emp no 7900

Emp bonus 1000.0.

 

Syntax to Create Function in Oracle Database Server

Create or replace function functionname (parameter)

Return returntype is

declare variables;

begin

statements;

end;

 

Note

• By default parameter of function is IN and IN is not allowed to specify IN parameter.

• For a function return type is mandatory.

• Within the body of function statement is mandatory.

The following function is created for finding the sum of salaries of employees working in a given department, by accepting depno as input.

sql>ed demo3

create or replace function sum_dept(dno number)

return is

temp number(q)

begin

select sum(sql) into temp from employee where depno=dno

end;

sql>@demo3

Function created

 

• The following JDBC program is calling the above function using CallableStatement.

 

Example

 

import java.sql.*;

import java.util.*;

public class CallableTest3

{

               public static void main(String args[ ]) throws Exception

              {

                   //load the type-4 driver

                   Class.forName("oracle.jdbc.driver.OracleDriver");

                   //get connection

                   Connection cn=DriverManager.getConnection ("jdbc:oracle:thin:@locdlhost:1521:XE","system","manager");

                   //create CallableStatememt object

                   CallableStatement cs=cn.prepareCall ("{? =call sum_dept(?) }");

                   // register OUT parameter

                   cs.registerOutParameter(1,Types.INTEGER);

                   Scanner sc=new Scanner (System.in) ;

                   System.out.println("Enter deptno");

                   int dno=sc.nextInt ();

                   //set IN parameter

                   cs.setInt(2,dno) ;

                   //call the procedure

                   cs.execute ( ) ;

                   //read OUT parameter

                   int k=cs.getInt (1) ;

                   System.out.println("sal sum is " +k) ;

                   cs.close() ;

                   cn.close() ;

               }

}

 

Output:

Enter depno

30

Sum of sal=2000