Why should use Stored Procedures

In software development its very common to interact with Database(s) and Application Layer used to fetch data from DB using any programming language like java, C# conjunction with SQL.
There are two kind of DB languages
1) SQL : Non procedural language, where you can not write your own logic / subroutines.
2) PL/SQL: Procedural language, where you can write business logic and subroutines.

Stored procedures used where we would require the following
* modular programming at DB level
* Faster execution.
* Reduce network traffic.
* Security mechanism.

Advantages:

->You can modify stored procedures independently of the program source code.The application doesn't have to be recompiled when/if the SQL is altered.
->Stored procedures abstract or separate server-side functions from the client-side.
->Stored procedures allow a lot more flexibility offering capabilities such as conditional logic.
->Stored procedures are stored within the databse and run directly in database engine, bandwidth and execution time are reduced. This is because a single stored procedure can execute a complex set of SQL statements.

Disadvantages:

->If we need to modify anything in stored procedure then its definition has to be replaced.
->Any changes in the stored procedure will impact all the places whereever this procedure is used.

How to create :
http://download.oracle.com/docs/html/B16022_01/ch3.htm
Examples in java:
http://www.oracle.com/technology/sample_code/tech/java/jsp/oracle9ijsp.html