STORED PROCEDURE
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-
1 Using stored Procedure the processing of
data at the back-end database would be faster.
2 Overhead on the java program can be reduce.
Disadvantage of Stored
Procedure-
1 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.
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.
Example-
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.
In-Procedure:
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..
Example-
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.
Out-Procedure:
It is just a method of java which is contains zero parameter return.... like max, min,count, sum etc.
Example-
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.
Example-
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-CREATE OR REPLACE PROCEDURE getEmpInfo
(E_ID IN NUMBER, E_FIRST OUT VARCHAR) AS
BEGIN
SELECT first INTO E_FIRST
FROM EmpMaster
WHERE ID = E_ID;
END;
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
