This tutorial about the best practice construction and use of an Oracle PLSQL procedure follows on from our PL/SQL overview which provides a general introduction to PL/SQL. In this tutorial we're gong to dissect and analyse a few examples to show best practice and maybe highlight a few things that are not so good.
we get bogged down with the detail, it's worth spending a little time
on a couple of fundamental principles.
FundamentalsFirst of all, a procedure in PL/SQL (as with any type of sub program in any language) should be designed to do just one thing. That doesn't mean it can have only one line of code! Simply that if the description includes lots of "and"s and/or "or"s then it's time for a re-design.
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 housekeeping 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 on earth is 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.
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
[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:
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 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.
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:
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
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 anonymous block) as a local procedure (i.e. not available outside the enclosing block).
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
5 err_msg CONSTANT VARCHAR2(45) := 'calc_net_pay: employee not found ';
9 SELECT rate*hours - deductions INTO net_pay
10 FROM emp where id = emp_id;
14 WHEN NO_DATA_FOUND THEN
18 WHEN TOO_MANY_ROWS THEN
20 'Duplicate pay details found for '||
23 END calc_net_pay;
Let's dissect this example.
A PLSQL procedure 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 unforeseen 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 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 possible 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 exception 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).
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.
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 available from the Oracle Technology Network.
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.