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 do 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.

Secondly, comments and white space should be used liberally to make the code 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 obvious.

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:

PROCEDURE <procedure_name> [(<parameters>)] IS
[<declarations>]
BEGIN
<statement(s)>
[EXCEPTION <exception handler(s)] END [<procedure_name>];

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.

Looking 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 declarations;
must have one or more executable statements sandwiched between the reserved words BEGIN and END;
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 code more readable. In particular, it saves having to guess which end is which if there are any nested blocks.

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not 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 ignite your career.

The exception handling section

The exception handling section in a PLSQL procedure, as with anonymous blocks and PL/SQL functions, is used 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 that 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 which we'll call "do_nothing".

procedure do_nothing is begin null; end;

This 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 case!

Although PL/SQL is free format and not case sensitive, by convention reserved words are written in upper case and BEGIN and END are usually on a line by themselves. The reason for this is to make the code easier to read and 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
BEGIN
NULL;
END do_nothing;

To store this procedure in an Oracle database as a stand alone entity we need to precede the keyword PROCEDURE with the clause CREATE OR REPLACE. 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.

1 CREATE OR REPLACE
2 PROCEDURE calc_net_pay (emp_id NUMBER
3 ,net_pay OUT NUMBER) IS
4
5 err_msg CONSTANT VARCHAR2(45) := 'calc_net_pay: employee not found ';
6
7 BEGIN
8
9    SELECT rate*hours - deductions INTO net_pay
10     FROM emp where id = emp_id;
11
12 EXCEPTION
13
14 WHEN NO_DATA_FOUND THEN
15    DBMS_OUTPUT.PUT_LINE(err_msg||emp_id);
16 RAISE;
17
18 WHEN TOO_MANY_ROWS THEN
19    DBMS_OUTPUT.PUT_LINE(
20 'Duplicate pay details found for '||
21 emp_id);
22
23 END calc_net_pay;

Let's disect this example.

Parameters

A PLSQL prcocedure can have an infinite number of parameters (in 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.

Exception Handlers

A PLSQL procedure can only have one exception handling section (at most). Within that you can handle as many different named exceptions (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 having "NULL;" as the body of the exception handler eg. WHEN TOO_MANY_ROWS THEN NULL;). This is effectively ignoring the error, which may not always be a good idea.

In the above example, there are 2 different exception handlers - one to trap the 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 cursors).

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).

Declaration Section

This 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 and end) 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. However if you want to learn more about PL/SQL our partners in the UK offer instructor-led training on PL/SQL (as well as other aspects of Oracle).

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 with expert instructor-led Oracle training.

PLSQL Procedure Examples Dissected and Analysed

Fundamentals