Database Reference
In-Depth Information
9.10 Stored Procedure
SQL acts as the interface to the database. A client program, whether it exists on the same
computer or on another, makes a connection to the database, sends a request in the form of
SQL to the server, and in return gets back structured data,
So a stored procedure is a program that resides inside an Oracle database that manipulates
data in the database before the data is used outside the database. A procedure is a module
performing one or more actions; it does not need to return any values.
Procedure is made up of:
1. A declarative part
2. An executable part
3. An optional exception handling part
Declarative part: It may include the declarations of variables, constants, cursors, subpro-
grams and exceptions. These are local to procedure and no existence out side a procedure.
Executable part: this part includes SQL and PL/SQL statements. This block manipulates
data and result is to be returned back to the calling portion.
Exception handling part: PL/SQL provides a feature to handle the Exceptions which occur
in a PL/SQL Block known as exception Handling. Using Exception Handling we can test
the code and avoid it from exiting abruptly. When an exception occurs a messages which
explains its cause is received. When an exception is raised, Oracle searches for an appro-
priate exception handler in the exception section.
Use of Stored Procedures
