Oracle Tips by
A stored procedure is a piece of code that performs a
specific task. The procedure is compiled by Oracle and stored within
the data dictionary.
This chapter discusses the creation of procedures using PL/SQL. I
have provided several detailed examples, and will guide you through
the entire process of creating a new procedure. By the end of this
chapter, you will be familiar with how stored procedures are used and
how to design, create, and test your own procedures.
Advantages of Procedures
By allowing you to store the executable and source code for your
application within the data dictionary, Oracle allows you to create
applications that reach new heights in performance, modularity,
maintainability, and reliability. Code no longer has to be generated
within the front end of a system; instead, logic can be centralized
and called from every part of a system’s interface.
Stored procedures can execute any DML statement that can be
executed in SQL*Plus, as shown in Listing 4.1.
Listing 4.1 Embedded SQL within a stored procedure.
WHERE CURRENT OF Employees_cur;
The logic of a stored procedure is more easily maintained than
individual copies of the same piece of code spread throughout a
system. If a logic error is discovered or a business rule changes,
only the one stored procedure has to be changed and tested.
If the same logic is coded into several different applications,
changing the code could easily take several times longer than changing
a single piece of code. There are also reliability issues because
making several changes increases the likelihood of a defect being
introduced into the code.
By coding logic for a specific task into a stored procedure, the
logic for the task becomes readily available to any code that needs to
perform the specific task. The procedure will accept a defined set of
input values and will process those values in exactly the same manner
every time the procedure executes. Figure 4.1 illustrates the concepts
behind modular code.
Figure 4.1 Modular
versus non-modular code.
There are several reasons why stored procedures provide some
performance improvements over code that is implemented in multiple
locations to perform the same task, including:
- On large projects, code to perform a similar
task in multiple locations is rarely written by the same developer.
Calling a stored procedure to perform a task increases the
likelihood that the DML statements inside the stored procedure are
already cached in the SGA because those statements are written only
- The implementation of a stored procedure does
not change depending on which part of the system calls the
procedure. The parameter values are the only differences in the
- Oracle maintains a copy of the executable
version of the stored procedure (p-code) and executes this
copy rather than recompiling the procedure.
A stored procedure only has to be written once and can be called
from many different parts of a system. Even if a logic error is found
or a business rule changes, the change only has to be made once and
every part of the system that calls the stored procedure is corrected.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.