#

| Chapter 3 The PL/SQL Development Spiral |

## 3.6 Building a Structured Function

Consider the problem of a function that does not execute a RETURN. The whole point of a function is to return a value. Not only should it return a value when everything goes right, it should even return a value when the function fails and raises an exception (NULL being the usual candidate under these circumstances).

In the twice function all my RETURN statements are nested inside IF clauses. So an invalid entry by the user means that all those RETURNs are ignored. There are lots of ways to fix this specific problem. You could include an ELSE statement. You could make sure that the action was valid at the start of the function (we'll look at that in a moment). The best all-around solution, however, is to always construct your functions with the following templated structure:

>      1    FUNCTION twice RETURN VARCHAR2
>      2    IS
>      3       v_retval VARCHAR2(100) := 'null';
>      4    BEGIN
>      5
>      6       RETURN v_retval;
>      7
>      8    EXCEPTION
>      9       WHEN OTHERS
>     10       THEN
>     11          RETURN NULL;
>     12    END twice;

In this template I declare a local variable (the return value or `v_retval`) with the same datatype as the function itself. I then always make the last line of the function a RETURN of the `v_retval` variable's value. In addition, my exception returns NULL if any kind of exception is raised. You will never get a -6503 error with this template -- and it is easier to debug than functions with RETURN statements scattered throughout the body of the program.

A version of twice that follows the template is shown in Example 3.4. Now I have a return value variable as the last line of the function body. To do this, I simply replaced each of the individual RETURN statements inside the IF statement with an assignment to `v_retval`. I have not, therefore, added any kind of special handling for invalid actions. Yet I no longer have to worry about -6503, because I have chosen a structure for my function that automatically rules out that possibility. Furthermore, it even returns a sensible value in the case of a bad action code. The `v_retval` is initialized by PL/SQL to NULL. If the user passes a code like BS, the value of `v_retval` will not be changed and, as a result, NULL will be returned, indicating an incorrect value (or, come to think of it, NULL input).

#### Example 3.4: A Template-based twice Function

>     CREATE OR REPLACE FUNCTION twice
>        (string_in IN VARCHAR2,
>         action_in IN VARCHAR2 DEFAULT 'N')
>     RETURN VARCHAR2
>     IS
>        v_action VARCHAR2(10) := UPPER (action_in);
>        v_retval VARCHAR2(100);
>     BEGIN
>        IF v_action = 'UL'
>        THEN
>           v_retval := UPPER (string_in) || LOWER (string_in);

>        ELSIF v_action = 'LU'
>        THEN
>           v_retval := LOWER (string_in) || UPPER (string_in);

>        ELSIF v_action = 'N'
>        THEN
>           v_retval := string_in || string_in;
>        END IF;
>        RETURN v_retval;
>     EXCEPTION
>        WHEN OTHERS
>        THEN
>           RETURN NULL;
>     END twice;
>     /

3.5 Rough Waters Ahead |  | 3.7 Handling Program Assumptions

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.