Oct 27, 2014

Internals of Stored Procedure - JDBC


This post is very useful to know about internals of Stored Procedure. JDBC is used for database operations in java and Stored procedures are used for eliminating overhead from Java. Internals of Stored-Procedure will cover all the concepts like What is Stored Procedure, What is procedure, When do people prefer Stored Procedure, Advantages and uses of Stored Procedure, Disadvantage or flaws of Stored Procedures, API of Stored Procedure, Types of Procedure etc…
Before knowing about Stored Procedure we should know about What is Procedure? Procedure is nothing, It is a only function or method of database. These database methods contains group of SQL statements. We create procedure using PL/SQL programming.
Stored Procedure is nothing but it is one of the most useful mechanism for creating procedure in the database and called by java program is known as Stored Procedure.
There is a one big question that When do we prefer using Stored-Procedure? The answer of this question is that whenever we want to get allowance, gross salary etc. from database, we don’t need to create any logic to calculate allowance, gross salary in the java program. We will just create a separate procedure for calculating the allowance and gross salary and will get the desired result in our java program.

Advantage of Stored Procedure-

     Using stored Procedure the processing of data at the back-end database would be faster.
   Overhead on the java program can be reduce.
Disadvantage of Stored Procedure-
  Each and every database contains its own syntax of creating procedure. So if we want to change the database it will create problems due to changed syntax of each database. In this case we will need to write procedures again.

Note- Stored Procedure are not supported in M.S. Access (A Microsoft’s Product).
java.sql.CallableStatement interface is the API of Stored Procedure in Java.

Types of Stored-Procedure’s

There are 4 types of procedures in java.

1. Zero Parameterized Procedure
2. In-Procedure
3. Out-Procedure
4. In-Out Procedure

Zero Parameterized Procedure:

Those procedure which doesn’t contains any parameter is called Zero Parameterized Procedure.

SQL> create or replace

2 procedure prc1

3 Is

4 Begin

5 Update abc1 set name='VIKAS' where id=101;

6 End prc1;

7 /

Procedure created.


1.It is just like a method of java which is parameterized but void return type.
2. like pass the 'idno' or any conditional data as a parameter..

SQL> r/

1 create or replace

2 procedure prc2(aa IN number)

3 Is

4 Begin

5 Update abc1 set name='VIKAS' where id=aa;

6* End prc2;

Procedure created.


It is just a method of java which is contains zero parameter return.... like max, min,
count, sum etc.

SQL> create or replace

2 procedure prc3(abc OUT number)

3 Is

4 Begin

5 Select count(id) into abc from abc1;

6 End prc3;

7 /

Procedure created

In-Out Procedure:

It is just a method of java which is parameterized as well as return type .... like max,
min, count etc.

SQL> create or replace

2 procedure prc4(x IN number,y OUT number)

3 Is

4 Begin

5 Select max(id) into y from abc1 where id>=x;

6 End prc4;

7 /

Procedure created.

Creating CallableStatement Object:

Following code is for oracle database-





FROM EmpMaster



Note: This post is covering all of the fundamentals of stored procedures and related topics. For more details on other related topics, please visit blog-archive.

Thank You