Stored Procedures

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;
/