[?] Subscribe To This Site

How To Build a PLSQL Procedure

PL/SQL is a block-structured language with 3 types of block: PLSQL procedure, anonymous plsql block or plsql function. Each of these block types has the same three sections: declarations, executable code and exception handlers with the executable code being the only mandatory section.

Before we start to look at an example, it's worth stating that a procedure (or function or anonymous block) in PLSQL doesn't have a fixed size or a maximum size (subject to memory constraints) and should be designed to do one thing only and be as independent of any other procedures as possible.

That doesn't mean a procedure in PL/SQL can have only 1 line of code - it means that when you state the purpose of your procedure, the statement should be succinct and should describe an action.

For example in a payroll program, you might have one procedure to compute net pay, another to calculate how many days holiday an employee has left and another to calculate flexi-time hours accrued or owing.

When you start describing your procedure as "computing net pay and calculating annual leave remaining and calculating sick pay and ..." then it's time to reconsider and split it into 2 or more procedures.

Structure of a PLSQL Procedure

Let's look at the basic structure of a PLSQL procedure. First we'll provide the basic syntax diagram and then we'll look at some examples.

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

Dissecting this, we see that a procedure must start with the reserved word PROCEDURE, must have a name, must have at least one executable statement sandwiched between the reserved words BEGIN and END, the END can be labeled with the procedure name and the procedure is terminated by a semi-colon.

Simple PLSQL Procedure Example

Now let's see some examples. We'll start with a very simple procedure which we'll call my_first_proc.

PROCEDURE my_first_proc IS
BEGIN
NULL;
END my_first_proc;

This is a valid PLSQL procedure, although it doesn't do anything useful - in fact it doesn't do anything at all - it will compile!

A few points of interest to note about this example:

this procedure has no parameters
the only line of code is the NULL statement
the NULL statement (as its name suggests) doesn't do anything
without the NULL statement, the procedure would not compile
as it is this procedure would have to be part of another block

One general point to note is that PLSQL is a free format, case insensitive language. By convention, however, reserved words are written in upper case and BEGIN and END have a line to themselves. This makes the code easier to read and therefore cheaper to maintain. We could have written this procedure on one line like this:

PROCEDURE my_first_proc IS BEGIN NULL; END my_first_proc;

Whilst this may be acceptable for a very simple procedure like this, for more complex procedures it wouldn't be.

You should always take care with the layout and make sure you thoroughly comment why you're doing things (the code will say what you're doing). Your procedures (like all of your PL/SQL code) should be easy to read and look elegant. If it looks ugly it is more likely to be difficult to maintain and if it's your code you've nobody else to blame.

One thing we didn't mention before is that a PLSQL procedure, function or anonymous block can be nested inside (that is declared inside, not just called from) any other PL/SQL procedure, function or anonymous block.

All 3 block types can also be part of a PLSQL package which is analogous to a program in other languages such as Java, COBOL or C.

To create this procedure as a standalone entity we need to precede it with the clause CREATE OR REPLACE. Doing this causes it to be stored in the database and therefore available to other users (depending on privileges/permissions granted).

Expanded PLSQL Procedure Example

Now let's see expand on our previous example. This time we'll add some parameters and an exception handler.

1 CREATE OR REPLACE
2 PROCEDURE calc_net_pay (emp_id NUMBER
3 ,net_pay OUT NUMBER) IS
4 BEGIN
5
6    SELECT rate*hours - deductions(emp_id)
7    INTO net_pay FROM emp where id = emp_id;
8
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11    DBMS_OUTPUT.PUT_LINE(
12     'Error: employee id '||emp_id||
13   ' not found in calc_net_pay');
14
15 WHEN OTHERS THEN
16    DBMS_OUTPUT.PUT_LINE(
17     'Error processing employee id '||
18   emp_id||'in calc_net_pay');
19
20 END calc_net_pay;

Parameters

As you can see, this PLSQL procedure has 2 parameters: 1 input parameter emp_id and 1 output parameter net_pay_out. There is also a 3rd type of parameter which is an IN OUT parameter.

IN parameters can be made optional by providing them with a default value in the declaration. Oracle also enforces the behaviour of IN parameters in both procedures and functions - they can't be written to inside the procedure or function.

Another point to note is that OUT parameters are initialised to NULL inside PLSQL procedures and functions which means any value they had before the call to the procedure is lost.

IN OUT parameters preserve their initial value inside procedures and functions until they are written to.

Exception Handler

A PLSQL procedure (or function or anonymous block) has only one exception handling section but this can handle as many different types exceptions as you like, both user-defined and Oracle provided.

The WHEN OTHERS exception handler is a catch all, handling any exceptions not already handled and so must be the last exception handler.

Other Points To Note

SQL statements can be embedded directly into the code and calls to PL/SQL functions can be embedded in SQL statements as seen on line 6.

Type definitions and declarations of local variables have to go between the reserved words IS and BEGIN.

More details on PLSQL procedures and about PL/SQL in general can be found in the Oracle� Database PL/SQL User's Guide and Reference available from Oracle.

Return from PLSQL procedure to PLSQL overview

footer for Oracle page