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.
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
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular