Musings on a PLSQL Function

This tutorial about the best practice construction and use of an Oracle PLSQL function carries on from our PL/SQL overview which provides a general introduction to the language. In this tutorial we're gong to dissect and analyse a few examples to show what's good, what's bad and what's downright ugly when using functions in PL/SQL.

The basic syntax is as follows:


From this we can see that a PLSQL function must start with the reserved word FUNCTION, must have a name, may have one or more parameters and must specify the type of the value that the function returns after the reserved word RETURN. This section is known as the function heading and defines the interface to the function. It must precede the function body.

The body of the function is sandwiched between the reserved words BEGIN and END and must have the reserved word RETURN followed by the expression to be returned. The function is terminated by a semi-colon.

Between the function header and body is the declare section which is used to declare any local variables (or types) that will be used by the function. N.B. Any variables declared in this section are in scope only inside the function. The function can, of course,  also access any variables declared in an enclosing block.

You may have noticed that there is another optional section between the reserved words RETURN and END. This is the exception handling section, the purpose of which is to trap and handle errors (exceptions) raised by the Oracle database. Any exceptions not handled here will be propagated to the calling block automatically.

Oracle Tips & Tricks
to SKYROCKET Your Career!

If you're not already a subscriber to our newsletter you're missing out on a myriad of tips and tricks you need to become a better, faster, smarter developer. Subscribe now and ignite your career.

One other point worth mentioning before we progress to looking at a few examples is that a function need not have any executable statements other than the RETURN statement! The reason for this is that the expression in a function return can do all the work, as we'll see shortly.

Simple PLSQL Function Example

We'll start our set of examples with a very simple function which we'll call "marvin".

function marvin return varchar2 is begin return 'marvin'; end;

This PLSQL function doesn't do anything useful but there are a few points worth mentioning:

• the size of the character string returned is not specified
• the only line of executable code in the body of the function is the RETURN statement
• 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. This makes the code easier to read and therefore cheaper to maintain. Adding extra blank lines (and comments) in the source code is a good way of ensuring the code is easy to read and maintain. Any extra white space as well as the comments are stripped out at compile time so there's no cost to performance.

Here's our function re-formatted to make it easier to read.

FUNCTION marvin RETURN VARCHAR2 IS
BEGIN
    RETURN 'marvin';
END marvin;

For simple functions like this layout doesn't really matter, but it is important with longer or more complex functions.

On a general note, a PLSQL function, procedure or anonymous block can be nested inside (that is declared inside, not just called from) any other PL/SQL procedure, function or anonymous block.

To have this function stored in our Oracle database as a standalone entity we need to precede the keyword FUNCTION with the clause CREATE OR REPLACE. This makes it available for re-use and to other users (depending on privileges/permissions granted). The other alternative is to enclose it in another block. This would make it local to that block.

Expanded PLSQL Function Example

In this example we'll include a parameter, a local variable declaration and an exception handler.

1  CREATE OR REPLACE
2  FUNCTION deductions (emp_id NUMBER := -99) IS
3  
4      total_deductions NUMBER;
5           
6  BEGIN
7
8      SELECT tax+insurance+social_club_fees
9      INTO total_deductions
10     FROM emp where id = emp_id;
11
12     RETURN total_deductions/12;
13
14 EXCEPTION
15    WHEN no_data_found THEN
16        RETURN 0;
17    WHEN OTHERS THEN
18        RETURN -1;
17 END deductions;

Let's dissect this example one section at a time.

Parameters

A PLSQL function can have an infinite number of parameters (in theory), although it's best not to have too many for issues of clarity and performance reasons (they all have to be pushed on to and popped off the stack).

The example above has just 1 parameter emp_id. In this case it is a read only parameter (the default). Read/write parameters and write only parameters are allowed with functions but best practice is to keep functions pure - returning just one value and not having any side effects. Also any IN parameter can be made optional by providing a default value (in this case -99).

Exception Handler

A PLSQL function can can handle as many different named exceptions (both user-defined and internal Oracle exceptions) as you like. However there can only be at most one exception handling section. Oracle provides the OTHERS exception as a catch all to handle any exceptions not specifically trapped by a named exception handler. This must always be the last exception handler in the section. It is possible to trap but take no action (by having "NULL;" as the body of the exception handler) when an exception is raised. This is effectively ignoring the error which may or may not be such a good idea!

Any exceptions not caught by an exception handler are automatically propagated to the calling block by Oracle. Any exception not handled there is automatically propagated to the caller of that block and so on until either the exception is handled by the calling block or the environment in which the code is running. Even if the exception is handled it can be RAISED to be handled again (or not) by a calling block.

In the above example, we use 2 different exception handlers to trap the exception raised when the query returns no rows and all other exceptions. It might have been better though to let the calling block handle exceptions rather than return spurious values. That's something that needs to be considered at the design stage so that a coherent approach is adopted.

Declaration Section

This section holds the declarations of any types and variables that are required just for the function. Any type or variable declared here is not in scope (and therefore not accessible) outside of the function. In the case of our example we have declared just one variable.

PLSQL Function body

The function body (between the begin and end) does the work. In the example above there is a SQL query statement. Notice that the query is just embedded into the code - there are no calls to library routines to access the database as Oracle handles all this directly. This tight integration with the database makes using PL/SQL with Oracle very easy.

Note that using an implicit cursor as in the example runs the risk of an Oracle no_data_found exception being raised if the query returns no rows and a too_many_rows exception if more than 1 match is found.

Another PLSQL Function Example

We mentioned earlier that the only executable line of code needed is the RETURN statement. Just to prove it here's another example:

FUNCTION circumf (angle NUMBER,radius NUMBER)
RETURN NUMBER IS
    pi CONSTANT NUMBER := 3.1415926;
BEGIN
    RETURN ROUND((angle/180)*pi*radius,2);
END circumf;

More details on PLSQL functions and other aspects of PL/SQL can be found in the OracleŽ Database PL/SQL User's Guide and Reference available from the Oracle Technology Network.

More best practice

PLSQL functions should be designed to do just one thing. When the function description includes several "and"s it's time to reconsider the design. Also, the name of a function should be a noun or asking a question rather than a verb. For example "leave_remaining" rather than "calculate_holiday_entitlement".

One of the neat things about PLSQL functions is that function calls can be embedded directly into SQL statements if and only if the function (as well as any function or procedure called by it) obeys all of the following rules regarding purity :

• When a function is called from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement no database tables can be modified by it.

• When a function is called from an INSERT, UPDATE, or DELETE statement it cannot query or modify any database tables modified by that statement.

• When a function is called from a SELECT, INSERT, UPDATE, or DELETE statement, no SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), system control statements (such as ALTER SYSTEM) can be executed by it. Neither can it execute any DDL statements (such as CREATE TABLE etc) as they perform an implicit commit.

Return from musings on a PLSQL function to the introductory PLSQL tutorial