In general, we are performing the database operations by using ordinary SQL statements.
When we want to execute n number of SQL statements through java program, the java environment is executing those queries one at a time which leads to lack of performance to a jdbc application.
In order to improve the performance of jdbc application, it is recommended to write all n number of SQL statements in a single program (in case of oracle it is called PL/SQL program) and that program will execute at a time irrespective of number of SQL statements which improves the performance of a java application.
A program which contains n number of SQL statements and residing a database environment is known as stored procedure. Stored procedures are divided into two types, they are procedure and function.
A procedure is one which contains block of statements which will return either zero or more than one value.
Syntax for creating a procedure:
create or replace procedure <procedure name> (parameters if any)
as/is local variables; begin block of statements; end; /
In order to call a procedure from java environment we must call on the name of procedure.
For example:
create or replace procedure proc1 as i out number; a out number; b number; c number; x in out number; begin i:=40+42; b:=10; c:=20; a:=b+c; x:=x+b+c; end; /
Create an oracle procedure which takes two input numbers and it must return sum of two numbers, multiplication and subtraction ?
Answer:
create or replace procedure proc2 (a in number, b number, n out number, n2 out number, n3 out number) as begin n1:=a+b; n2:=a*b; n3:=a-b; end; /
A function is one which contains n number of block of statements to perform some operation and it returns a single value only.
Syntax for creating a function:
create or replace function (a in number, b in number) return <return type> as n1 out number; begin n1:=a+b; return (n1); end; /
In order to execute the stored procedures from jdbc we must follow the following steps:
1. Create an object of CallableStatement by using the following method:
Here, String represents a call for calling a stored procedure from database environment.
2. Prepare a call either for a function or for a procedure which is residing in database.
Syntax for calling a function:
"{? = call <name of the function> (?,?,??.)}"
For example:
CallableStatement cs=con.prepareCall ("{? = call fun1 (?,?)}");
The positional parameters numbering will always from left to right starting from 1. In the above example the positional parameter-1 represents out parameter and the positional parameter-2 and parameter-3 represents in parameters.
Syntax for calling a procedure:
"{call <name of the procedure> (?,?,...)}"
For example:
CallableStatement cs=con.prepareCall ("{call fun1 (?,?,?,?,?)}");
3. Specify which input parameters are by using the following generalized method:
Public void setXXX (int, XXX);
For example:
cs.setInt (2, 10); cs.setInt (3, 20);
4. Specify which output parameters are by using the following generalized method:
In jdbc we have a predefined class called java.sql.Types which contains various data types of jdbc which are equivalent to database data types.
Java | Jdbc | Database |
---|---|---|
int | INTEGER | number |
String | VARCHAR | varchar2 |
Short | TINY INTEGER | number |
Byte | SMALL INTEGER | number |
All the data members which are available in Types class are belongs to public static final data members.
For example:
cs.registerOutParameter (1, Types.INTEGER);
5. Execute the stored procedure by using the following method:
For example:
cs.execute ();
6. Get the values of out parameters by using the following method:
public XXX getXXX (int);
Here, int represents position of out parameter. XXX represents fundamental data type or string or date.
For example:
int x=cs.getInt (1); System.out.println (x);
Write a java program which illustrates the concept of function?
Answer:
StuFun:
create or replace function StuFun (a in number, b in number, n1 out number) return number as n2 number; begin n1:=a*b; n2:=a+b; return (n2); end; /