CallableStatement
Interface
To call the stored procedures and
functions, CallableStatement interface is used.
We can have business logic on the database by
the use of stored procedures and functions that will make the performance
better because these are precompiled.
Suppose you need the get the age of the employee
based on the date of birth, you may create a function that receives date as the
input and returns age of the employee as the output.
What is the difference between stored
procedures and functions.
The differences between stored procedures and
functions are given below:
Stored Procedure
|
Function
|
is used
to perform business logic.
|
is used
to perform calculation.
|
must
not have the return type.
|
must
have the return type.
|
may
return 0 or more values.
|
may
return only one values.
|
We can
call functions from the procedure.
|
Procedure
cannot be called from function.
|
Procedure
supports input and output parameters.
|
Function
supports only input parameter.
|
Exception
handling using try/catch block can be used in stored procedures.
|
Exception
handling using try/catch can't be used in user defined functions.
|
How to get the instance of CallableStatement?
The prepareCall() method of Connection interface
returns the instance of CallableStatement. Syntax is given below:
1. public CallableStatement prepareCall("{ call procedurename(?,?...?)}");
The example to get the instance of
CallableStatement is given below:
1. CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");
It calls the procedure myprocedure that receives
2 arguments.
Full example to call the stored procedure
using JDBC
To call the stored procedure, you need to create
it in the database. Here, we are assuming that stored procedure looks like
this.
1. create or replace procedure "INSERTR"
2. (id IN NUMBER,
3. name IN VARCHAR2)
4. is
5. begin
6. insert into user420 values(id,name);
7. end;
8. /
The table structure is given below:
1. create table user420(id number(10), name varchar2(200));
In this example, we are going to call the stored
procedure INSERTR that receives id and name as the parameter and inserts it
into the table user420. Note that you need to create the user420 table as well
to run this application.
1. import java.sql.*;
2. public class Proc {
3. public static void main(String[] args) throws Exception{
4.
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6. Connection con=DriverManager.getConnection(
7. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
8.
9. CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");
10. stmt.setInt(1,1011);
11. stmt.setString(2,"Amit");
12. stmt.execute();
13.
14. System.out.println("success");
15. }
16. }
Now check the table in the database, value is
inserted in the user420 table.
Example to call the function using JDBC
In this example, we are calling the sum4
function that receives two input and returns the sum of the given number. Here,
we have used the registerOutParameter method of
CallableStatement interface, that registers the output parameter with its
corresponding type. It provides information to the CallableStatement about the
type of result being displayed.
The Types class defines many
constants such as INTEGER, VARCHAR, FLOAT, DOUBLE, BLOB, CLOB etc.
Let's create the simple function in the database
first.
1. create or replace function sum4
2. (n1 in number,n2 in number)
3. return number
4. is
5. temp number(8);
6. begin
7. temp :=n1+n2;
8. return temp;
9. end;
10. /
Now, let's write the simple program to call the
function.
1. import java.sql.*;
2.
3. public class FuncSum {
4. public static void main(String[] args) throws Exception{
5.
6. Class.forName("oracle.jdbc.driver.OracleDriver");
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. CallableStatement stmt=con.prepareCall("{?= call sum4(?,?)}");
11. stmt.setInt(2,10);
12. stmt.setInt(3,43);
13. stmt.registerOutParameter(1,Types.INTEGER);
14. stmt.execute();
15.
16. System.out.println(stmt.getInt(1));
17.
18. }
19. }
Output: 53
//How to create procedure in Oracle 10 g for insert
1.create table
create table tt(id int,name varchar(56))
2.create procedure for insert
create or replace procedure insertTT
(id IN int,name IN varchar)
is
begin
insert into tt values(id,name);
commit;
end;
3. how to execute on oracle
begin
insertTT(67,'Hari');
end;
//-----------------------For Update--------------//
create or replace procedure updateTT
(id1 IN int,name1 IN varchar)
is
begin
update tt set name=name1 where id=id1;
commit;
end;
//how to execute in oracle
begin
updateTT(345,'Krishan');
end;
///--------------For Delete--------------------//
create or replace procedure deleteTT
(id1 IN int)
is
begin
delete from tt where id=id1;
commit;
end;
//how to execute in oracle
begin
deleteTT(345);
end;
//-------------------For select-----------------//
create or replace procedure selectTT
(o_id OUT int,o_name OUT varchar,i_id IN int)
is
begin
select id,name into o_id,o_name from tt where id=i_id ;
commit;
end;
//how to execute in oracle
declare
o_id int;
o_name varchar(45);
begin
selectTT(o_id,o_name,79);
DBMS_OUTPUT.PUT_LINE('ID:'||o_id);
DBMS_OUTPUT.PUT_LINE('NAME:'||o_name);
end;
//output
ID:79
NAME:Prem
Program to call store procedure for select command
import java.sql.*;
class TestProc
{
public static void main(String gh[])throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
String url="jdbc:oracle:thin:@//localhost:1521/xe";
String user="system";
String pwd="1234";
Connection con=DriverManager.getConnection(url,user,pwd);
System.out.println(con);
int o_id=0;
String o_name=null;
CallableStatement cb=con.prepareCall("{call selectTT(?,?,?)}");
cb.registerOutParameter(1,java.sql.Types.INTEGER);
cb.registerOutParameter(2,java.sql.Types.VARCHAR);
cb.setInt(3,79);
//cb.setInt(2,79);
//cb.setString(2,"Prem");
cb.execute();
o_id=cb.getInt(1);
o_name=cb.getString(2);
System.out.println("Id="+o_id);
System.out.println("Name="+o_name);
}
}
No comments:
Post a Comment