Creating a PL/SQL Stored Function by example

PL/SQL Stored Function by exampleProblem

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.

 

Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 1726 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 1630 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 1377 views sepia Sat, 01 Dec 2018, 10:54:57
Executing a PL/SQL Stored Func...
Executing a PL/SQL Stored Func... 1322 views Игорь Воронов Sun, 09 Sep 2018, 09:22:25
Comments (0)
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Suggested Locations