# Creating a PL/SQL Stored Function by example Problem

One of your PL/SQL programs is using a few lines of code repeatedly for performing a calculation. Rather than using the same lines of code numerous times throughout your application, it makes more sense to encapsulate the functionality into a common routine that can be called and reused time and time again.

Solution

Create a PL/SQL stored function to encapsulate your code, and save it into the Oracle database. Once stored in the database, any user with execution privileges can invoke the function. Let's take a look at a function to give you an idea of how they work.

In this example, the function is used to round a given number to the nearest quarter. This function works well for accepting a decimal value for labor hours and rounding to the nearest quarter hour.

``````CREATE OR REPLACE FUNCTION CALC_QUARTER_HOUR(HOURS IN NUMBER) RETURN NUMBER AS

CALCULATED_HOURS NUMBER := 0;

BEGIN

-- if HOURS is greater than one, then calculate the decimal portion
-- based upon quarterly hours

IF HOURS > 1 THEN

-- calculate the modulus of the HOURS variable and compare it to
-- fractional values

IF MOD(HOURS, 1) <=.125 THEN

CALCULATED_HOURS := substr(to_char(HOURS),0,1);

ELSIF MOD(HOURS, 1) > .125 AND MOD(HOURS,1) <= .375 THEN

CALCULATED_HOURS := substr(to_char(HOURS),0,1) + MOD(.25,1);

ELSIF MOD(HOURS, 1) > .375 AND MOD(HOURS,1) <= .625 THEN

CALCULATED_HOURS := substr(to_char(HOURS),0,1) + MOD(.50,1);

ELSIF MOD(HOURS, 1) > .63 AND MOD(HOURS,1) <= .825 THEN

CALCULATED_HOURS := substr(to_char(HOURS),0,1) + MOD(.75,1);

ELSE

CALCULATED_HOURS := ROUND(HOURS,1);

END IF;

ELSE

-- if HOURS is less than one, then calculate the entire value

-- based upon quarterly hours

IF HOURS > 0 AND HOURS <=.375 THEN

CALCULATED_HOURS := .25;

ELSIF HOURS > .375 AND HOURS <= .625 THEN

CALCULATED_HOURS := .5;

ELSIF HOURS > .625 AND HOURS <= .825 THEN

CALCULATED_HOURS := .75;

ELSE

CALCULATED_HOURS := ROUND(HOURS,1);

END IF;

END IF;

RETURN CALCULATED_HOURS;

END CALC_QUARTER_HOUR;``````

This function accepts one value as input, a decimal value representing a number of hours worked. The function then checks to see whether the value is greater than one, and if so, it performs a series of manipulations to round the value to the nearest quarter hour. If the value is not greater than one, then the function rounds the given fraction to the nearest quarter.

How It Works

A function is a named body of code that is stored within the database and returns a value. Functions are often used to encapsulate logic so that it can be reused. A function can accept zero or more parameters and always returns a value. A function is comprised of a header, an execution section containing statements, and an optional exception block.

For example, the header for our PL/SQL solution function is as follows:

``CREATE OR REPLACE FUNCTION CALC_QUARTER_HOUR(HOURS IN NUMBER) RETURN NUMBER AS``

The OR `REPLACE` clause is optional, but in practice it is something you'll most always want. Specifying `OR REPLACE` will replace a function that is already under the same name in the same schema. (A function name must be unique within its schema.)

Functions can take zero or more parameters, which can be any datatype including collections. Our example function takes one parameter, a `NUMBER` representing some number of hours.

The parameters that can be passed to a function can be declared in three different ways, namely, as `IN`, `OUT`, and `IN OUT`. The difference between these three declaration types is that parameters declared as IN are basically read-only, `OUT` parameters are write-only, and `IN OUT` parameters are read-write. The value of an `OUT` parameter is initially `NULL` but can contain a value after the function has returned. Similarly, the value of an `IN OUT` can be modified within the function, but IN parameters cannot.

Note Typically you want only IN parameters for a function. If you find yourself creating a function with `OUT` or `IN OUT` parameters, then reconsider and think about creating a stored procedure instead. This is not a hard-and-fast requirement, but it is generally good practice for a function to return only one value.

The declaration section of the function begins directly after the header, and unlike the anonymous block, you do not include the `DECLARE` keyword at the top of this section. Just like the anonymous block, the declaration section is where you will declare any variables, types, or cursors for your function. Our declaration section defines a single variable:

``CALCULATED_HOURS NUMBER := 0;``

Following the declaration is the executable section, which is laid out exactly like that of an anonymous block. The only difference with a function is that it always includes a `RETURN` statement. It can return a value of any datatype as long as it is the same datatype specified in the `RETURN` clause of the header.

Following the return clause can be an optional `EXCEPTION` block to handle any errors that were encountered in the function. The following example is the same function that was demonstrated in the solution to this recipe, except that it has an added `EXCEPTION` block.

``````CREATE OR REPLACE FUNCTION CALC_QUARTER_HOUR(HOURS IN NUMBER)

RETURN NUMBER AS
CALCULATED_HOURS NUMBER := 0;

BEGIN

-- if HOURS is greater than one, then calculate the decimal portion
-- based upon quarterly hours

IF HOURS > 1 THEN

-- calculate the modulus of the HOURS variable and compare it to
-- fractional values

IF MOD(HOURS, 1) <=.125 THEN

CALCULATED_HOURS := substr(to_char(HOURS),0,1);

ELSIF MOD(HOURS, 1) > .125 AND MOD(HOURS,1) <= .375 THEN

CALCULATED_HOURS := substr(to_char(HOURS),0,1) + MOD(.25,1);

ELSIF MOD(HOURS, 1) > .375 AND MOD(HOURS,1) <= .625 THEN

CALCULATED_HOURS := substr(to_char(HOURS),0,1) + MOD(.50,1);

ELSIF MOD(HOURS, 1) > .63 AND MOD(HOURS,1) <= .825 THEN

CALCULATED_HOURS := substr(to_char(HOURS),0,1) + MOD(.75,1);

ELSE

CALCULATED_HOURS := ROUND(HOURS,1);

END IF;

ELSE

-- if HOURS is less than one, then calculate the entire value
-- based upon quarterly hours

IF HOURS > 0 AND HOURS <=.375 THEN

CALCULATED_HOURS := .25;

ELSIF HOURS > .375 AND HOURS <= .625 THEN

CALCULATED_HOURS := .5;

ELSIF HOURS > .625 AND HOURS <= .825 THEN

CALCULATED_HOURS := .75;

ELSE

CALCULATED_HOURS := ROUND(HOURS,1);

END IF;

END IF;

RETURN CALCULATED_HOURS;

EXCEPTION

WHEN VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE('VALUE ERROR RAISED, TRY AGAIN');

RETURN -1;

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('UNK ERROR RAISED, TRY AGAIN');

RETURN -1;

END CALC_QUARTER_HOUR;``````

Again, don't fret if you are unfamiliar with how to handle exceptions, because they will be discussed in detail later in the book. At this point, it is important to know that you have the ability to declare exceptions that can be caught by code so that your program can process abnormalities or errors accordingly.

Functions are important not only for encapsulation but also for reuse. As a matter of fact, the function defined within the solution uses other built-in PL/SQL functions within them. There are entire libraries that consist of functions that are helpful for performing various transactions. Functions are a fundamental part of PL/SQL programming, just as they are in any other language. It is up to you to ensure that your database is stocked with plenty of useful functions that can be used in your current and future applications. More by this author:

### Comments on Creating a PL/SQL Stored Function by example

Be the first to comment