For example, a HR system might have one procedure to
calculate net pay and another to calculate how much holiday an
employee has left. That's fine but if there was just one procedure to
everything - with a description like "calculate net
pay and annual leave remaining and perform nightly houskepping and ..."
for example - then
that would be a good candidate for a re-design.
comments and white space should be used liberally to make the
easy to follow and more importantly less expensive to maintain. And
don't think comments are just for your successor - it's very easy to
come back to a program after a few weeks and think "what the hell's
going on here?" even when you wrote it yourself! The best comments are
those explaining why something is being done rather than repeating the
Structure of a PLSQL Procedure
Having dealt with fundamentals let's look at the structure of a PL/SQL
procedure. The basic syntax is as follows:
As you can see this
is similar to a PL/SQL function - the only 2 differences being the lack
of a RETURN
statement and obviously it starts with the
reserved word PROCEDURE
at the structure we see that a procedure:
- must have a name;
- may have one
or more parameters;
- may have local type and/or variable
- must have one or more executable statements
sandwiched between the
reserved words BEGIN
- may have an exception (error) handling section;
- and the procedure name
may be repeated as a label for the END statement.
Labelling the end
statement of a procedure is a very quick and easy way of making the
readable. In particular, it saves having to guess
is which if there are any nested blocks.
Tips & Tricks to
SKYROCKET Your Career!
already a subscriber you're missing out on a myriad
of tips and techniques to help
you become a better, faster, smarter developer. Subscribe now and
The exception handling section
The exception handling section in a PLSQL
procedure, as with anonymous blocks and PL/SQL functions, is
to trap and
handle errors (exceptions) raised by the Oracle database. Any
exceptions not handled here are propagated to the calling block
automatically and if not handled there, then to the caller of
block and so on until eventually control is returned to the
environment in which the code was running.
In other words if an exception is not handled anywhere then the code
stops with an error. The easiest way to see this is to run a PLSQL
procedure that has been deliberately coded to fail (use an implicit
cursor to run a query that will find no matches for example) using
SQL*Plus or SQL Developer or TOAD.
Simple PLSQL Procedure Example
We'll start our set of examples with a very simple procedure
we'll call "do_nothing"
is begin null; end;
PLSQL procedure doesn't do
anything (literally) but there are a few points worth mentioning:
- this procedure has no parameters
- there are no local declarations
- the only line of executable code is the NULL
statement, which (as its name suggests) doesn't do anything
- it's written all on one line and all in lower
Although PL/SQL is free format and not
case sensitive, by
convention reserved words are written in upper case
are usually on a line by themselves. The reason for this is to
make the code easier to read
therefore maintain. For short simple procedures, formatting
isn't really important, but the longer and the more complex the
procedure the more important the layout becomes.
Here's the procedure re-formatted to make it easier to read.
PROCEDURE do_nothing IS
To store this procedure in an Oracle database as a stand alone
entity we need to precede the keyword PROCEDURE
with the clause CREATE
This makes it
available to other users (depending on
privileges/permissions granted) and available for re-use in the future.
Alternatively this procedure could be enclosed in another block (PLSQL
procedure, PL/SQL function, or PL/SQL anonynous block) as a local
procedure (i.e. not available outside the enclosing block).
Expanded PLSQL Procedure Example
In this example we'll include parameters, local declarations
and an exception handler.
2 PROCEDURE calc_net_pay (emp_id NUMBER
,net_pay OUT NUMBER) IS
5 err_msg CONSTANT VARCHAR2(45) :=
'calc_net_pay: employee not found '
9 SELECT rate*hours -
deductions INTO net_pay
FROM emp where
id = emp_id;
14 WHEN NO_DATA_FOUND THEN
WHEN TOO_MANY_ROWS THEN
'Duplicate pay details found for '||
23 END calc_net_pay;
Let's disect this example.
A PLSQL prcocedure can have an infinite number of parameters
theory), but it's best not have too many, otherwise the procedure
becomes difficult to read and if it's trying to do too many things any
changes might have unforseen side effects.
The above example has 1 IN parameter (the default type) emp_id
This is a read only parameter.
The other parameter net_pay
is an OUT (write only) parameter. IN OUT (read/write) parameters are
also allowed. Also any
IN parameter can be made optional by providing a default value.
A PLSQL procedure can only have one exception handling section (at
most). Within that you can handle as many different named
(both user-defined and internal Oracle exceptions) as you
like and there are few restrictions on your freedom of action
in handling exceptions.
As well as the specific named exceptions, Oracle
provides a catch all exception OTHERS
which must always be the last exception
handler in the section.
It is also possible to trap an exception but take no action (by
as the body of the exception handler eg. WHEN TOO_MANY_ROWS THEN
This is effectively ignoring the error,
which may not always be a good idea.
the above example, there are 2 different exception handlers - one to
exception raised when the query returns no rows (no_data_found
and the other when more than 1 row
is found (too_many_rows
The 2nd exception is possibile only as a result of using an
implicit cursor (the embedded select
statement). Had the cursor been declared as an explicit cursor that
exception would never be raised (due to the way Oracle handles
By use of the RAISE
statement in the handling of the TOO_MANY_ROWS
excpetion we have said that a calling block must also handle the
exception (if not it will be re-raised until it is handled or
control is returned to the environment).
section holds the declarations of any local types and
variables. Nothing declared here is in scope (accessible)
outside of the procedure. In the case of
our example we have declared just one constant err_msg.
PLSQL procedure body
The body of the procedure (between the begin
does the work. The example
above is a rather simple procedure having just one
SQL statement which is embedded in the code. No calls to
library routines to
access the database are required as PL/SQL is tightly integrated with
the Oracle database.
The PLSQL procedure could obviously be a much more complex. Anonymous
PL/SQL blocks and functions can be nested inside and called from
procedures All the other types of PL/SQL statements are also available
- the purpose of a procedure being to encapsulate (and potentially hide
the complexity of) a specific piece of logic or sequence of statements.
We could provide more complex examples but the idea of this tutorial is
merely to give you a feel for the language rather than write a book on
PL/SQL or duplicate the PL/SQL Language Reference manual available from the Oracle Technology Network
If you want to learn more about PL/SQL our
offer instructor-led training on PL/SQL
Looking for a PL/SQL training course?
Learn PL/SQL from real-world experts with PL/SQL
training in the UK
. Advance your skills and sky rocket your career
instructor-led Oracle training.